Skip site navigation (1) Skip section navigation (2)

Postgres not using indices defined on my table with certain queries using "in"

From: "s anwar" <sanwar(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Postgres not using indices defined on my table with certain queries using "in"
Date: 2007-07-04 22:44:00
Message-ID: 3e3c86f90707041544i6962429cha1e415a755cf9744@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
The two queries blow require radically different query times 1600ms vs 10ms:

select * from test where ock in (1800,1810);
select * from test where ock = 1800 or ock = 1810;

The first query does not use any of the indices defined. I can't
figure out why. How can I make Postgres to use the indices.

In comparison, in the following two queries consume 42ms vs 92ms and
both use the indices defined:

select * from test where ock in (1800,1801);
select * from test where ock = 1800 or ock = 1801;

Below is my table definition and the indices that I've defined on it.
I've gone overboard with the indices trying to understand the behavior
that Postgres is exhibiting. My table contains 161000 records.

      Table "public.test"
 Column |   Type   | Modifiers
--------+----------+-----------
 ock    | integer  |
 ick    | smallint |
 det    | smallint |
Indexes:
    "test_ock_1800" btree (ock) WHERE ock >= 1800 AND ock <= 1800
    "test_ock_1800_1809" btree (ock) WHERE ock >= 1800 AND ock <= 1809
    "test_ock_1801" btree (ock) WHERE ock >= 1801 AND ock <= 1801
    "test_ock_1802" btree (ock) WHERE ock >= 1802 AND ock <= 1802
    "test_ock_1803" btree (ock) WHERE ock >= 1803 AND ock <= 1803
    "test_ock_1804" btree (ock) WHERE ock >= 1804 AND ock <= 1804
    "test_ock_1805" btree (ock) WHERE ock >= 1805 AND ock <= 1805
    "test_ock_1810_1819" btree (ock) WHERE ock >= 1810 AND ock <= 1819
    "test_ock_1820_1829" btree (ock) WHERE ock >= 1820 AND ock <= 1829
    "test_ock_1830_1839" btree (ock) WHERE ock >= 1830 AND ock <= 1839
    "test_ock_1880" btree (ock) WHERE ock >= 1880 AND ock <= 1880


EXPLAIN output using "select * from test where ock in (1800,1810);"
                              QUERY PLAN
----------------------------------------------------------------------
 Seq Scan on test  (cost=100000000.00..100002802.50 rows=103 width=8)
   Filter: (ock = ANY ('{1800,1810}'::integer[]))
(2 rows)


EXPLAIN output using "select * fom test where ock = 1800 or ock = 1810;"
                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Bitmap Heap Scan on test  (cost= 8.95..12.96 rows=103 width=8)
   Recheck Cond: ((ock = 1800) OR (ock = 1810))
   ->  BitmapOr  (cost=8.95..8.95 rows=1 width=0)
         ->  Bitmap Index Scan on test_ock_1800  (cost=0.00..4.26
rows=1 width=0)
               Index Cond: (ock = 1800)
         ->  Bitmap Index Scan on test_ock_1810_1819  (cost=0.00..4.64
rows=1 width=0)
               Index Cond: (ock = 1810)
(7 rows)



EXPLAIN select * from test where ock in (1800, 1801);
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Bitmap Heap Scan on test  (cost=9.31..13.32 rows=103 width=8)
   Recheck Cond: (ock = ANY ('{1800,1801}'::integer[]))
   ->  Bitmap Index Scan on test_ock_1800_1809  (cost=0.00..9.28 rows=1 width=0)
         Index Cond: (ock = ANY ('{1800,1801}'::integer[]))
(4 rows)


EXPLAIN select * from test where ock = 1800 or ock = 1801;
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Bitmap Heap Scan on test  (cost=8.57..12.58 rows=103 width=8)
   Recheck Cond: ((ock = 1800) OR (ock = 1801))
   ->  BitmapOr  (cost=8.57..8.57 rows=1 width=0)
         ->  Bitmap Index Scan on test_ock_1800  (cost= 0.00..4.26
rows=1 width=0)
               Index Cond: (ock = 1800)
         ->  Bitmap Index Scan on test_ock_1801  (cost=0.00..4.26
rows=1 width=0)
               Index Cond: (ock = 1801)
(7 rows)

Thanks.
Saadat.

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2007-07-05 04:08:23
Subject: Re: Postgres not using indices defined on my table with certain queries using "in"
Previous:From: Sean DavisDate: 2007-07-04 20:57:17
Subject: Re: COPY and index updating

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group