Обсуждение: planner doesn't use bitmap index
Hi
There is interesting query on stackoverflow http://stackoverflow.com/questions/33418157/query-too-slow-in-postgresql-in-table-with-12m-rows - and it looks like planner issue.set enable_seqscan to off;
create table test1(a int, b int);
create index on test1(a);
postgres=# explain select * from test1 where a = 1 and b = 2;
QUERY PLAN
═════════════════════════════════════════════════════════════════════════
Bitmap Heap Scan on test1 (cost=4.24..14.94 rows=1 width=8)
Recheck Cond: (a = 1)
Filter: (b = 2)
-> Bitmap Index Scan on test1_a_idx (cost=0.00..4.24 rows=11 width=0)
Index Cond: (a = 1)
(5 rows)
create table test2(a timestamp with time zone, b int);
create index on test2(a);
QUERY PLAN
═════════════════════════════════════════════════════════════════════════
Bitmap Heap Scan on test1 (cost=4.24..14.94 rows=1 width=8)
Recheck Cond: (a = 1)
Filter: (b = 2)
-> Bitmap Index Scan on test1_a_idx (cost=0.00..4.24 rows=11 width=0)
Index Cond: (a = 1)
(5 rows)
create table test2(a timestamp with time zone, b int);
create index on test2(a);
analyze test2;
-- I was surprised, so following query can use index
postgres=# explain select a from test2 where a at time zone 'America/Santiago' >= now() at time zone 'America/Santiago' ;
QUERY PLAN
══════════════════════════════════════════════════════════════════════════════════════════════
Index Only Scan using test2_a_idx on test2 (cost=0.13..12.18 rows=1 width=8)
Filter: (timezone('America/Santiago'::text, a) >= timezone('America/Santiago'::text, now()))
(2 rows)
postgres=# explain select a from test2 where a at time zone 'America/Santiago' >= now() at time zone 'America/Santiago' ;
QUERY PLAN
══════════════════════════════════════════════════════════════════════════════════════════════
Index Only Scan using test2_a_idx on test2 (cost=0.13..12.18 rows=1 width=8)
Filter: (timezone('America/Santiago'::text, a) >= timezone('America/Santiago'::text, now()))
(2 rows)
but
why, the index isn't used in this case?
postgres=# explain select a,b from test2 where a at time zone 'America/Santiago' >= now() at time zone 'America/Santiago' ;
QUERY PLAN
══════════════════════════════════════════════════════════════════════════════════════════════
Seq Scan on test2 (cost=10000000000.00..10000000001.04 rows=1 width=12)
Filter: (timezone('America/Santiago'::text, a) >= timezone('America/Santiago'::text, now()))
(2 rows)
postgres=# explain select a,b from test2 where a at time zone 'America/Santiago' >= now() at time zone 'America/Santiago' ;
QUERY PLAN
══════════════════════════════════════════════════════════════════════════════════════════════
Seq Scan on test2 (cost=10000000000.00..10000000001.04 rows=1 width=12)
Filter: (timezone('America/Santiago'::text, a) >= timezone('America/Santiago'::text, now()))
(2 rows)
or in this case?
postgres=# explain select a from test2 where a at time zone 'America/Santiago' >= now() at time zone 'America/Santiago' and b = 1 ;
QUERY PLAN
════════════════════════════════════════════════════════════════════════════════════════════════════════════
Seq Scan on test2 (cost=10000000000.00..10000000001.05 rows=1 width=8)
Filter: ((b = 1) AND (timezone('America/Santiago'::text, a) >= timezone('America/Santiago'::text, now())))
(2 rows)
postgres=# explain select a from test2 where a at time zone 'America/Santiago' >= now() at time zone 'America/Santiago' and b = 1 ;
QUERY PLAN
════════════════════════════════════════════════════════════════════════════════════════════════════════════
Seq Scan on test2 (cost=10000000000.00..10000000001.05 rows=1 width=8)
Filter: ((b = 1) AND (timezone('America/Santiago'::text, a) >= timezone('America/Santiago'::text, now())))
(2 rows)
Composite index fixes it. But it should to work without composite index too?
create index on test2(a,b);
postgres=# explain select a from test2 where a at time zone 'America/Santiago' >= now() at time zone 'America/Santiago' and b = 1 ;
QUERY PLAN
══════════════════════════════════════════════════════════════════════════════════════════════
Index Only Scan using test2_a_b_idx on test2 (cost=0.13..12.18 rows=1 width=8)
Index Cond: (b = 1)
Filter: (timezone('America/Santiago'::text, a) >= timezone('America/Santiago'::text, now()))
(3 rows)
create index on test2(a,b);
postgres=# explain select a from test2 where a at time zone 'America/Santiago' >= now() at time zone 'America/Santiago' and b = 1 ;
QUERY PLAN
══════════════════════════════════════════════════════════════════════════════════════════════
Index Only Scan using test2_a_b_idx on test2 (cost=0.13..12.18 rows=1 width=8)
Index Cond: (b = 1)
Filter: (timezone('America/Santiago'::text, a) >= timezone('America/Santiago'::text, now()))
(3 rows)
Tested on master.
Regards
Pavel
Pavel Stehule <pavel.stehule@gmail.com> writes:
> -- I was surprised, so following query can use index
> postgres=# explain select a from test2 where a at time zone
> 'America/Santiago' >= now() at time zone 'America/Santiago' ;
> QUERY
> PLAN
> ══════════════════════════════════════════════════════════════════════════════════════════════
> Index Only Scan using test2_a_idx on test2 (cost=0.13..12.18 rows=1
> width=8)
> Filter: (timezone('America/Santiago'::text, a) >=
> timezone('America/Santiago'::text, now()))
> (2 rows)
This plan isn't actually "using" the index in any meaningful way; it's
applying the where condition as a filter. It happens to be sane to use
the index as a dumb data source, because it can be an index-only scan, and
that might (if you're lucky and don't hit too many recheckable rows) be
cheaper than a seqscan. But we don't consider plain indexscans as worth
the trouble to consider in such cases, because a full-table plain
indexscan can never beat a seqscan, either in the planner's cost model or
in reality.
> why, the index isn't used in this case?
> postgres=# explain select a,b from test2 where a at time zone
> 'America/Santiago' >= now() at time zone 'America/Santiago' ;
Can't be an index-only scan because of the use of b, so there's no
possible way that this can be better than a seqscan.
regards, tom lane
2015-10-29 19:20 GMT+01:00 Tom Lane <tgl@sss.pgh.pa.us>:
Pavel Stehule <pavel.stehule@gmail.com> writes:
> -- I was surprised, so following query can use index
> postgres=# explain select a from test2 where a at time zone
> 'America/Santiago' >= now() at time zone 'America/Santiago' ;
> QUERY
> PLAN
> ══════════════════════════════════════════════════════════════════════════════════════════════
> Index Only Scan using test2_a_idx on test2 (cost=0.13..12.18 rows=1
> width=8)
> Filter: (timezone('America/Santiago'::text, a) >=
> timezone('America/Santiago'::text, now()))
> (2 rows)
This plan isn't actually "using" the index in any meaningful way; it's
applying the where condition as a filter. It happens to be sane to use
the index as a dumb data source, because it can be an index-only scan, and
that might (if you're lucky and don't hit too many recheckable rows) be
cheaper than a seqscan. But we don't consider plain indexscans as worth
the trouble to consider in such cases, because a full-table plain
indexscan can never beat a seqscan, either in the planner's cost model or
in reality.
> why, the index isn't used in this case?
> postgres=# explain select a,b from test2 where a at time zone
> 'America/Santiago' >= now() at time zone 'America/Santiago' ;
Can't be an index-only scan because of the use of b, so there's no
possible way that this can be better than a seqscan.
I understand.
Than you for explanation.
Regards
Pavel
regards, tom lane