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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-novice by date

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