From: Jim Carroll <jim@carroll.com>
To: pgsql-performance@postgresql.org
Subject: Postgres inconsistent use of Index vs. Seq Scan
Date: Fri, 13 Mar 2015 17:27:29 -0400
Message-ID: <011801d05dd4$8226b890$867429b0$@carroll.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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.
I've created a simplistic example that I believe demonstrates the issue,
notice this first query is sequential
CREATE TABLE node
(
id SERIAL PRIMARY KEY,
vid INTEGER
);
CREATE INDEX x ON node(vid);
INSERT INTO node(vid) VALUES (1),(2);
EXPLAIN ANALYZE
SELECT *
FROM node
WHERE NOT vid IN (1);
Seq Scan on node (cost=0.00..36.75 rows=2129 width=8) (actual
time=0.009..0.010 rows=1 loops=1)
Filter: (vid <> 1)
Rows Removed by Filter: 1
Total runtime: 0.025 ms
But if we invert the query to IN, you'll notice that it now decided to use
the index
EXPLAIN ANALYZE
SELECT *
FROM node
WHERE vid IN (2);
Bitmap Heap Scan on node (cost=4.34..15.01 rows=11 width=8) (actual
time=0.017..0.017 rows=1 loops=1)
Recheck Cond: (vid = 1)
-> Bitmap Index Scan on x (cost=0.00..4.33 rows=11 width=0) (actual
time=0.012..0.012 rows=1 loops=1)
Index Cond: (vid = 1)
Total runtime: 0.039 ms
Can anyone shed any light on this? Specifically, is there a way to re-write
out NOT IN to work with the index (when obviously the result set is not as
simplistic as just 1 or 2).
We are using Postgres 9.2 on CentOS 6.6
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: jim@carroll.com
Subject: Re: Postgres inconsistent use of Index vs. Seq Scan
In-Reply-To: <011801d05dd4$8226b890$867429b0$@carroll.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