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

On Wed, Nov 12, 2008 at 9:02 AM, Andrus <kobruleht2(at)hot(dot)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