sql - 如何加快max()查询

在PostgreSql 8.4查询中

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'

需要3秒钟来运行:

"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"

如何在PostgreSQL 8.4.4中加快速度？
表结构如下。
algsa表在kuupaev上有索引，也许可以使用？
或者可以更改查询以添加其他一些索引以使其快速。表中的现有列无法更改。

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);

更新

尝试过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.

但查询运行时间仍为2.7秒。

为什么有30000 rows in sample。是不是太多了，应该减少吗？

最佳答案

在旧版本的PostgreSQL中，这是一个已知的问题-但它似乎已由8.4解决。实际上，docs for 8.0有警告，但docs for 8.1没有。

因此，至少由于这个原因，您不需要升级主要版本。但是，您应该升级到当前的8.4系列版本8.4.16，因为您缺少数年的错误修复和调整值(value)。

真正的问题是，您在表达式上使用max而不是简单的值，并且该表达式没有功能索引。

您可以尝试在表达式kuupaev||kellaaeg上创建索引...但我怀疑您存在数据模型问题，并且通过修复数据模型还有更好的解决方案。

看来kuupaev是kuupäev或日期，而kellaaeg可能是时间。如果是这样:永远不要使用串联(||)运算符来组合日期和时间；使用间隔加法，例如kuupaev + kellaaeg。而不是char，您应该使用数据类型time或interval，并对CHECK加上kellaaeg约束，具体取决于它的含义以及是否限制为24小时。或者，更好的是，使用timestamp类型(用于本地时间)或timestamp with time zone类型(用于全局时间)的单个字段来存储组合的日期和时间。

如果执行此操作，则可以在合并的列上创建一个简单的索引，该索引将同时替换kellaaeg和kuupaev，并将其用于min和max等。如果您只需要日期部分或时间部分，请使用date_trunc，extract和date_part函数；请参阅文档。

参见this earlier answer以获得另一个示例，其中分开的date和time列是一个坏主意。

您仍然应该计划升级到9.2。从8.4升级到9.2的升级路径并不是很粗糙，您只需要注意默认情况下standard_conforming_strings的设置为开，并将bytea_output从escape更改为hex即可。在过渡和移植过程中，两者都可以设置回8.4的默认值。 8.4将不再受支持。