sql - 使用 `date_trunc` 的索引不会自动使用

Question

我有一个metrics带有这个索引的表：

CREATE INDEX "metrics_measurementsReportedAt_metrics" ON "metrics" ("metrics", date_trunc('day', "measurementsReportedAt" at time zone 'America/New_York'));

问题是该列不以某种方式使用此索引：

EXPLAIN ANALYZE  SELECT *
  FROM metrics
  WHERE
    "metrics"."measurementsReportedAt" >= date_trunc('day', (NOW() - interval '1 hour') at time zone 'America/New_York');
                                                             QUERY PLAN                                                              
-------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on metrics  (cost=0.00..2101.76 rows=570 width=56) (actual time=4.383..17.626 rows=589 loops=1)
   Filter: ("measurementsReportedAt" >= date_trunc('day'::text, timezone('America/New_York'::text, (now() - '01:00:00'::interval))))
   Rows Removed by Filter: 17178
 Planning time: 1.050 ms
 Execution time: 17.700 ms
(5 rows)

在我禁用 seq 扫描后：SET enable_seqscan = OFF;

                                                                  QUERY PLAN                                                                           
---------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on metrics  (cost=7481.69..8664.19 rows=570 width=56) (actual time=5.264..6.113 rows=589 loops=1)
   Recheck Cond: ("measurementsReportedAt" >= date_trunc('day'::text, timezone('America/New_York'::text, (now() - '01:00:00'::interval))))
   Heap Blocks: exact=266
   ->  Bitmap Index Scan on "metrics_resourceId_measurementsReportedAt_key"  (cost=0.00..7481.55 rows=570 width=0) (actual time=5.123..5.123 rows=589 loops=1)
         Index Cond: ("measurementsReportedAt" >= date_trunc('day'::text, timezone('America/New_York'::text, (now() - '01:00:00'::interval))))
 Planning time: 0.289 ms
 Execution time: 6.255 ms
(7 rows)

查询开始运行得更快。为什么 postgres 在这里不使用索引？

标签： sqlpostgresqlindexingquery-performance