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
