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 <[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
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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
