On Wed, 12 Nov 2008, Vladimir Sitnikov wrote:
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'))
Oops, yes, I missed the "OR" in the query. This rewrite is good - my suggested index would not have helped.
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';
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.
Matthew -- Contrary to popular belief, Unix is user friendly. It just happens to be very selective about who its friends are. -- Kyle Hearn -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
