By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,903 Members | 1,086 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,903 IT Pros & Developers. It's quick & easy.

phpPGAdmin Indexes, what does this do?

P: n/a
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

Nov 12 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.