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

>
> This query finds initial balance date befeore given date.

If you are not interested in other balances except initial ones (the ones
that have '00') the best way is to create partial indices that I have
suggested.
That will keep size of indices small, while providing good performance
(constant response time)

> bilkaib table contains several year transactions so it is large.
>
That is not a problem for the particular case. However, when you evaluate
query performance, it really makes sense giving number of rows in each table
(is 100K rows a "large" table? what about 10M rows?) and other properties
of the data stored in the table (like number of rows that have cr='00')

> Alternatively if you create an index on (cr, bilkaib) and one on (db,
> bilkaib) then you will be able to use other values in the query too.
>
That means if you create one index on biklaib (cr, datecol) and another
index on (db, datecol) you will be able to improve queries like
select greatest(
(select max(date) from bilkaib where datecol<=date'2008-11-01' and
cr=XXX),
(select max(date) from bilkaib where datecol<=date'2008-11-01' and
db=YYY)).
with arbitrary XXX and YYY. I am not sure if you really want this.

> I'm sorry I do'nt understand this.
> What does the (cr, bilkaib) syntax mean?

I believe that should be read as (cr, datecol).

> Should I create two functions indexes and re-write query as Vladimir
> suggests or is there better appoach ?

I am afraid PostgreSQL is not smart enough to rewrite query with "or" into
two separate index scans. There is no way to improve the query significantly
without rewriting it.

Note: for this case indices on (datecol), (cr) and (db) are not very
helpful.

Regards,
Vladimir Sitnikov