PostgreSQL: exists vs left join

Question

I heard many times that postgres handles exists queries even faster then left join. http://archives.postgresql.org/pgsql-performance/2002-12/msg00185.php

That's definitely true for one table aggregation.

But in our case their is more then one and the same query build with exists that make postgres to hang forever:

explain 
SELECT count(DISTINCT "groups".id) AS count_all 
FROM "groups"
WHERE (exists(
    select * from products p where groups.id = p.group_id AND exists(
        select * from products_categories pc where p.id = pc.product_id AND pc.category_id in (2,3))) AND groups.id != 3) 

result:

 Aggregate  (cost=26413436.66..26413436.67 rows=1 width=4)
   ->  Seq Scan on groups  (cost=0.00..26413403.84 rows=13126 width=4)
         Filter: ((id <> 3) AND (subplan))
         SubPlan
           ->  Index Scan using index_products_on_group_id on products p  (cost=0.00..1006.13 rows=1 width=1483)
                 Index Cond: ($1 = group_id)
                 Filter: (subplan)
                 SubPlan
                   ->  Seq Scan on products_categories pc  (cost=0.00..498.49 rows=1 width=8)
                         Filter: ((category_id = ANY ('{2,3}'::integer[])) AND ($0 = product_id))

That is the root cause of incredibly long execution time? Is that some kind of configuration problem?

Thanks, Bogdan.

Answer 1

Is there an index on groups.id? Because to me it looks like there is none.

Also, can you tell us what you are trying to accomplish? Maybe we can help you optimize your query for you.

Answer 2

Well, for each row in "groups", postgresql is doing a full scan of products_categories, which isn't good. Not necessarily a configuration problem, but perhaps the query could be stated without nesting subqueries like that?

SELECT count(DISTINCT "groups".id) AS count_all 
FROM "groups"
WHERE exists(
    select 1 from products p where groups.id = p.group_id
             join products_categories pc on pc.product_id = p.id
    where pc.category_id in (2,3)
    ) and groups.id <> 3

Also does products_categories have an index on product_id?