phpPGAdmin Indexes, what does this do?

[Sean]

Hi all,

I have two databases set up, one for development and one for
production. They are almost identical. I noticed that a query was
taking about 6.7 seconds to execute on the development database, but
only .08 seconds on the production database. The only difference was
there was another key for the production server (I am guessing it was an
index). So I clicked on the "Index" link in PhpPGAdmin for the id of
the table on the development database. This created another key just
like the one I had on the production. Now the query executed at .08
seconds. To further see what was going on, I removed the new index from
the dev DB and testing the query again... amazingly, too me, it was
still fast. Can anyone explain this to me? Thanks, Sean. Below is my
query:

SELECT
seasonal.id AS seasonal_id,
seasonal.title AS seasonal_title,
prod.id,
prod.title,
prod.co_title,
prod.summary,
prod.pic1,
prod.new_pic,
prod.new_date
FROM
seasonal,
xref_seasonal_prod,
prod,
prod_opt
WHERE
seasonal.home = 't'
AND xref_seasonal_prod.xref_seasonal_id = seasonal.id
AND xref_seasonal_prod.xref_prod_id = prod.id
AND prod.live = 't'
AND (prod_opt.clearan = 'f' OR (COALESCE(prod_opt.quantity,0) -
COALESCE(prod_opt.committed,0)) > 0)
AND prod_opt.xref_prod_id = prod.id
AND prod_opt.live = 't'
AND EXISTS
(
SELECT
a.id
FROM
prod_opt AS a
WHERE
a.xref_prod_id = prod.id
AND a.live = 't'
ORDER BY
a.priority
LIMIT 1
)
AND prod.mrf_only = 'f'
ORDER BY
xref_seasonal_prod.priority,
prod.title
LIMIT 3
---------------------------------
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard

[Manfred Koizar]

On Fri, 7 Nov 2003 14:02:55 -0800 (PST), Sean <dc*****@yahoo.com>
wrote:

To further see what was going on, I removed the new index from
the dev DB and testing the query again... amazingly, too me, it was
still fast. Can anyone explain this to me?

CREATE INDEX updates reltuples and relpages in pg_class. This might
cause the planner to choose a different plan. May I guess that you
didn't VACUUM or ANALYSE your table for a long time?

Servus
Manfred

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend