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
Дата: ,
Msg-id: alpine.DEB.1.10.0811121730500.4666@aragorn.flymine.org
(см: обсуждение, исходный текст)
Ответ на: 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")
Список: 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", )

On Wed, 12 Nov 2008, Vladimir Sitnikov wrote:
> 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'))

Oops, yes, I missed the "OR" in the query. This rewrite is good - my
suggested index would not have helped.

> 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';

Alternatively if you create an index on (cr, bilkaib) and one on (db,
bilkaib) then you will be able to use other values in the query too.

Matthew

--
Contrary to popular belief, Unix is user friendly. It just happens to be
very selective about who its friends are.                 -- Kyle Hearn


В списке pgsql-performance по дате сообщения:

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