Henshall, Stuart - WCP wrote:
>
> It might be interesting to try removing the distinct clause from the
> first query and seeing what difference that makes, both real and
> supposed. Also maybe try seeing what difference it would make to the
> EXISTS sub query to have distinct invoice_id rather than *.
Removing or addin a distinct did not change the results but ...
Your query with EXISTS is this:
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'
) "
I am no SQL expert so I took it as face value (especially since I don't
quite underst EXISTS).
But I removed this "AND invoice_li.invoice_id=sq_inv_li.invoice_id "
from the subquery and I got the correct count *and* the same speed as
the IN query ...
Jc