提高PostgreSQL聚合性能

Question

如何在PostgreSQL中增加执行MAX(id)聚合查询的速度？

我有一些与id相关的记录，可以在一秒内用COUNT()计算出来。

select count(id) as cnt from mytable where ref_id=2660

row   cnt
1     2844

然而，当我尝试使用MAX()查找最新的记录ID时，查询需要近5分钟。

select max(id) as id from mytable where ref_id=2660

这很令人惊讶，因为我发现PG在处理更复杂的查询时非常快。为什么在处理相对较少的记录时会出现如此大的查询时间差异？如何才能最好地提高查询性能？

编辑：这是上述MAX()选择的查询计划：

"Result  (cost=219.84..219.85 rows=1 width=0)"
"  InitPlan 1 (returns $0)"
"    ->  Limit  (cost=0.00..219.84 rows=1 width=4)"
"          ->  Index Scan Backward using mytable_pkey on mytable  (cost=0.00..773828.42 rows=3520 width=4)"
"                Filter: ((id IS NOT NULL) AND (ref_id = 2660))"

- Cerin

---

也许你需要给‘id’添加一个索引。 - a2j

请确保您具有（ref_id，id）索引。 - arthurprs

(ref_id, id) 索引起作用了！将其设置为您的答案，我会接受它。 - Cerin

Answer 1

我在谷歌上搜索了一下，似乎PostgreSQL（8.4及以下版本）不喜欢MAX和MIN，它会对表进行顺序扫描以获得结果。没有查询计划和版本，很难说这是否适用于您的情况。

您可以尝试以下解决方法。

SELECT id from mytable WHERE ref_id=2660 ORDER BY id DESC LIMIT 1

编辑：确保你有一个(ref_id，id)索引，否则不可避免地会进行表扫描/排序。

- arthurprs

---

这个程序大约需要一分钟才能运行，比我的查询快得多，但仍然相对缓慢。 - Cerin

Answer 2

我正在使用Postgres 8.4，并且可以说这可能是Postgres优化器的一个错误，在涉及最小值和最大值聚合函数的查询中没有使用索引。将我的查询从
Select max(field) from table 改为
Select field from table order by field limit 1
我的查询执行时间从10秒缩短到少于一秒钟。 当然，您可以针对所涉及的列定义一个索引，否则Postgres将进行seq_scan。

- Pikachu