如何加速 max() 查询 [英] how to speed up max() query

问题描述

在 PostgreSql 8.4 查询中

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'

运行需要 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 上有索引，也许可以使用?或者是否可以更改查询以添加一些其他索引以使其快速.表中的现有列无法更改.

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

更新

试过分析详细的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 秒.

but query run time was still 2.7 seconds.

为什么样本中有30000行.是不是太多了，这个应该减少吗?

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

推荐答案

这是旧版本 PostgreSQL 中的一个已知问题 - 但看起来它可能已在 8.4 中解决；事实上，8.0 的文档 有警告，但8.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.

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

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.

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

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.

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

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.

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

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.

如果这样做，您可以在组合列上创建一个简单的索引来替换 kellaaeg 和 kuupaev 并将其用于 min和 max 等等.如果您只需要日期部分或某些事情的时间部分，请使用 date_trunc、extract 和 date_part 函数；请参阅文档.

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.

请参阅这个较早的答案，了解将 date 和 time 分开的另一个示例 列是个坏主意.

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

您仍应计划升级到 9.2.从 8.4 到 9.2 的升级路径不是太粗糙，你真的只需要注意 standard_conforming_strings 默认的设置和 bytea_output 从 的变化>escape 到 hex.在过渡和移植工作期间，两者都可以设置回 8.4 默认值.8.4 将不再受支持.

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.

这篇关于如何加速 max() 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！