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

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 
 

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

------------------------------------------------------------------------------------------------------------------------------------------
BitmapHeap 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=98width=0) (actual time=3.009..3.009 rows=0 loops=1)         ->  Bitmap Index Scan on tenk1_hundred
(cost=0.00..9.83rows=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)
(actualtime=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@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 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=1000loops=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=1000loops=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@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83