Re: IN vs EXIIST

Strangely enough doing an EXPLAIN on the two queries shows that using
EXISTS would be faster than IN ... even though it isn't ..

psql TMP -c "explain select count(distinct invoice_id) from invoice_li
where received='true' AND shipped='false' AND cancelled='false'
  AND
   (invoice_id not in
     (
      select distinct invoice_id from invoice_li where received='false'
AND cancelled='false'
      )
     OR ship_now='true'
     ) "
NOTICE:  QUERY PLAN:

Aggregate  (cost=17642485.70..17642485.70 rows=1 width=4)
   ->  Seq Scan on invoice_li  (cost=0.00..17642460.40 rows=10120 width=4)
         SubPlan
           ->  Materialize  (cost=871.61..871.61 rows=1 width=4)
                 ->  Unique  (cost=871.61..871.61 rows=1 width=4)
                       ->  Sort  (cost=871.61..871.61 rows=1 width=4)
                             ->  Seq Scan on invoice_li
(cost=0.00..871.60 rows=1 width=4)

EXPLAIN
$ psql TMP -c "explain select count(distinct invoice_id) from invoice_li
where received='true'
  AND shipped='false' AND cancelled='false'
  AND
    (NOT EXISTS
      (
       select * from invoice_li AS sq_inv_li where received='false'
  AND cancelled='false' AND invoice_li.invoice_id=sq_inv_li.invoice_id
       )
      OR ship_now='true'
      ) "
NOTICE:  QUERY PLAN:

Aggregate  (cost=4955505.10..4955505.10 rows=1 width=4)
   ->  Seq Scan on invoice_li  (cost=0.00..4955479.80 rows=10120 width=4)
         SubPlan
           ->  Index Scan using invoice_li_pkey on invoice_li sq_inv_li
  (cost=0.00..244.79 rows=1 width=80)

EXPLAIN

Jc