Thread: Bitmap scan when it is not needed

From:
"Ilia Kantor"
Date:

explain analyze select * from objects_hier where tg && array[0] and id <10000;

 

Bitmap Heap Scan on objects_hier  (cost=4.79..8.80 rows=1 width=337) (actual time=0.110..0.110 rows=0 loops=1)

   Recheck Cond: ((tg && '{0}'::integer[]) AND (id < 10000))

   ->  BitmapAnd  (cost=4.79..4.79 rows=1 width=0) (actual time=0.106..0.106 rows=0 loops=1)

         ->  Bitmap Index Scan on gistbla2  (cost=0.00..2.10 rows=30 width=0) (actual time=0.042..0.042 rows=0 loops=1)

               Index Cond: (tg && '{0}'::integer[])

         ->  Bitmap Index Scan on ohid  (cost=0.00..2.44 rows=1240 width=0) (actual time=0.058..0.058 rows=1255 loops=1)

               Index Cond: (id < 10000)

 

I see, Bitmap is going to AND my indexes.. It read one with less number of rows estimated the first (right)..

It found 0 records at gistbla2 index…

 

Then why is it reading ohid ? 

 

Maybe a quickfix is possible for cases when 0 records is found to stop reading other AND elements..

 

From:
Tom Lane
Date:

"Ilia Kantor" <> writes:
> Maybe a quickfix is possible for cases when 0 records is found to stop
> reading other AND elements..

Not sure how useful this will be in practice (since the planner tends
not to bother ANDing unselective indexes at all), but it's easy enough
to do ... so I did it.

            regards, tom lane