Re: bitmapscan test, no success, bs is not faster - Mailing list pgsql-hackers

Pavel Stehule <stehule@kix.fsv.cvut.cz> writes:
> I tested bitmap scan and maybe I didnt find good examples, but with bitmap 
> scan is slower than hashjoin. Only when I use non otiptimized SELECT bps 
> was little bit faster. All my SELECTs are equal.

Bitmap scans can't possibly be any faster for cases where the indexscan
only fetches one row, which is true of all your test cases AFAICS.

It should be at least marginally faster than the old code for cases
involving overlapping ORed conditions, that isWHERE some-indexable-condition OR some-other-indexable-condition
where the conditions retrieve some of the same rows.

But I think the real win will come on ANDing of distinct indexes, that
isWHERE condition-for-index-A AND condition-for-index-B
where neither of the index conditions is individually very selective but
together they select just a few rows.  Before, the optimizer could only
choose one index or the other ... but now it can use both.

An example in the regression database is

regression=# explain analyze select * from tenk1 where hundred between 1 and 10 and thousand between 1 and 100;
                                                     QUERY PLAN
 

------------------------------------------------------------------------------------------------------------------------------------------Bitmap
HeapScan on tenk1  (cost=19.91..234.07 rows=94 width=244) (actual time=7.372..8.560 rows=100 loops=1)  Recheck Cond:
((hundred>= 1) AND (hundred <= 10) AND (thousand >= 1) AND (thousand <= 100))  ->  BitmapAnd  (cost=19.91..19.91
rows=94width=0) (actual time=7.094..7.094 rows=0 loops=1)        ->  Bitmap Index Scan on tenk1_hundred
(cost=0.00..9.62rows=937 width=0) (actual time=3.210..3.210 rows=1000 loops=1)              Index Cond: ((hundred >= 1)
AND(hundred <= 10))        ->  Bitmap Index Scan on tenk1_thous_tenthous  (cost=0.00..10.04 rows=1007 width=0) (actual
time=3.147..3.147rows=1000 loops=1)              Index Cond: ((thousand >= 1) AND (thousand <= 100))Total runtime:
9.505ms
 
(8 rows)

In 8.0 this looks like

regression=# explain analyze select * from tenk1 where hundred between 1 and 10 and thousand between 1 and 100;
                                   QUERY PLAN
 
-------------------------------------------------------------------------------------------------------Seq Scan on
tenk1 (cost=0.00..558.00 rows=99 width=244) (actual time=0.171..69.189 rows=100 loops=1)  Filter: ((hundred >= 1) AND
(hundred<= 10) AND (thousand >= 1) AND (thousand <= 100))Total runtime: 70.013 ms
 
(3 rows)

The optimizer is a bit off on the relative merits of seqscan and
indexscan for this case, but even the indexscan is not in the same
ballpark, because it has to choose just one index to use:

regression=# set enable_seqscan to 0;
SET
regression=# explain analyze select * from tenk1 where hundred between 1 and 10 and thousand between 1 and 100;
                                               QUERY PLAN
 

-------------------------------------------------------------------------------------------------------------------------------Index
Scanusing tenk1_hundred on tenk1  (cost=0.00..1455.48 rows=99 width=244) (actual time=10.762..24.454 rows=100 loops=1)
IndexCond: ((hundred >= 1) AND (hundred <= 10))  Filter: ((thousand >= 1) AND (thousand <= 100))Total runtime: 25.384
ms
(4 rows)

        regards, tom lane