[HACKERS] planner doesn't use bitmap index

[Pavel Stehule]

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.

I have empty tables test1 and test2

set enable_seqscan to off;
create table test1(a int, b int);
create index on test1(a);
analyze test1;

-- expected behave
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);
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)

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=100.00..101.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=100.00..101.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)

Tested on master.

Regards

Pavel

Re: [HACKERS] planner doesn't use bitmap index

[Tom Lane]

Pavel Stehule  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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] planner doesn't use bitmap index

[Pavel Stehule]

2015-10-29 19:20 GMT+01:00 Tom Lane :

> Pavel Stehule  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
>