From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jim Carroll <jim@carroll.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Postgres inconsistent use of Index vs. Seq Scan
Date: Fri, 13 Mar 2015 18:04:06 -0400
Message-ID: <25960.1426284246@sss.pgh.pa.us> (raw)
In-Reply-To: <011801d05dd4$8226b890$867429b0$@carroll.com>
References: <011801d05dd4$8226b890$867429b0$@carroll.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
"Jim Carroll" <jim@carroll.com> writes:
> I'm having difficulty understanding what I perceive as an inconsistency in
> how the postgres parser chooses to use indices. We have a query based on NOT
> IN against an indexed column that the parser executes sequentially, but
> when we perform the same query as IN, it uses the index.
What you've got here is a query that asks for all rows with vid <> 1.
Not-equals is not an indexable operator according to Postgres, and there
would not be much point in making it one, since it generally implies
having to scan the majority of the table.
If, indeed, 99% of your table has vid = 1, then there would be some point
in trying to use an index to find the other 1%; but you'll have to
formulate the query differently (perhaps "vid > 1" would do?) or else
use a properly-designed partial index.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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, jim@carroll.com
Subject: Re: Postgres inconsistent use of Index vs. Seq Scan
In-Reply-To: <25960.1426284246@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