Message-ID: <4CB7268D.4060901@yandex.ru>
Date: Thu, 14 Oct 2010 19:49:33 +0400
From: Nikolai Zhubr <n-a-zhubr@yandex.ru>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; ru;
	rv:1.9.1.5) Gecko/20091204 Thunderbird/3.0
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Index scan / Index cond limitation or ?
Content-Type: text/plain; charset=KOI8-R; format=flowed
Content-Transfer-Encoding: 7bit

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