Search Postgresql Archives

How to find greatest record before known values fast

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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
 
 
Andrus.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux