Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YWX7P-0007OC-O9 for pgsql-performance@arkaria.postgresql.org; Fri, 13 Mar 2015 21:27:43 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YWX7P-0002uP-1f for pgsql-performance@arkaria.postgresql.org; Fri, 13 Mar 2015 21:27:43 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YWX7N-0002sU-Mv for pgsql-performance@postgresql.org; Fri, 13 Mar 2015 21:27:41 +0000 Received: from smtp.hck.carroll.com ([216.44.20.203]) by magus.postgresql.org with esmtps (TLS1.0:DHE_RSA_CAMELLIA_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1YWX7I-0004AT-Uh for pgsql-performance@postgresql.org; Fri, 13 Mar 2015 21:27:40 +0000 Received: from AS10 (as10.as.hck.carroll.com [10.64.0.64]) by smtp.hck.carroll.com (8.14.5/8.14.5) with ESMTP id t2DLRT0I044856 for ; Fri, 13 Mar 2015 21:27:29 GMT (envelope-from jim@carroll.com) From: "Jim Carroll" To: 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> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0119_01D05DB2.FB1C4480" X-Mailer: Microsoft Outlook 14.0 Content-Language: en-us Thread-Index: AdBd1H4933teamtdQWSswArkEkcS+g== X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org This is a multipart message in MIME format. ------=_NextPart_000_0119_01D05DB2.FB1C4480 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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 ------=_NextPart_000_0119_01D05DB2.FB1C4480 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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=3D0.00..36.75 rows=3D2129 width=3D8) (actual = time=3D0.009..0.010 rows=3D1 loops=3D1)

  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=3D4.34..15.01 rows=3D11 = width=3D8) (actual time=3D0.017..0.017 rows=3D1 = loops=3D1)

  Recheck Cond: (vid =3D 1)

  ->  Bitmap Index Scan = on x  (cost=3D0.00..4.33 rows=3D11 width=3D0) (actual = time=3D0.012..0.012 rows=3D1 loops=3D1)

        Index Cond: (vid =3D = 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

 

------=_NextPart_000_0119_01D05DB2.FB1C4480--