phpPGAdmin Indexes, what does this do?

[Sean Gonsman]

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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org