Re: bitmapscan test, no success, bs is not faster

Pavel Stehule <stehule(at)kix(dot)fsv(dot)cvut(dot)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 is
WHERE 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
is
WHERE 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 Heap Scan 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=94 width=0) (actual time=7.094..7.094 rows=0 loops=1)
-> Bitmap Index Scan on tenk1_hundred (cost=0.00..9.62 rows=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.147 rows=1000 loops=1)
Index Cond: ((thousand >= 1) AND (thousand <= 100))
Total runtime: 9.505 ms
(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 Scan using tenk1_hundred on tenk1 (cost=0.00..1455.48 rows=99 width=244) (actual time=10.762..24.454 rows=100 loops=1)
Index Cond: ((hundred >= 1) AND (hundred <= 10))
Filter: ((thousand >= 1) AND (thousand <= 100))
Total runtime: 25.384 ms
(4 rows)

regards, tom lane