title: PostgreSQL: exists vs left join
body: <p>I heard many times that postgres handles <em>exists</em> queries even faster then <em>left join</em>.
  <a href="http://archives.postgresql.org/pgsql-performance/2002-12/msg00185.php" rel="nofollow noreferrer">http://archives.postgresql.org/pgsql-performance/2002-12/msg00185.php</a></p>
  
  <p>That's definitely true for one table aggregation.</p>
  
  <p>But in our case their is more then one and the same query build with <em>exists</em> that make postgres to hang forever:</p>
  
  <pre><code>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) 
  </code></pre>
  
  <p>result:</p>
  
  <pre><code> Aggregate  (cost=26413436.66..26413436.67 rows=1 width=4)
     -&gt;  Seq Scan on groups  (cost=0.00..26413403.84 rows=13126 width=4)
           Filter: ((id &lt;&gt; 3) AND (subplan))
           SubPlan
             -&gt;  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
                     -&gt;  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))
  </code></pre>
  
  <p>That is the root cause of incredibly long execution time?
  Is that some kind of configuration problem?</p>
  
  <p>Thanks,
  Bogdan.</p>