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
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... |