[GENERAL] How to find greatest record before known values fast

[Andrus]

I’m looking for a way to increase select statement speed in Postgres 9.0.

Table has required index present. Desired result can obtained using index 
(kuupaev,kellaaeg) immediately.
However Postgres scans all rows:

explain analyze SELECT 
        max( kuupaev||kellaaeg ) as res
      from firma2.ALGSA 
      where laonr=1 and kuupaev <=current_date and 
         (kuupaev,kellaaeg) <= ( current_date, '23 59'  )

    "Aggregate  (cost=6932.65..6932.67 rows=1 width=10) (actual 
time=1608.590..1608.592 rows=1 loops=1)"
    "  ->  Seq Scan on algsa  (cost=0.00..6571.49 rows=144464 width=10) (actual 
time=0.032..922.431 rows=144458 loops=1)"
    "        Filter: ((laonr = 1::numeric) AND (kuupaev <= ('now'::text)::date) 
AND (ROW(kuupaev, kellaaeg) <= ROW(('now'::text)::date, '23 59'::bpchar)))"
    "Total runtime: 1608.846 ms"

In real query instead of 1, current_date and '23 59' there are variable 
parameters.

Table has both indexes present but postgres will not use them. 
Indexes can changed and query can re-written if this helps. 
Table structure cannot changed. char columns cannot replaced with varchar 
columns. kuupaev must be date and kellaaeg must be char(5) type.

Query contains reduntant condition `kuupaev <=current_date` but index is still 
not used.

I tried also `SELECT max( (kuupaev,kellaaeg ))` but got error that max() 
function does not exist.

How to speed this query ?


Table structure is :

    CREATE TABLE firma2.algsa
    (
      id serial NOT NULL,
      laonr numeric(2,0),
      kuupaev date NOT NULL,
      kellaaeg character(5) NOT NULL DEFAULT ''::bpchar,
      osak character(10) NOT NULL,
      toode character(20) NOT NULL,
      partii character(15),
      kogus numeric(12,4) NOT NULL DEFAULT 0,
      hind numeric(15,5) NOT NULL DEFAULT 0,
      kulum numeric(15,5) NOT NULL DEFAULT 0,
      tegkogus numeric(12,4),
      stkuupaev date,
      klient character(12),
      masin character(5),
      CONSTRAINT algsa_pkey PRIMARY KEY (id)
    );
    
    
    CREATE INDEX algsa_kuupaev_idx
      ON firma2.algsa
      USING btree
      (kuupaev);
    
    CREATE INDEX algsa_kuupaev_kellaaeg_idx
      ON firma2.algsa
      USING btree
      (kuupaev, kellaaeg);

using

"PostgreSQL 9.0.3, compiled by Visual C++ build 1500, 32-bit"

Posted also in

http://stackoverflow.com/questions/26165745/how-find-greatest-tuple-before-given-2-column-tuple-in-postgres-fast

Andrus.