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

It's interesting, that Tom's example behaves different on my notebook:

8.02 (default optimization)
regression=# explain analyze select * from tenk1 where hundred between 1 and 10 and thousand between 1 and 100;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Index Scan using tenk1_thous_tenthous on tenk1 (cost=0.00..142.91 rows=1 width=244) (actual time=0.369..7.378 rows=100 loops=1)
Index Cond: ((thousand >= 1) AND (thousand <= 100))
Filter: ((hundred >= 1) AND (hundred <= 10))
Total runtime: 8.100 ms
(4 rows)

CVS HEAD
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=20.14..236.96 rows=98 width=244) (actual time=3.116..6.857 rows=100 loops=1)
Recheck Cond: ((hundred >= 1) AND (hundred <= 10) AND (thousand >= 1) AND (thousand <= 100))
-> BitmapAnd (cost=20.14..20.14 rows=98 width=0) (actual time=3.009..3.009 rows=0 loops=1)
-> Bitmap Index Scan on tenk1_hundred (cost=0.00..9.83 rows=971 width=0) (actual time=1.497..1.497 rows=1000 loops=1)
Index Cond: ((hundred >= 1) AND (hundred <= 10))
-> Bitmap Index Scan on tenk1_thous_tenthous (cost=0.00..10.07 rows=1011 width=0) (actual time=1.179..1.179 rows=1000 loops=1)
Index Cond: ((thousand >= 1) AND (thousand <= 100))
Total runtime: 7.568 ms
(8 rows)

On Tue, 26 Apr 2005, Tom Lane wrote:

> 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
>
> ---------------------------(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
>

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83