Skip site navigation (1) Skip section navigation (2)

Redundant bitmap index scans on smallint column

From: Marti Raudsepp <marti(at)juffo(dot)org>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Redundant bitmap index scans on smallint column
Date: 2011-09-05 07:52:44
Message-ID: CABRT9RDM95h9As29sQBnA_jUh120zqVND_4xhsq=ea6O3R2_FA@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Hi list,

This simple query shouldn't cause two bitmap index scans:
EXPLAIN select * from test where b='0';

 Bitmap Heap Scan on test  (cost=1056.68..8200.12 rows=29839 width=314)
   Recheck Cond: ((b = 0) AND (b = 0::smallint))
   ->  BitmapAnd  (cost=1056.68..1056.68 rows=5237 width=0)
         ->  Bitmap Index Scan on test_i_idx  (cost=0.00..485.45
rows=29839 width=0)
         ->  Bitmap Index Scan on test_b_c_idx  (cost=0.00..556.06
rows=29839 width=0)
               Index Cond: (b = 0::smallint)

One of the indexes is a partial index, and the other is just a simple index.

Apparently, for some reason, the '0' is expanded into both an integer
and a smallint literal and the planner thinks it can reduce rows by
checking the condition twice?

This is how I reproduced the issue:
set enable_indexscan=off;
create table test as select i, (i/30000)::smallint as b, 0::int as c,
repeat('x', 300) as filler from generate_series(1,170000) i;
create index test_i_idx on test (i) where b=0;
create index test_b_c_idx on test (b,c);
analyze test;
explain select * from test where b='0';

Reproduced on PostgreSQL 8.3.15, 8.4.8, 9.0.4, 9.1rc1 and 9.2devel.
However, this issue does NOT occur on 8.2.21

When I write the literal without quotes, I get a more sensible plan:
EXPLAIN select * from test where b=0;

 Bitmap Heap Scan on test  (cost=493.79..8260.88 rows=30007 width=314)
   Recheck Cond: (b = 0)
   ->  Bitmap Index Scan on test_i_idx  (cost=0.00..486.29 rows=30007 width=0)

Also, *before* analyzing the table, I get a good plan:
EXPLAIN select * from test where b='0';

 Bitmap Heap Scan on test  (cost=18.86..2450.01 rows=850 width=42)
   Recheck Cond: (b = 0::smallint)
   ->  Bitmap Index Scan on test_b_c_idx  (cost=0.00..18.64 rows=850 width=0)
         Index Cond: (b = 0::smallint)


Regards,
Marti Raudsepp

Responses

pgsql-hackers by date

Next:From: Magnus HaganderDate: 2011-09-05 10:38:15
Subject: Re: WAL "low watermark" during base backup
Previous:From: Jeremy DrakeDate: 2011-09-05 07:07:49
Subject: Re: Re: [COMMITTERS] pgsql: Remove "fmgr.h" include in cube contrib --- caused crash on a Ge

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group