planner doesn't use bitmap index

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: planner doesn't use bitmap index
Date: 2015-10-29 16:31:34
Message-ID: CAFj8pRD0q=p16b3Pa3TwaLRvw0JdLcEMMmui-jY2_WC_KpbTHw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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=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)

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Fujii Masao 2015-10-29 16:41:13 Re: [DOCS] max_worker_processes on the standby
Previous Message Colin 't Hart 2015-10-29 16:31:04 Did the "Full-text search in PostgreSQL in milliseconds" patches land?