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
Дата
Msg-id 1d709ecc0811120928n3c183df5kb7b7d50dee6f73ed@mail.gmail.com
обсуждение исходный текст
Ответ на Increasing select max(datecol) from bilkaib where datecol<=date'2008-11-01' and (cr='00' or db='00') speed  ("Andrus" <kobruleht2@hot.ee>)
Ответы Re: Increasing select max(datecol) from bilkaib where datecol<=date'2008-11-01' and (cr='00' or db='00') speed
Список pgsql-performance
Дерево обсуждения


On Wed, Nov 12, 2008 at 9:02 AM, Andrus <kobruleht2@hot.ee> wrote:
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 am afraid I do not see a way to use bitmaps to get any improvement here: the server will still need to read the whole indices to figure out the answer.

I suggest you to create two more indices:

create index date_with_zero_cr on bilkaib(date) where cr='00';
create index date_with_zero_db on bilkaib(date) where db='00';

And rewrite query as follows:
select greatest(
   (select max(date) from bilkaib where datecol<=date'2008-11-01' and cr='00'),
   (select max(date) from bilkaib where datecol<=date'2008-11-01' and db='00'))


Regards,
Vladimir Sitnikov

В списке pgsql-performance по дате отправления:

Предыдущее
От: Matthew Wakeling
Дата:
Сообщение: 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