Re: Join query on 1M row table slow - Mailing list pgsql-general

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@rwa-net.co.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@postgresql.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