So kellaaeg is a time? Your best bet here would be to create an index that is an actual timestamp comprised of both kuupaev and kellaaeg. You could do this with to_timestamp by concatinating both fields together, or it may be easier to replace the space in kellaaeg with a colon and cast it to time, then add the two:
kuupaev + replace( kellaaeg, ' ', ':' )::time
I know you can't alter the table, but can you create a view on top of the table? If you did that, you could have a real timestamp field in the view that is calculated from kuupaev and kellaaeg and you can create a functional index that uses the same calculation. That would be the easiest way to use this.
On 10/2/14, 3:49 PM, Andrus wrote:
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.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
