how to speed up max() query

Question

In PostgreSql 8.4 query

explain analyze SELECT 
    max( kuupaev||kellaaeg ) as res
  from ALGSA 
  where laonr=1 and kuupaev <='9999-12-31' and 
     kuupaev||kellaaeg <= '9999-12-3123 59'

Takes 3 seconds to run:

"Aggregate  (cost=3164.49..3164.50 rows=1 width=10) (actual time=2714.269..2714.270 rows=1 loops=1)"
"  ->  Seq Scan on algsa  (cost=0.00..3110.04 rows=21778 width=10) (actual time=0.105..1418.743 rows=70708 loops=1)"
"        Filter: ((kuupaev <= '9999-12-31'::date) AND (laonr = 1::numeric) AND ((kuupaev || (kellaaeg)::text) <= '9999-12-3123 59'::text))"
"Total runtime: 2714.363 ms"

How to speed it up in PostgreSQL 8.4.4 ? Table structure is below. algsa table has index on kuupaev maybe this can be used? Or is it possible to change query to add some other index to make it fast. Exising columns in table cannot changed.

CREATE TABLE firma1.algsa
(
  id serial NOT NULL,
  laonr numeric(2,0),
  kuupaev date NOT NULL,
  kellaaeg character(5) NOT NULL DEFAULT ''::bpchar,
  ... other columns
  CONSTRAINT algsa_pkey PRIMARY KEY (id),
  CONSTRAINT algsa_id_check CHECK (id > 0)
)
);

CREATE INDEX algsa_kuupaev_idx  ON firma1.algsa  USING btree  (kuupaev);

Update

Tried analyze verbose firma1.algsa;

INFO:  analyzing "firma1.algsa"
INFO:  "algsa": scanned 1640 of 1640 pages, containing 70708 live rows and 13 dead rows; 30000 rows in sample, 70708 estimated total rows
Query returned successfully with no result in 1185 ms.

but query run time was still 2.7 seconds.

Why there are 30000 rows in sample . Isn't it too much, should this decreased?

Answer 1

This was a known issue in old versions of PostgreSQL - but it looks like it might've been resolved by 8.4; in fact, the docs for 8.0 have the caveat but the docs for 8.1 do not.

So you don't need to upgrade major versions for this reason, at least. You should however upgrade to the current 8.4 series release 8.4.16, as you're missing several years worth of bug fixes and tweaks.

The real problem here is that you're using max on an expression, not a simple value, and there's no functional index for that expression.

You could try creating an index on the expression kuupaev||kellaaeg ... but I suspect you have data model problems, and that there's a better solution by fixing your data model.

It looks like kuupaev is kuupäev, or date, and kellaaeg might be time. If so: never use the concatenation (||) operator for combining dates and times; use interval addition, eg kuupaev + kellaaeg. Instead of char you should be using the data type time or interval with a CHECK constraint for kellaaeg, depending on what it means and whether it's limited to 24 hours or not. Or, better still, use a single field of type timestamp (for local time) or timestamp with time zone (for global time) to store the combined date and time.

If you do this, you can create a simple index on the combined column that replaces both kellaaeg and kuupaev and use that for min and max among other things. If you need just the date part or just the time part for some things, use the date_trunc, extract and date_part functions; see the documentation.

See this earlier answer for another example of where separate date and time columns are a bad idea.

You should still plan an upgrade to 9.2. The upgrade path from 8.4 to 9.2 isn't too rough, you really just have to watch out for the setting of standard_conforming_strings on by default and the change of bytea_output from escape to hex. Both can be set back to the 8.4 defaults during transition and porting work. 8.4 won't be supported for much longer.

Answer 2

My first instinct would be to try an index:

 create index algsa_laonr_kuupaev_kellaaeg_idx
 on ALGSA (laonr asc, (kuupaev||kellaaeg) desc)

... and try the query as:

 SELECT kuupaev||kellaaeg as res
 from ALGSA 
 where laonr=1 and
       kuupaev||kellaaeg <= '9999-12-3123 59'
 order by
       laonr asc,
       kuupaev||kellaaeg desc
 limit 1