Pavel Stehule <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
