IN and EXISTS

• Traditionally, the optimizer does terrible with IN clauses
   
• EXISTS is not perfect either, but much, much faster
   
• 7.4 will handle IN much better
   
• If using < 7.4, switch from IN to EXISTS:

EXPLAIN ANALYZE
SELECT * FROM tax s WHERE s. unit_name2 = 'sapiens'
AND s.usage IN 
  (SELECT usage FROM tax WHERE update_date < '2001-01-02');

                                      QUERY PLAN                                      
--------------------------------------------------------------------------------------
 Index Scan using tax_unitname2 on tax s  (C=0.00..209286.91 21) (A=11.58..11.60 1/1)
   Index Cond: (unit_name2 = 'sapiens'::character varying)
   Filter: (subplan)
   SubPlan
     ->  Seq Scan on tax  (C=0.00..9781.42 261323) (A=2.30..9.34 498/1)
           Filter: (update_date < '2001-01-02'::date)
 Total runtime: 11.91 msec

Change To:

EXPLAIN ANALYZE
SELECT * FROM tax s WHERE s. unit_name2 = 'sapiens'
AND NOT EXISTS
  (SELECT 1 FROM tax WHERE tax.usage=s.usage AND update_date < '2001-01-02');

                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 Index Scan using tax_unitname2 on tax s  (C=0.00..178.46 21) (A=5.56..5.56 0/1)
   Index Cond: (unit_name2 = 'sapiens'::character varying)
   Filter: (NOT (subplan))
   SubPlan
     ->  Seq Scan on tax  (C=0.00..10609.31 65331) (A=5.31..5.31 1/1)
           Filter: (("usage" = $0) AND (update_date < '2001-01-02'::date))
 Total runtime: 5.87 msec

Previous: Indexes         [Table of Contents]         Next: Joins