To: Benoit Delbosc <bdelbosc@nuxeo.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Bad query plan inside EXISTS clause 
In-reply-to: <4B979DFC.7080509@nuxeo.com> 
References: <4B979DFC.7080509@nuxeo.com>
Comments: In-reply-to Benoit Delbosc <bdelbosc@nuxeo.com>
	message dated "Wed, 10 Mar 2010 14:26:20 +0100"
Date: Wed, 10 Mar 2010 10:44:20 -0500
Message-ID: <15947.1268235860@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Benoit Delbosc <bdelbosc@nuxeo.com> writes:
> I am trying to understand why inside an EXISTS clause the query planner 
>   does not use the index:

I'm not sure this plan is as bad as all that.  The key point is that the
planner is expecting 52517 rows that match that users_md5 value (and the
true number is evidently 51446, so that estimate isn't far off).  That's
about 1/48th of the table.  It knows that the EXISTS case can stop as
soon as it finds one match, so it's betting that a plain seqscan will
hit a match faster than an index lookup would be able to, ie,
seqscanning about 48 tuples is faster than one index lookup.  This might
be a bad bet if the users_md5 values are correlated with physical order,
ie the matches are not randomly scattered but are all towards the end of
the table.  Barring that, though, it could be a good bet if the table
isn't swapped in.  Which is what the default cost parameters are set
up to assume.

I suspect your real complaint is that you expect the table to be swapped
in, in which case what you ought to be doing is adjusting the planner's
cost parameters.  Some playing around here with a similar case suggests
that even a small reduction in random_page_cost would make it prefer an
indexscan for this type of situation.

			regards, tom lane