Re: [COMMITTERS] pgsql: Install some slightly realistic cost estimation

Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)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);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan 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 Index Scan on tenk1_unique1 (cost=0.00..6.14 rows=523 width=0) (actual time=1.547..1.547 rows=500 loops=1)
Index Cond: ((unique1 >= 1000) AND (unique1 < 1500))
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.43 rows=405 width=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=98 loops=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 Scan using tenk1_unique1, tenk1_unique1, tenk1_unique1 on tenk1 (cost=0.00..2590.19 rows=981 width=244) (actual time=0.145..14.293 rows=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