From: Tom Lane <tgl@sss.pgh.pa.us>
To: Benoit Delbosc <bdelbosc@nuxeo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Bad query plan inside EXISTS clause
Date: Wed, 10 Mar 2010 10:44:20 -0500
Message-ID: <15947.1268235860@sss.pgh.pa.us> (raw)
In-Reply-To: <4B979DFC.7080509@nuxeo.com>
References: <4B979DFC.7080509@nuxeo.com>
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
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, bdelbosc@nuxeo.com
Subject: Re: Bad query plan inside EXISTS clause
In-Reply-To: <15947.1268235860@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