Re: How to find greatest record before known values fast - Mailing list pgsql-general

So kellaaeg is a time? Your best bet here would be to create an index that is an actual timestamp comprised of both
kuupaevand kellaaeg. You could do this with to_timestamp by concatinating both fields together, or it may be easier to
replacethe 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
timestampfield in the view that is calculated from kuupaev and kellaaeg and you can create a functional index that uses
thesame 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
mustbe 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.