I have found in previous versions of postgres that rewriting the join can help. Have you tried something like:
select p.*
from product_categories pc, products p
where pc.product_id = p.id AND pc.category_id = $category_id
order by p.title
limit 25
offset $offset
cheers
Matthew
--
----- Original Message -----
Sent: Tuesday, February 10, 2004 7:51 PM
Subject: [GENERAL] Join query on 1M row table slow
I have a pretty simple select query that joins a table
(p) with 125K rows with another table (pc) with almost
one million rows:
select p.*
from product_categories pc
inner join products p
on pc.product_id = p.id
where pc.category_id = $category_id
order by p.title
limit 25
offset $offset
The query usually takes about five seconds to execute
(all other PG queries perform fast enough). I have
indexes on everything needed, and EXPLAIN shows
they're being used. Is there anything else I can do to
improve performance - such as tweaking some settings
in the config?
Redhat 9, PG 7.4.1.
__________________________________
Do you Yahoo!?
Yahoo! Finance: Get your refund fast by filing online.
http://taxes.yahoo.com/filing.html
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
_____________________________________________________________________
This e-mail has been scanned for viruses by MCI's Internet Managed Scanning Services - powered by MessageLabs. For further information visit http://www.mci.com