Postgres查询优化（强制使用索引扫描）

Question

以下是我的查询。我尝试使用索引扫描，但它只会顺序扫描。

顺便说一下，metric_data表有1.3亿行。metrics表大约有2000行。

metric_data表列：

  metric_id integer
, t timestamp
, d double precision
, PRIMARY KEY (metric_id, t)

如何让这个查询使用我的主键索引？

SELECT
    S.metric,
    D.t,
    D.d
FROM metric_data D
INNER JOIN metrics S
    ON S.id = D.metric_id
WHERE S.NAME = ANY (ARRAY ['cpu', 'mem'])
  AND D.t BETWEEN '2012-02-05 00:00:00'::TIMESTAMP
              AND '2012-05-05 00:00:00'::TIMESTAMP;

解释：

Hash Join  (cost=271.30..3866384.25 rows=294973 width=25)
  Hash Cond: (d.metric_id = s.id)
  ->  Seq Scan on metric_data d  (cost=0.00..3753150.28 rows=29336784 width=20)
        Filter: ((t >= '2012-02-05 00:00:00'::timestamp without time zone)
             AND (t <= '2012-05-05 00:00:00'::timestamp without time zone))
  ->  Hash  (cost=270.44..270.44 rows=68 width=13)
        ->  Seq Scan on metrics s  (cost=0.00..270.44 rows=68 width=13)
              Filter: ((sym)::text = ANY ('{cpu,mem}'::text[]))

- Jeff

Answer 1

为了测试目的，您可以通过“禁用”顺序扫描来强制使用索引 - 最好只在当前会话中执行：

SET enable_seqscan = OFF;

不要在生产服务器上使用此操作。 详细信息请参见手册。

我引用了“禁用”一词，因为您实际上不能禁用顺序表扫描。但对于Postgres，现在任何其他可用选项都更可取。这将证明（metric_id，t）上的多列索引是可以使用的，但效果不如指向主导列的索引好。

通过交换PRIMARY KEY中列的顺序（并在幕后使用它实施的索引）以（t，metric_id）的顺序，您可能会获得更好的结果。或者创建一个带有反转列的附加索引。

• 复合索引是否也适用于第一个字段的查询？

通常情况下，您不需要通过手动干预来强制执行更好的查询计划。如果将enable_seqscan = OFF设置导致显着更好的计划，则您的数据库可能有问题。请考虑此相关答案：

• 如何防止PostgreSQL有时选择错误的查询计划

- Erwin Brandstetter

---

设置这个标志使得上面的查询在我的机器上只需要150毫秒，相比之下原来需要45秒。谢谢！ - Jeff

非常有教育意义的答案。而且结果令人难以置信。 - klin

@Jeff：我在我的答案中添加了另一个提示。 - Erwin Brandstetter

感谢您的见解。在最后一句中，应该是 enable_seqscan = OFF 而不是 enable_seq_scan = OFF。 - ngu

@muluhumu：谢谢，已修复。 - Erwin Brandstetter

手册中没有关于为什么使用提示是错误的内容。它只说应该将它们视为临时解决方案，但没有任何依据支持这种说法。我正在处理一个查询，它在自己运行时需要1500毫秒，但在没有参数的TVF中，它会进行完整扫描，运行超过2分钟。禁用完整扫描可以解决问题。它即将投入生产！PostgreSQL社区对提示的诋毁需要结束！所有其他主要数据库都支持它们。查询优化器的作者并不是绝对可靠的。 - quickdraw

Answer 2

在这种情况下，您无法强制进行索引扫描，因为这样做不会使其更快。

您当前在metric_data(metric_id,t)上拥有索引，但服务器无法利用此索引进行查询，因为它需要仅通过metric_data.t进行区分（没有metric_id），但是没有这样的索引。服务器可以使用复合索引中的子字段，但仅从开头开始。例如，通过metric_id搜索将能够使用此索引。

如果您在metric_data(t)上创建另一个索引，则您的查询将利用该索引，并且将运行得更快。

此外，您应确保在metrics(id)上拥有索引。

- mvp

---

这不完全正确。多列索引也可以仅在第二个字段上使用。尽管没有那么有效。请参考dba.SE上的相关问题。 - Erwin Brandstetter

Answer 3

你尝试过使用：

WHERE S.NAME = ANY (VALUES ('cpu'), ('mem')) 而不是 ARRAY

像这里所示。

- Gabriel Bastos

Answer 4

看起来你缺少适当的外键约束：

CREATE TABLE metric_data
( metric_id integer
, t timestamp
, d double precision
, PRIMARY KEY (metric_id, t)
, FOREIGN KEY metrics_xxx_fk (metric_id) REFERENCES metrics (id)
)

并且在表格指标中：

CREATE TABLE metrics
( id INTEGER PRIMARY KEY
...
);

同时请检查您的统计数据是否足够（并且足够细粒度，因为您打算选择 metrics_data 表的 0.2%）

- joop