问题描述
以下是我的查询。我试图让它使用索引扫描，但它只会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亿行。 指标表有大约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;
EXPLAIN：
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;
我引用了禁用，因为您实际上无法禁用顺序表扫描。但是现在任何其他可用选项都适用于Postgres。这将证明（metric_id，t） 上的多列索引可以使用 - 只是不如前导列上的索引有效。
Details in the manual here. 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.
- Is a composite index also good for queries on the first field?
您通常不必通过手动干预强制更好的查询计划。如果设置 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:
- Keep PostgreSQL from sometimes choosing a bad query plan
这篇关于Postgres查询优化（强制索引扫描）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！