Re: Join query on 1M row table slow

Поиск
Список
Период
Сортировка
От Matthew Lunnon
Тема Re: Join query on 1M row table slow
Дата
Msg-id 005101c3f083$b1a74fa0$8e8bbd3e@rwanet.co.uk
обсуждение исходный текст
Ответ на Join query on 1M row table slow  (CSN <cool_screen_name90001@yahoo.com>)
Ответы Re: Join query on 1M row table slow
Список pgsql-general
Дерево обсуждения
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
--
 
Matthew Lunnon
Senior Software Engineer
RWA Ltd
www.rwa-net.co.uk
----- Original Message -----
From: CSN
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

В списке pgsql-general по дате отправления: