Re: Join query on 1M row table slow

On Tue, 10 Feb 2004, CSN wrote:

>
> I think that probably improves things (lower cost? -
> see my other post):
>
> explain 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)
> -> Sort (cost=4282.18..4282.46 rows=111
> width=290)
> Sort Key: p.title
> -> Nested Loop (cost=3609.75..4278.41
> rows=111 width=290)
> -> HashAggregate
> (cost=3609.75..3609.75 rows=111 width=4)
> -> Index Scan using
> idx_pc_category_id on product_categories pc
> (cost=0.00..3607.28 rows=986 width=4)
> Index Cond: (category_id =
> 1016)
> -> Index Scan using pkey_products_id
> on products p (cost=0.00..6.01 rows=1 width=290)
> Index Cond: (p.id =
> "outer".product_id)
> (9 rows)
>
>
> I figured the limit/offset was probably causing the
> problem. What's weird is that when the same query is
> executed again, it seems much faster - some sort of
> caching maybe?>

Yep. Exactly.

What does explain analyze say about the two queries?

> (table pc is just product_id <=> category_id - I don't
> really need the category_id)

If you could eliminate the need for that table in this query you should
get it to run much faster.