From: | Jean-Luc Lachance <jllachan(at)nsd(dot)ca> |
---|---|
To: | Jean-Christian Imbeault <jc(at)mega-bucks(dot)co(dot)jp> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: IN vs EXIIST |
Date: | 2002-09-19 18:08:27 |
Message-ID: | 3D8A129B.74C3A70B@nsd.ca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)postgresql(dot)org)
From | Date | Subject | |
---|---|---|---|
Next Message | Johnson, Shaunn | 2002-09-19 18:09:00 | Re: killing process question |
Previous Message | Jean-Luc Lachance | 2002-09-19 17:57:14 | Re: IN vs EXIIST |