postgresql 索引状态_postgresql – Postgres查询优化(强制索引扫描)

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

顺便提一下,metric_data表有1.3亿行.度量表有大约2000行.

metric_data表列：

metric_id integer

, t timestamp

, d double precision

, PRIMARY KEY (metric_id, t)

如何获取此查询以使用我的PRIMARY KEY索引？

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