Re: How to find greatest record before known values fast

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.