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  (Matthew Wakeling)
Re: Increasing select max(datecol) from bilkaib where datecol<=date'2008-11-01' and (cr='00' or db='00') speed  ("Vladimir Sitnikov")
Re: Increasing select max(datecol) from bilkaib where datecol<=date'2008-11-01' and (cr='00' or db='00') speed  (hubert depesz lubaczewski)
Список: pgsql-performance

Скрыть дерево обсуждения

Increasing select max(datecol) from bilkaib where datecol<=date'2008-11-01' and (cr='00' or db='00') speed  ("Andrus", )
 Re: Increasing select max(datecol) from bilkaib where datecol<=date'2008-11-01' and (cr='00' or db='00') speed  (Matthew Wakeling, )
 Re: Increasing select max(datecol) from bilkaib where datecol<=date'2008-11-01' and (cr='00' or db='00') speed  ("Vladimir Sitnikov", )
  Re: Increasing select max(datecol) from bilkaib where datecol<=date'2008-11-01' and (cr='00' or db='00') speed  (Matthew Wakeling, )
   Re: Increasing select max(datecol) from bilkaib where datecol<=date'2008-11-01' and (cr='00' or db='00') speed  ("Andrus", )
    Re: Increasing select max(datecol) from bilkaib where datecol<=date'2008-11-01' and (cr='00' or db='00') speed  ("Vladimir Sitnikov", )
     Re: Increasing select max(datecol) from bilkaib where datecol<=date'2008-11-01' and (cr='00' or db='00') speed  ("Andrus", )
 Re: Increasing select max(datecol) from bilkaib where datecol<=date'2008-11-01' and (cr='00' or db='00') speed  (hubert depesz lubaczewski, )
  Re: Increasing select max(datecol) from bilkaib wheredatecol<=date'2008-11-01' and (cr='00' or db='00') speed  ("Andrus", )

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 по дате сообщения:

От: "Scott Marlowe"
Дата:
Сообщение: Re: slow full table update
От: "Scott Marlowe"
Дата:
Сообщение: Re: Disk usage question