Re: [COMMITTERS] pgsql: Install some slightly realistic cost estimation - Mailing list pgsql-hackers

Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> Is this totally rad bitmap index support going in? :D  Does it require 
> new index types or does it work with existing ones, etc?

Works with the existing ones.  It's the same idea that's been discussed
several times, eg
http://archives.postgresql.org/pgsql-hackers/2005-02/msg00867.php

It's sort of working now, but since I don't have any planner frontend
code yet except for a truly ugly kluge in orindxpath.c, the only cases
it can deal with are simple ORs:

regression=# explain analyze select * from tenk1 where (unique1 >= 1000 and unique1 < 1500) or (unique1 >= 2000 and
unique1< 2400) or (unique1 > 300 and unique1 < 399);
QUERYPLAN                                                                    
 

-------------------------------------------------------------------------------------------------------------------------------------------------Bitmap
HeapScan on tenk1  (cost=14.12..397.62 rows=991 width=244) (actual time=4.107..12.512 rows=998 loops=1)  Recheck Cond:
(((unique1>= 1000) AND (unique1 < 1500)) OR ((unique1 >= 2000) AND (unique1 < 2400)) OR ((unique1 > 300) AND (unique1 <
399))) ->  BitmapOr  (cost=0.00..14.12 rows=1021 width=0) (actual time=2.942..2.942 rows=0 loops=1)        ->  Bitmap
IndexScan on tenk1_unique1  (cost=0.00..6.14 rows=523 width=0) (actual time=1.547..1.547 rows=500 loops=1)
IndexCond: ((unique1 >= 1000) AND (unique1 < 1500))        ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.43
rows=405width=0) (actual time=1.072..1.072 rows=400 loops=1)              Index Cond: ((unique1 >= 2000) AND (unique1 <
2400))       ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..2.56 rows=93 width=0) (actual time=0.283..0.283
rows=98loops=1)              Index Cond: ((unique1 > 300) AND (unique1 < 399))Total runtime: 16.913 ms
 
(10 rows)

This is only marginally faster than the equivalent 8.0 plan:

regression=# explain analyze select * from tenk1 where (unique1 >= 1000 and unique1 < 1500) or (unique1 >= 2000 and
unique1< 2400) or (unique1 > 300 and unique1 < 399);
   QUERY PLAN                                                                          
 

-------------------------------------------------------------------------------------------------------------------------------------------------------------Index
Scanusing tenk1_unique1, tenk1_unique1, tenk1_unique1 on tenk1  (cost=0.00..2590.19 rows=981 width=244) (actual
time=0.145..14.293rows=998 loops=1)  Index Cond: (((unique1 >= 1000) AND (unique1 < 1500)) OR ((unique1 >= 2000) AND
(unique1< 2400)) OR ((unique1 > 300) AND (unique1 < 399)))Total runtime: 18.712 ms
 
(3 rows)

although it should scale better to large numbers of tuples.

Things will get more interesting once we can AND the results of
unrelated indexes ...
        regards, tom lane