sql - 如何加速 max() 查询

Question

在 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 行。是不是太多了，这个要不要减少？

Answer 1

这是旧版本 PostgreSQL 中的一个已知问题 - 但看起来它可能已在 8.4 中得到解决；事实上，docs for 8.0有警告但docs for 8.1不要。

所以你至少不需要因为这个原因升级主要版本。但是，您应该升级到当前的 8.4 系列版本 8.4.16，因为您错过了数 年 的错误修复和调整。

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

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

看起来 kuupaev 是 kuupäev 或日期，而 kellaaeg 可能是时间。如果是这样:永远不要使用连接 (||) 运算符来组合日期和时间；使用区间加法，例如 kuupaev + kellaaeg。而不是 char 您应该使用数据类型 time 或 interval 以及 kellaaeg 的 CHECK 约束，取决于它的含义以及是否限制为 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 将不再受支持。