Postgres Pro
Downloads
Home   >   mailing lists

Re: Increasing select max(datecol) from bilkaib where datecol<=date'2008-11-01' and (cr='00' or db='00') speed - Mailing list pgsql-performance

From Vladimir Sitnikov
Subject Re: Increasing select max(datecol) from bilkaib where datecol<=date'2008-11-01' and (cr='00' or db='00') speed
Date
Msg-id 1d709ecc0811120928n3c183df5kb7b7d50dee6f73ed@mail.gmail.com
Whole thread Raw
In response to Increasing select max(datecol) from bilkaib where datecol<=date'2008-11-01' and (cr='00' or db='00') speed  ("Andrus" <kobruleht2@hot.ee>)
Responses Re: Increasing select max(datecol) from bilkaib where datecol<=date'2008-11-01' and (cr='00' or db='00') speed  (Matthew Wakeling <matthew@flymine.org>)
List pgsql-performance
Tree view


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 by date:

Previous
From: Matthew Wakeling
Date:
Subject: Re: Increasing select max(datecol) from bilkaib where datecol<=date'2008-11-01' and (cr='00' or db='00') speed
Next
From: Matthew Wakeling
Date:
Subject: Re: Increasing select max(datecol) from bilkaib where datecol<=date'2008-11-01' and (cr='00' or db='00') speed