Re: [COMMITTERS] pgsql: Install some slightly realistic cost estimation
От | Tom Lane |
---|---|
Тема | Re: [COMMITTERS] pgsql: Install some slightly realistic cost estimation |
Дата | |
Msg-id | 10770.1114053785@sss.pgh.pa.us обсуждение исходный текст |
Ответ на | Re: [COMMITTERS] pgsql: Install some slightly realistic cost estimation (Christopher Kings-Lynne <chriskl@familyhealth.com.au>) |
Ответы |
Re: [COMMITTERS] pgsql: Install some slightly realistic cost estimation
(Mike Rylander <mrylander@gmail.com>)
|
Список | 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
В списке pgsql-hackers по дате отправления:
Следующее
От: Tom LaneДата:
Сообщение: Re: Postgres: pg_hba.conf, md5, pg_shadow, encrypted passwords