Re: Join query on 1M row table slow

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(dot)marlowe(at)ihs(dot)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