postgresql - 为什么在索引扫描(Postgres 11.2)期间读取了这么多页面？

Question

我们有一个 Postgres 11.2 数据库，它根据复合键存储值的时间序列。给定 1 个或多个键，查询会尝试在给定时间约束的每个时间序列中找到最新值。

当数据未缓存时，我们会遇到查询超时，因为它似乎必须遍历大量页面才能找到数据。

这是 explain 中的相关部分.我们正在获取单个时间序列的数据(在此示例中有 367 个值):

->  Index Scan using quotes_idx on quotes q  (cost=0.58..8.61 rows=1 width=74) (actual time=0.011..0.283 rows=367 loops=1)
Index Cond: ((client_id = c.id) AND (quote_detail_id = qd.id) AND (effective_at <= '2019-09-26 00:59:59+01'::timestamp with time zone) AND (effective_at >= '0001-12-31 23:58:45-00:01:15 BC'::timestamp with time zone))
Buffers: shared hit=374

这是相关索引的定义:

CREATE UNIQUE INDEX quotes_idx ON quotes.quotes USING btree (client_id, quote_detail_id, effective_at);

其中列为 2x int4和一个 timestamptz , 分别。

假设我正在正确读取输出，为什么引擎要遍历 374 页(~3Mb，给定我们的 8kb 页面大小)以返回~26kb 的数据(367 行，宽度为 74 字节)？

当我们增加键的数量(比如 500)时，引擎最终会遍历超过 150k 页(超过 1GB)，如果没有缓存，这会花费大量时间。

请注意，基础表中的平均行大小为 82 字节(超过 11 列)，包含大约 700mi 行。

提前感谢您的任何见解!

Answer 1

在你的索引扫描中找到的 367 行可能存储在 300 多个表 block 中(这在大表中并不奇怪)。因此 PostgreSQL 必须访问所有这些 block 才能得出结果。

如果行都集中在几个表 block 中，这会执行得更好。换句话说，如果索引的逻辑顺序对应于表中行的物理顺序。在 PostgreSQL 术语中，高 correlation将是有益的。

您可以强制 PostgreSQL 以正确的顺序重写整个表

CLUSTER quotes USING quotes_idx;

那么您的查询应该会变得更快。

不过也有一些缺点:

• CLUSTER 正在运行时，无法访问该表。这通常意味着停机时间。

• 在 CLUSTER 之后，性能会很好，但 PostgreSQL 不维护顺序。随后的数据修改将降低相关性。

  为了保持查询的良好执行，您必须定期安排 CLUSTER。