From: Tom Lane <tgl@sss.pgh.pa.us>
To: Roxanne Reid-Bennett <rox@tara-lu.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: will the planner ever use an index when the condition is <> ?
Date: Sun, 18 Dec 2011 13:31:26 -0500
Message-ID: <3088.1324233086@sss.pgh.pa.us> (raw)
In-Reply-To: <4EEDFE1E.9070303@tara-lu.com>
References: <4EECB57F.3050208@tara-lu.com>
<CAP_rwwmg8ELD3YQgMyUzkXrrRy0GsgqdPZKYUFb-o6=X3YTgNA@mail.gmail.com>
<4EEDFE1E.9070303@tara-lu.com>
Roxanne Reid-Bennett <rox@tara-lu.com> writes:
> On 12/17/2011 11:24 AM, Filip Rembiałkowski wrote:
>> Normally there is no chance it could work,
>> because (a) the planner does not know all possible values of a column,
>> and (b) btree indexes cannot search on "not equal" operator.
> Is there an index type that can check "not equal"?
There is not. It's not so much that it's logically impossible as that
it doesn't seem worth the trouble to implement and maintain, because
most of the time a query like "where x <> constant" is going to fetch
most of the table, and so it would be better done as a seqscan anyway.
If you have a specific case where that's not true, you might consider
a partial index (CREATE INDEX ... WHERE x <> constant). But the details
of that would depend a lot on the queries you're concerned about.
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, rox@tara-lu.com
Subject: Re: will the planner ever use an index when the condition is <> ?
In-Reply-To: <3088.1324233086@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