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.