By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,776 Members | 1,777 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,776 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
---------------------------------
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


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

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.