DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:cc:subject
	:references:in-reply-to:content-type:content-transfer-encoding;
	b=bMMSDRAHpovpGT+d4DKbMH32CeorNSMC4eJq5CeFfnhtrEgzVRKByXNPCx3NfQoV36
	EiySxVH3SwEgi21aQf4Za5HoBDSj4zwkAoKQDB74C5y07RWd0CG0pvllzFj1mwe4zY2r
	rhGvN/PlJFybFZ8hZiGIKaJpp/QpnPLGYMT5w=
Message-ID: <4B97A720.4080304@gmail.com>
Date: Wed, 10 Mar 2010 15:05:20 +0100
From: Yeb Havinga <yebhavinga@gmail.com>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
To: Kenneth Marshall <ktm@rice.edu>
CC: Benoit Delbosc <bdelbosc@nuxeo.com>,
 pgsql-performance@postgresql.org
Subject: Re: Bad query plan inside EXISTS clause
References: <4B979DFC.7080509@nuxeo.com>
	<20100310134342.GA29320@it.is.rice.edu>
	<4B97A3C8.6000509@gmail.com>
In-Reply-To: <4B97A3C8.6000509@gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Yeb Havinga wrote:
> Kenneth Marshall wrote:
>> EXISTS matches NULLs too and since they are not indexed a
>> sequential scan is needed to check for them. Try using
>> IN instead.
>>   
> This is nonsense in more than one way.
Hit ctrl-return a bit too slow - exists does not match null but a set of 
records, that is either empty or not empty. Also it is possible to index 
table columns with nulls, and then the indexes can still be used. 
Besides filtering record sets with expressions, indexes are also used 
for ordering. There the effect of indexes with nulls can be seen: where 
to put them: in front or after the non nulls? So indexes can be 
perfectly used in conjunction with nulls. I found the original mail 
rather intriguing and played with an example myself a bit, but could not 
repeat the behavior (9.0 devel version), in my case the exists used an 
index. Maybe it has something to do with the fact that the planner 
estimates to return 50000 rows, even when the actual numbers list only 1 
hit. In the exists case, it can stop at the first hit. In the select all 
rows case, it must return all rows. Maybe a better plan emerges with 
better statistics?

regards,
Yeb Havinga