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