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

["Vladimir Sitnikov" <sitnikov.vladimir@xxxxxxxxx>]

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