Re: IN vs EXIIST

How about:
select (
  ( select count(distinct invoice_id)
    from invoice_li
    WHERE shipped='false' AND cancelled='false')
- ( select count(distinct invoice_id)
    from invoice_li
    WHERE received='false' AND shipped='false' AND cancelled='false'));

Jean-Christian Imbeault wrote:
>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)