From: Tom Lane <tgl@sss.pgh.pa.us>
To: Karl Denninger <karl@denninger.net>
Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: Bryce Nesbitt <bryce2@obviously.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: 512, 600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?
Date: Fri, 12 Feb 2010 15:07:34 -0500
Message-ID: <26772.1266005254@sss.pgh.pa.us> (raw)
In-Reply-To: <4B75851E.6070909@denninger.net>
References: <4B723042.7090406@obviously.com>
<4B751AD5020000250002F248@gw.wicourts.gov>
<4B757C59.8070500@denninger.net>
<4B752934020000250002F24E@gw.wicourts.gov>
<4B75851E.6070909@denninger.net>
Karl Denninger <karl@denninger.net> writes:
> Explain Analyze on the alternative CLAIMS the same query planner time
> (within a few milliseconds) with explain analyze. But if I replace the
> executing code with one that has the alternative ("not exists") syntax
> in it, the system load goes to crap instantly and the execution times
> "in the wild" go bananas.
Could we see the actual explain analyze output, and not some handwaving?
What I would expect 8.4 to do with the NOT EXISTS version is to convert
it to an antijoin --- probably a hash antijoin given that the subtable
is apparently small. That should be a significant win compared to
repeated seqscans as you have now. The only way I could see for it to
be a loss is that that join would probably be performed after the other
subplan tests instead of before. However, the rowcounts for your
original query suggest that all the subplans get executed the same
number of times; so at least on the test values you used here, all
those conditions succeed. Maybe your test values were not
representative of "in the wild" cases, and in the real usage it's
important to make this test before the others.
If that's what it is, you might see what happens when all of the
sub-selects are converted to exists/not exists style, instead of
having a mishmash...
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, karl@denninger.net, Kevin.Grittner@wicourts.gov, bryce2@obviously.com
Subject: Re: 512, 600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?
In-Reply-To: <26772.1266005254@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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