From: Vladimir Sitnikov <sitnikov.vladimir@gmail.com>
To: Andrus <kobruleht2@hot.ee>
Cc: pgsql-performance@postgresql.org
Subject: Re: Increasing select max(datecol) from bilkaib where datecol<=date'2008-11-01' and (cr='00' or db='00') speed
Date: Wed, 12 Nov 2008 09:28:53 -0800
Message-ID: <1d709ecc0811120928n3c183df5kb7b7d50dee6f73ed@mail.gmail.com> (raw)
In-Reply-To: <gff26k$2etc$1@news.hub.org>
References: <gff26k$2etc$1@news.hub.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sitnikov.vladimir@gmail.com, kobruleht2@hot.ee
Subject: Re: Increasing select max(datecol) from bilkaib where datecol<=date'2008-11-01' and (cr='00' or db='00') speed
In-Reply-To: <1d709ecc0811120928n3c183df5kb7b7d50dee6f73ed@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox