On Wed, Nov 12, 2008 at 9:02 AM, Andrus <kobruleht2@xxxxxx> 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
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
