Postgres 查询优化(强制索引扫描) [英] Postgres query optimization (forcing an index scan)

问题描述

以下是我的查询.我试图让它使用索引扫描，但它只会 seq 扫描.

Below is my query. I am trying to get it to use an index scan, but it will only seq scan.

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

By the way the metric_data table has 130 million rows. The metrics table has about 2000 rows.

metric_data 表列:

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

如何让这个查询使用我的 PRIMARY KEY 索引?

How can I get this query to use my PRIMARY KEY index?

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[]))

推荐答案

出于测试的目的，您可以通过禁用"来强制使用索引顺序扫描 - 仅在当前会话中最佳:

For testing purposes you can force the use of the index by "disabling" sequential scans - best in your current session only:

SET enable_seqscan = OFF;

不要不要在高效的服务器上使用它.此处的手册中的详细信息.

Do not use this on a productive server. Details in the manual here.

我引用了禁用"，因为您实际上无法禁用顺序表扫描.但是现在任何其他可用的选项对于 Postgres 都是可取的.这将证明 (metric_id, t) 上的多列索引可以使用 - 只是不如前导列上的索引有效.

I quoted "disabling", because you cannot actually disable sequential table scans. But any other available option is now preferable for Postgres. This will prove that the multicolumn index on (metric_id, t) can be used - just not as effective as an index on the leading column.

通过将 PRIMARY KEY(以及用于在幕后实现它的索引)中的列顺序切换为 (t, metric_id).或者创建一个附加索引，其中包含这样的反向列.

You probably get better results by switching the order of columns in your PRIMARY KEY (and the index used to implement it behind the curtains with it) to (t, metric_id). Or create an additional index with reversed columns like that.

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

您通常不必通过手动干预来强制执行更好的查询计划.如果设置 enable_seqscan = OFF 导致 更好的计划，则您的数据库中可能有问题.考虑这个相关的答案:

You do not normally have to force better query plans by manual intervention. If setting enable_seqscan = OFF leads to a much better plan, something is probably not right in your database. Consider this related answer:

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

这篇关于Postgres 查询优化(强制索引扫描)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！