pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedIndex scan / Index cond limitation or ?
3+ messages / 2 participants
[nested] [flat]
* Index scan / Index cond limitation or ?
@ 2010-10-14 15:49 Nikolai Zhubr <n-a-zhubr@yandex.ru>
0 siblings, 1 reply; 3+ messages in thread
From: Nikolai Zhubr @ 2010-10-14 15:49 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Index scan / Index cond limitation or ?
@ 2010-10-15 18:29 Tom Lane <tgl@sss.pgh.pa.us>
parent: Nikolai Zhubr <n-a-zhubr@yandex.ru>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2010-10-15 18:29 UTC (permalink / raw)
To: Nikolai Zhubr <n-a-zhubr@yandex.ru>; +Cc: pgsql-performance
Nikolai Zhubr <n-a-zhubr@yandex.ru> writes:
> So, "in (1, 2)" condition is not in Index Cond anymore! Why is that? How
> can I push it back?
It thinks the indexscan condition is sufficiently selective already.
An = ANY condition like that will force multiple index searches,
one for each of the OR'd possibilities, so it's far from "free" to add
it to the index condition. The planner doesn't think it's worth it.
Perhaps on your real query it is, but there's not much point in
debating about the behavior on this toy table; without realistic
table sizes and up-to-date stats it's impossible to say whether that
choice is correct or not.
> SELECT version();
> PostgreSQL 8.3.1, compiled by Visual C++ build 1400
You really, really, really ought to be running 8.3.something-newer.
We didn't put out the last 11 8.3.x bugfix updates just because
we didn't have anything better to do.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Index scan / Index cond limitation or ?
@ 2010-10-15 21:00 Nikolai Zhubr <n-a-zhubr@yandex.ru>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Nikolai Zhubr @ 2010-10-15 21:00 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
15.10.2010 22:29, Tom Lane:
> Nikolai Zhubr<n-a-zhubr@yandex.ru> writes:
>> So, "in (1, 2)" condition is not in Index Cond anymore! Why is that? How
>> can I push it back?
>
> It thinks the indexscan condition is sufficiently selective already.
> An = ANY condition like that will force multiple index searches,
> one for each of the OR'd possibilities, so it's far from "free" to add
> it to the index condition. The planner doesn't think it's worth it.
Aha, ok. It makes sense then. Is this specific case (=ANY in index cond)
described somewhere with reasonable detail? I always try to RTFM first
and most of the time I can find pretty good hints in the regular manual
already (sufficient as a starting point at least) but this specific
topic seems to be somewhat mysterious.
> Perhaps on your real query it is, but there's not much point in
> debating about the behavior on this toy table; without realistic
> table sizes and up-to-date stats it's impossible to say whether that
> choice is correct or not.
>
>> SELECT version();
>> PostgreSQL 8.3.1, compiled by Visual C++ build 1400
>
> You really, really, really ought to be running 8.3.something-newer.
> We didn't put out the last 11 8.3.x bugfix updates just because
> we didn't have anything better to do.
Yes, I know, and I do appreciate the efforts of postgresql devels to
create updates for older versions too.
This server is internal-only (so it does not see any real world yet).
Anyway, I hope to update everything to 9.0.1 soon.
Thank you!
Nikolai
>
> regards, tom lane
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2010-10-15 21:00 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-10-14 15:49 Index scan / Index cond limitation or ? Nikolai Zhubr <n-a-zhubr@yandex.ru>
2010-10-15 18:29 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-10-15 21:00 ` Nikolai Zhubr <n-a-zhubr@yandex.ru>
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