Index scan / Index cond limitation or ?

From: Nikolai Zhubr <n-a-zhubr(at)yandex(dot)ru>
To: pgsql-performance(at)postgresql(dot)org
Subject: Index scan / Index cond limitation or ?
Date: 2010-10-14 15:49:33
Message-ID: 4CB7268D.4060901@yandex.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hello people,

I'm having trouble to persuade index scan to check all of the conditions
I specify _inside_ index cond. That is, _some_ condition always get
pushed out of index cond and applied later (which will often result, for
my real table contents, in too many unwanted rows initially hit by index
scan and hence randomly slow queries)
An index with all relevant columns does exist of course.

Here goes an example.

create table foo (
id serial primary key,
rec_time timestamp with time zone DEFAULT now(),
some_value integer,
some_data text
);
CREATE INDEX foo_test ON foo (id, rec_time, some_value);
set enable_seqscan = false;
set enable_bitmapscan = true;

explain select id from foo where true
and rec_time > '2010-01-01 22:00:06'
--and rec_time < '2010-10-14 23:59'
and some_value in (1, 2)
and id > 123

This one works perfectly as I want it (and note "and rec_time < ... "
condition is commented out):

Bitmap Heap Scan on foo (cost=13.18..17.19 rows=1 width=4)
Recheck Cond: ((id > 123) AND (rec_time > '2010-01-01
22:00:06+03'::timestamp with time zone) AND (some_value = ANY
('{1,2}'::integer[])))
-> Bitmap Index Scan on foo_test (cost=0.00..13.18 rows=1 width=0)
Index Cond: ((id > 123) AND (rec_time > '2010-01-01
22:00:06+03'::timestamp with time zone) AND (some_value = ANY
('{1,2}'::integer[])))"

Now, as soon as I enable "and rec_time < ... " condition, I get the
following:

explain select id from foo where true
and rec_time > '2010-01-01 22:00:06'
and rec_time < '2010-10-14 23:59'
and some_value in (1, 2)
and id > 123

Bitmap Heap Scan on foo (cost=8.59..13.94 rows=1 width=4)
Recheck Cond: ((id > 123) AND (rec_time > '2010-01-01
22:00:06+03'::timestamp with time zone) AND (rec_time < '2010-10-14
23:59:00+04'::timestamp with time zone))
Filter: (some_value = ANY ('{1,2}'::integer[]))
-> Bitmap Index Scan on foo_test (cost=0.00..8.59 rows=2 width=0)
Index Cond: ((id > 123) AND (rec_time > '2010-01-01
22:00:06+03'::timestamp with time zone) AND (rec_time < '2010-10-14
23:59:00+04'::timestamp with time zone))

So, "in (1, 2)" condition is not in Index Cond anymore! Why is that? How
can I push it back?

SELECT version();
PostgreSQL 8.3.1, compiled by Visual C++ build 1400
but the behaviour seems exactly the same in 9.0 (just checked it briefly).

Thank you!
Please CC me, I'm not on the list.

Nikolai

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Cédric Villemain 2010-10-14 18:47:43 Re: How does PG know if data is in memory?
Previous Message Jesper Krogh 2010-10-14 15:29:40 Re: Slow count(*) again...