时间戳

首页 > 解决方案 > 在没有时区字段的时间戳上查询不使用索引

postgresql - 在没有时区字段的时间戳上查询不使用索引

问题描述

我有一个超过 300 万行的表，其中一列creationdate名为timestamp without time zone.

我在上面创建了几个索引，例如：

"idx_routingtasks_creationdate" btree (creationdate)
"idx_routingtasks_creationdate2" btree ((creationdate::date))

creationdate::date当按（按日期转换）过滤时，idx_routingtasks_creationdate不使用索引：

explain analyze select * from routingtasks where creationdate::date < (now() - interval '1 day')::date;                                                                                 
                                                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on routingtasks  (cost=0.00..1315631.93 rows=2811638 width=715) (actual time=186642.012..413763.643 rows=2800659 loops=1)
   Filter: ((creationdate)::date < ((now() - '1 day'::interval))::date)
   Rows Removed by Filter: 212248
 Planning time: 0.195 ms
 Execution time: 413875.829 ms
(5 rows)

不按日期投射时也是如此：

explain analyze select * from routingtasks where creationdate < now() - interval '1 day';
                                                            QUERY PLAN                                                             
-----------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on routingtasks  (cost=0.00..1300588.39 rows=2918447 width=715) (actual time=72089.312..327288.333 rows=2876756 loops=1)
   Filter: (creationdate < (now() - '1 day'::interval))
   Rows Removed by Filter: 141052
 Planning time: 0.104 ms
 Execution time: 327401.745 ms
(5 rows)

如何在creationdate列上创建索引以允许此过滤器使用它？

标签： postgresql

解决方案

答案就在执行计划的这一部分：

Seq Scan ... (actual ... rows=2876756 ...)
  ...
  Rows Removed by Filter: 141052

由于无论如何都会返回几乎所有行，因此使用顺序扫描并丢弃过滤掉的少数行是处理查询的最有效方法。

如果你想验证，暂时

SET enable_seqscan = off;

如果可能，使 PostgreSQL 避免顺序扫描。然后您可以测试查询执行是否变得更快。

推荐阅读