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

Doh! Yeah, now I remember ;)

QUERY 1:

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

                       QUERY PLAN


----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=9595.99..9596.05 rows=25 width=290)
(actual time=514.808..514.942 rows=25 loops=1)
   ->  Sort  (cost=9595.99..9598.45 rows=986
width=290) (actual time=514.794..514.868 rows=25
loops=1)
         Sort Key: p.title
         ->  Nested Loop  (cost=0.00..9546.96 rows=986
width=290) (actual time=0.672..421.732 rows=2358
loops=1)
               ->  Index Scan using idx_pc_category_id
on product_categories pc  (cost=0.00..3607.28 rows=986
width=4) (actual time=0.343..125.762 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.075..0.083 rows=1 loops=2358)
                     Index Cond: ("outer".product_id =
p.id)
 Total runtime: 516.174 ms
(9 rows)


QUERY 2:

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

                         QUERY PLAN


---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=4282.18..4282.24 rows=25 width=290)
(actual time=447.852..447.979 rows=25 loops=1)
   ->  Sort  (cost=4282.18..4282.46 rows=111
width=290) (actual time=447.836..447.904 rows=25
loops=1)
         Sort Key: p.title
         ->  Nested Loop  (cost=3609.75..4278.41
rows=111 width=290) (actual time=104.256..358.182
rows=2358 loops=1)
               ->  HashAggregate
(cost=3609.75..3609.75 rows=111 width=4) (actual
time=103.922..114.836 rows=2358 loops=1)
                     ->  Index Scan using
idx_pc_category_id on product_categories pc
(cost=0.00..3607.28 rows=986 width=4) (actual
time=0.401..92.253 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.069..0.076 rows=1 loops=2358)
                     Index Cond: (p.id =
"outer".product_id)
 Total runtime: 449.370 ms
(10 rows)


-CSN


--- "scott.marlowe" <scott.marlowe@ihs.com> wrote:
> On Tue, 10 Feb 2004, CSN wrote:
>
> > > 2. Vacuum analyze the tables concerned and post
> the
> > > output of EXPLAIN ANALYSE
> > > with your query.
> >
> > => explain analyze;
> >
> > results in:
> >
> > ERROR:  syntax error at or near ";" at character
> 16
>
> No silly.  you do:
>
> explain analyze select ... (rest of the query...)
>
> and it runs the query and tells you how long each
> bit took and what it
> THOUGHT it would get back in terms of number of rows
> and what it actually
> got back.
>
> Let us know...
>


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