Re: [PERFORM] Increasing select max(datecol) from bilkaib where datecol<=date'2008-11-01' and (cr='00' or db='00') speed

[Vladimir Sitnikov]

On Wed, Nov 12, 2008 at 9:02 AM, Andrus <[EMAIL PROTECTED]> 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