Re: Join query on 1M row table slow

Appears to be somewhat slower - took about 600-2600ms
on different runs.

CSN

=> explain analyze select p.* from product_categories
pc, products p where pc.product_id = p.id AND
pc.category_id = 1016 order by p.title limit 25 offset
0;

QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=9270.77..9270.83 rows=25 width=290)
(actual time=2598.686..2598.875 rows=25 loops=1)
-> Sort (cost=9270.77..9273.15 rows=952
width=290) (actual time=2598.677..2598.805 rows=25
loops=1)
Sort Key: p.title
-> Nested Loop (cost=0.00..9223.67 rows=952
width=290) (actual time=27.257..2485.644 rows=2358
loops=1)
-> Index Scan using idx_pc_category_id
on product_categories pc (cost=0.00..3493.30 rows=951
width=4) (actual time=26.819..396.049 rows=2358
loops=1)
Index Cond: (category_id = 1016)
-> Index Scan using pkey_products_id
on products p (cost=0.00..6.01 rows=1 width=290)
(actual time=0.838..0.845 rows=1 loops=2358)
Index Cond: ("outer".product_id =
p.id)
Total runtime: 2600.395 ms
(9 rows)

--- Matthew Lunnon <mlunnon(at)rwa-net(dot)co(dot)uk> wrote:
> 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
> To: pgsql-general(at)postgresql(dot)org
> 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
>

__________________________________
Do you Yahoo!?
Yahoo! Finance: Get your refund fast by filing online.
http://taxes.yahoo.com/filing.html