Christopher Kings-Lynne <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
