查询在没有时区字段的时间戳上不使用索引 [英] Query not using index on timestamp without time zone field

问题描述

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

I have a table with more than 3 million rows, one column named creationdate is a timestamp without time zone.

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

I created a couple of indexes on it, like:

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

当按creationdate::date(按日期投射)过滤时，不使用索引idx_routingtasks_creationdate:

When filter by creationdate::date (casting by date) the index idx_routingtasks_creationdate is not used:

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 列上创建索引以允许此过滤器使用它?

How can I create an index on the creationdate column to allow this filter use it?

推荐答案

答案就在这部分执行计划中:

The answer lies in this part of the execution plan:

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

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

Since almost all rows are returned anyway, using a sequential scan and discarding the few rows that are filtered out is the most efficient way to process the query.

如果你想验证一下，暂时

If you want to verify that, temporarily

SET enable_seqscan = off;

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

to make PostgreSQL avoid a sequential scan if possible. Then you can test if query execution gets faster or not.

这篇关于查询在没有时区字段的时间戳上不使用索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！