Increasing select max(datecol) from bilkaib where datecol<=date'2008-11-01' and (cr='00' or db='00') speed
От | Andrus |
---|---|
Тема | Increasing select max(datecol) from bilkaib where datecol<=date'2008-11-01' and (cr='00' or db='00') speed |
Дата | |
Msg-id | gff26k$2etc$1@news.hub.org обсуждение исходный текст |
Ответы |
Re: Increasing select max(datecol) from bilkaib where
datecol<=date'2008-11-01' and (cr='00' or db='00') speed
Re: Increasing select max(datecol) from bilkaib where datecol<=date'2008-11-01' and (cr='00' or db='00') speed Re: Increasing select max(datecol) from bilkaib where datecol<=date'2008-11-01' and (cr='00' or db='00') speed |
Список | pgsql-performance |
There are columns kuupaev date, cr char(10), db char(10) and regular indexes for all those fields. bilkaib table contains large number of rows. The following query takes too much time. How to make it faster ? I think PostgreSql should use multiple indexes as bitmaps to speed it. I can re-write this query in any way or split to multiple statements if this makes it faster. Andrus. explain analyze select max(kuupaev) from bilkaib where kuupaev<=date'2008-11-01' and (cr='00' or db='00') "Result (cost=339.75..339.76 rows=1 width=0) (actual time=52432.256..52432.260 rows=1 loops=1)" " InitPlan" " -> Limit (cost=0.00..339.75 rows=1 width=4) (actual time=52432.232..52432.236 rows=1 loops=1)" " -> Index Scan Backward using bilkaib_kuupaev_idx on bilkaib (cost=0.00..1294464.73 rows=3810 width=4) (actual time=52432.222..52432.222 rows=1 loops=1)" " Index Cond: (kuupaev <= '2008-11-01'::date)" " Filter: ((kuupaev IS NOT NULL) AND ((cr = '00'::bpchar) OR (db = '00'::bpchar)))" "Total runtime: 52432.923 ms" "PostgreSQL 8.1.4 on i686-pc-linux-gnu, compiled by GCC i686-pc-linux-gnu-gcc (GCC) 3.4.6 (Gentoo 3.4.6-r1, ssp-3.4.5-1.0, pie-8.7.9)"
В списке pgsql-performance по дате отправления: