pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedwhy "WHERE uid NOT IN" is so slow, and EXCEPT in the same situtation is fast?
4+ messages / 2 participants
[nested] [flat]
* why "WHERE uid NOT IN" is so slow, and EXCEPT in the same situtation is fast?
@ 2008-07-31 02:45 Miernik <public@public.miernik.name>
2008-07-31 03:08 ` Re: why "WHERE uid NOT IN" is so slow, and EXCEPT in the same situtation is fast? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Miernik @ 2008-07-31 02:45 UTC (permalink / raw)
To: pgsql-performance
Two queries which do the same thing, first one takes ages to complete
(did wait several minutes and cancelled it), while the second one took
9 seconds? Don't they do the same thing?
miernik=> EXPLAIN SELECT uid FROM locks WHERE uid NOT IN (SELECT uid FROM locks INNER JOIN wys USING (uid, login));
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on locks (cost=38341.39..61365389.71 rows=48446 width=4)
Filter: (NOT (subplan))
SubPlan
-> Materialize (cost=38341.39..39408.47 rows=79508 width=4)
-> Hash Join (cost=3997.27..37989.89 rows=79508 width=4)
Hash Cond: (((wys.uid)::integer = (locks.uid)::integer) AND ((wys.login)::text = (locks.login)::text))
-> Seq Scan on wys (cost=0.00..13866.51 rows=633451 width=16)
-> Hash (cost=2069.91..2069.91 rows=96891 width=16)
-> Seq Scan on locks (cost=0.00..2069.91 rows=96891 width=16)
(9 rows)
Time: 231,634 ms
miernik=> EXPLAIN SELECT uid FROM locks EXCEPT (SELECT uid FROM locks INNER JOIN wys USING (uid, login));
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SetOp Except (cost=59306.12..60188.11 rows=17640 width=4)
-> Sort (cost=59306.12..59747.12 rows=176399 width=4)
Sort Key: "*SELECT* 1".uid
-> Append (cost=0.00..41823.79 rows=176399 width=4)
-> Subquery Scan "*SELECT* 1" (cost=0.00..3038.82 rows=96891 width=4)
-> Seq Scan on locks (cost=0.00..2069.91 rows=96891 width=4)
-> Subquery Scan "*SELECT* 2" (cost=3997.27..38784.97 rows=79508 width=4)
-> Hash Join (cost=3997.27..37989.89 rows=79508 width=4)
Hash Cond: (((wys.uid)::integer = (locks.uid)::integer) AND ((wys.login)::text = (locks.login)::text))
-> Seq Scan on wys (cost=0.00..13866.51 rows=633451 width=16)
-> Hash (cost=2069.91..2069.91 rows=96891 width=16)
-> Seq Scan on locks (cost=0.00..2069.91 rows=96891 width=16)
(12 rows)
Time: 1479,238 ms
miernik=>
--
Miernik
http://miernik.name/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: why "WHERE uid NOT IN" is so slow, and EXCEPT in the same situtation is fast?
2008-07-31 02:45 why "WHERE uid NOT IN" is so slow, and EXCEPT in the same situtation is fast? Miernik <public@public.miernik.name>
@ 2008-07-31 03:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-07-31 03:18 ` Re: why "WHERE uid NOT IN" is so slow, and EXCEPT in the same situtation is fast? Miernik <public@public.miernik.name>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2008-07-31 03:08 UTC (permalink / raw)
To: Miernik <public@public.miernik.name>; +Cc: pgsql-performance
Miernik <public@public.miernik.name> writes:
> Two queries which do the same thing, first one takes ages to complete
> (did wait several minutes and cancelled it), while the second one took
> 9 seconds? Don't they do the same thing?
Hmm, what have you got work_mem set to? The first one would likely
have been a lot faster if it had hashed the subplan; which I'd have
thought would happen with only 80K rows in the subplan result,
except it didn't.
The queries are in fact not exactly equivalent, because EXCEPT
involves some duplicate-elimination behavior that won't happen
in the NOT IN formulation. So I don't apologize for your having
gotten different plans. But you should have gotten a plan less
awful than that one for the NOT IN.
Another issue is that the NOT IN will probably not do what you
expected if the subquery yields any NULLs.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: why "WHERE uid NOT IN" is so slow, and EXCEPT in the same situtation is fast?
2008-07-31 02:45 why "WHERE uid NOT IN" is so slow, and EXCEPT in the same situtation is fast? Miernik <public@public.miernik.name>
2008-07-31 03:08 ` Re: why "WHERE uid NOT IN" is so slow, and EXCEPT in the same situtation is fast? Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-07-31 03:18 ` Miernik <public@public.miernik.name>
2008-07-31 03:55 ` Re: why "WHERE uid NOT IN" is so slow, and EXCEPT in the same situtation is fast? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Miernik @ 2008-07-31 03:18 UTC (permalink / raw)
To: pgsql-performance
On Wed, Jul 30, 2008 at 11:08:06PM -0400, Tom Lane wrote:
> Hmm, what have you got work_mem set to? The first one would likely
> have been a lot faster if it had hashed the subplan; which I'd have
> thought would happen with only 80K rows in the subplan result,
> except it didn't.
work_mem = 1024kB
The machine has 48 MB total RAM and is a Xen host.
> The queries are in fact not exactly equivalent, because EXCEPT
> involves some duplicate-elimination behavior that won't happen
> in the NOT IN formulation. So I don't apologize for your having
> gotten different plans.
But if use EXCEPT ALL?
> Another issue is that the NOT IN will probably not do what you
> expected if the subquery yields any NULLs.
In this specific query I think it is not possible for the subquery to
have NULLs, because its an INNER JOIN USING (the_only_column_in_the
_result, some_other_column_also). If any "uid" column of any row would
have been NULL, it wouldn't appear in that INNER JOIN, no?
--
Miernik
http://miernik.name/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: why "WHERE uid NOT IN" is so slow, and EXCEPT in the same situtation is fast?
2008-07-31 02:45 why "WHERE uid NOT IN" is so slow, and EXCEPT in the same situtation is fast? Miernik <public@public.miernik.name>
2008-07-31 03:08 ` Re: why "WHERE uid NOT IN" is so slow, and EXCEPT in the same situtation is fast? Tom Lane <tgl@sss.pgh.pa.us>
2008-07-31 03:18 ` Re: why "WHERE uid NOT IN" is so slow, and EXCEPT in the same situtation is fast? Miernik <public@public.miernik.name>
@ 2008-07-31 03:55 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2008-07-31 03:55 UTC (permalink / raw)
To: Miernik <public@public.miernik.name>; +Cc: pgsql-performance
Miernik <public@public.miernik.name> writes:
> On Wed, Jul 30, 2008 at 11:08:06PM -0400, Tom Lane wrote:
>> Hmm, what have you got work_mem set to? The first one would likely
>> have been a lot faster if it had hashed the subplan; which I'd have
>> thought would happen with only 80K rows in the subplan result,
>> except it didn't.
> work_mem = 1024kB
Try increasing that ... I don't recall the exact per-row overhead
but I'm quite sure it's more than 8 bytes. Ten times that would
likely get you to a hash subquery plan.
> The machine has 48 MB total RAM and is a Xen host.
48MB is really not a sane amount of memory to run a modern database
in. Maybe you could make it go with sqlite or some other tiny-footprint
DBMS, but Postgres isn't focused on that case.
>> The queries are in fact not exactly equivalent, because EXCEPT
>> involves some duplicate-elimination behavior that won't happen
>> in the NOT IN formulation. So I don't apologize for your having
>> gotten different plans.
> But if use EXCEPT ALL?
Fraid not, EXCEPT ALL has yet other rules for how it deals with
duplicates.
>> Another issue is that the NOT IN will probably not do what you
>> expected if the subquery yields any NULLs.
> In this specific query I think it is not possible for the subquery to
> have NULLs,
Okay, just wanted to point out a common gotcha.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2008-07-31 03:55 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-07-31 02:45 why "WHERE uid NOT IN" is so slow, and EXCEPT in the same situtation is fast? Miernik <public@public.miernik.name>
2008-07-31 03:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-07-31 03:18 ` Miernik <public@public.miernik.name>
2008-07-31 03:55 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox