一个看似简单的PostgreSQL查询的算法改进

Question

高级问题: 我能否更快地使用sum进行order by和group by操作? (在非常大的表格中，例如数百万行，使用PG 8.4)

假设我有这样一张表:

                                 Table "public.summary"
   Column    |       Type        |                      Modifiers
-------------+-------------------+------------------------------------------------------
 ts          | integer           | not null default nextval('summary_ts_seq'::regclass)
 field1      | character varying | not null
 otherfield  | character varying | not null
 country     | character varying | not null
 lookups     | integer           | not null


Indexes:
    "summary_pk" PRIMARY KEY, btree (ts, field1, otherfield, country)
    "ix_summary_country" btree (country)
    "ix_summary_field1" btree (field1)
    "ix_summary_otherfield" btree (otherfield)
    "ix_summary_ts" btree (ts)

我需要查询的是：

select summary.field1,
    summary.country,
    summary.ts,
    sum(summary.lookups) as lookups,
from summary
where summary.country = 'za' and
    summary.ts = 1275177600
group by summary.field1, summary.country, summary.ts
order by summary.ts, lookups desc, summary.field1
limit 100;

(中文：在特定的(ts,country)下，前100个field1的“热门程度”是指与任何匹配行的查找次数之和，而不考虑其他字段的值)

有没有什么办法可以真正加快这个过程？从算法上看，这似乎是一种全表扫描的操作，但我可能会漏掉一些东西。

- Gregg Lind

---

+1：格式整齐，使用序列填充主键！ - OMG Ponies

“LIMIT 100” 的意思是仅返回100行数据，而不是每个ts/country/etc的前100行。 - OMG Ponies

SO的格式化提示，记得全部使用小写字母，尽管这很奇怪 :) - Gregg Lind

一个快速的问题：为什么要返回summary.country和summary.ts？如果它们都在where子句中被明确过滤为单个值，那么（1）在选择列表/分组中包含它们是多余的，因为它们总是相同的值，（2）暗示你在运行查询时已经知道这些值，所以没有理由返回它们。限制正在操作的列集可能有助于性能（尽管这可能是微不足道的）。 - Matthew Wood

马修·伍德 - 干得好！移除它们确实会稍微加快速度，但是可以忽略不计。 - Gregg Lind

Answer 1

对于此查询的任何查询计划都必须扫描与WHERE条件匹配的每一行，并通过分组条件来滚动它们 - 也就是说，工作量与按组进行的输入行数成正比，而不是结果行数。

对于这样的查询，最高效的查询计划可能是单个索引扫描。如果按(country, ts)顺序构建索引，则应该可以实现这一点；使用该索引，此类查询的每个可能查询都会解析为索引上的连续范围。但仍需要内存中排序 - 可能可以通过不同的索引避免这种情况。

正如其他人所说，发布执行计划是您的最佳选择。

- Nick Johnson

Answer 2

为了能够提出建议，您应该发布查询的执行计划。

而且，“OMG Ponies”是正确的：limit 100将限制整个结果为100行，它不会在单个组上起作用！

Postgres Wiki中有一篇很好的文章，解释了如何发布与慢查询相关的问题。

http://wiki.postgresql.org/wiki/SlowQueryQuestions

- user330315

---

我已经更正了问题以反映OMG Ponies的观点。他们是正确的，但这是正确的查询，并且是我想要的。我已经更新了文本以匹配。 - Gregg Lind

Answer 3

在编程方面，按照Nick Johnson的建议，在（国家，ts）上建立索引是最好的选择，并且如果work_mem没有设置得很高，您可能还想提高它。如果需要（并且如果将其设置得非常高，则建议这样做），可以在运行时进行设置。它将有助于将排序保留在内存中，而不会溢出到磁盘（如果发生这种情况）。

对于真正的帮助，我们需要查看EXPLAIN ANALYZE，将其发布在explain.depesz.com上可以使其非常易读。

- rfusca