From: Nikolai Zhubr <n-a-zhubr@yandex.ru>
To: pgsql-performance@postgresql.org
Subject: Index scan / Index cond limitation or ?
Date: Thu, 14 Oct 2010 19:49:33 +0400
Message-ID: <4CB7268D.4060901@yandex.ru> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: n-a-zhubr@yandex.ru
Subject: Re: Index scan / Index cond limitation or ?
In-Reply-To: <4CB7268D.4060901@yandex.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox