How to find largest tuple before getting 2 column tuple in postgres quickly
How to increase the speed of operator selection in Postgres 9.0?
The table has the required index. The desired result can be obtained using the index (kuupaev, kelaeg) immediately. However, Postgres scans all lines:
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 a real query, instead of 1, current_date and '23 59 'there are variable parameters.
Both indexes are present on the table, but postgres will not use them. The indexes can be changed and the query can be rewritten if it helps. The structure of the table cannot be changed. char columns cannot be replaced with varchar columns. kuupaev should be a date and kellaaeg should be char (5).
The request contains a reduntant clause kuupaev <=current_date
, but the index is still not being used.
I also tried SELECT max( (kuupaev,kellaaeg ))
it but got an error that the max () function does not exist.
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 with Visual C ++ build 1500, 32-bit"
source to share
No one has answered this question yet
See similar questions:
or similar: