Re: IN vs EXIIST

Henshall, Stuart - WCP wrote:

[deleted]

I tried your optimized query but it was muh slower. Here are the results:

##Query using EXISTS

$ !1173
time psql TMP -c "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'
     ) "
  count
-------
    170
(1 row)


real    0m8.322s
user    0m0.010s
sys     0m0.000s

##Query using IN

$ !1175
time psql TMP -c "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'
    ) "
  count
-------
    170
(1 row)


real    0m0.234s
user    0m0.000s
sys     0m0.010s

Maybe EXISTS is not always faster than IN ?

After a "vacuum analyze" the numbers become:

#using EXISTS

real    0m3.229s
user    0m0.000s
sys     0m0.000s

#using IN

real    0m0.141s
user    0m0.000s
sys     0m0.000s


Jc