Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 36910633987
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Fri, 12 Feb 2010 16:07:49 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 15132-07
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Fri, 12 Feb 2010 20:07:38 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by mail.postgresql.org (Postfix) with ESMTP id 944E3632D8B
	for <pgsql-performance@postgresql.org>;
	Fri, 12 Feb 2010 16:07:38 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id o1CK7YKn026773;
	Fri, 12 Feb 2010 15:07:34 -0500 (EST)
To: Karl Denninger <karl@denninger.net>
cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>,
	Bryce Nesbitt <bryce2@obviously.com>, pgsql-performance@postgresql.org
Subject: Re: 512,
	600ms query becomes 7500ms... but why? Postgres 8.3 query planner
	quirk?
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>
Comments: In-reply-to Karl Denninger <karl@denninger.net>
	message dated "Fri, 12 Feb 2010 10:43:10 -0600"
Date: Fri, 12 Feb 2010 15:07:34 -0500
Message-ID: <26772.1266005254@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 201002/212
X-Sequence-Number: 37633

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