pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Yeb Havinga <yebhavinga@gmail.com>
To: Kenneth Marshall <ktm@rice.edu>
Cc: Benoit Delbosc <bdelbosc@nuxeo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Bad query plan inside EXISTS clause
Date: Wed, 10 Mar 2010 15:05:20 +0100
Message-ID: <4B97A720.4080304@gmail.com> (raw)
In-Reply-To: <4B97A3C8.6000509@gmail.com>
References: <4B979DFC.7080509@nuxeo.com>
	<20100310134342.GA29320@it.is.rice.edu>
	<4B97A3C8.6000509@gmail.com>

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




view thread (8+ messages)  latest in thread

Message-ID: <4B97A720.4080304@gmail.com>
Permalink:  ../4B97A720.4080304@gmail.com/
Also on:    postgresql.org/message-id/4B97A720.4080304@gmail.com

 · 

reply
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: yebhavinga@gmail.com, ktm@rice.edu, bdelbosc@nuxeo.com
  Subject: Re: Bad query plan inside EXISTS clause
  In-Reply-To: <4B97A720.4080304@gmail.com>

* 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