时间戳

首页 > 解决方案 > 如何在 where 子句中将部分索引用于计算的过滤条件？

sql - 如何在 where 子句中将部分索引用于计算的过滤条件？

问题描述

假设我有这个简单的查询：

EXPLAIN ANALYZE
SELECT
    COUNT(*)
FROM
    audiences a
WHERE
    a.created_at >= (current_date - INTERVAL '5 days');

这是一个 1GB+ 表，created_at列上有部分索引。当我运行这个查询时，它会进行顺序扫描并且不使用我的索引，这显然需要很多时间：

Aggregate  (cost=345853.43..345853.44 rows=1 width=8) (actual time=27126.426..27126.426 rows=1 loops=1)
  ->  Seq Scan on audiences a  (cost=0.00..345840.46 rows=5188 width=0) (actual time=97.564..27124.317 rows=8029 loops=1)
        Filter: (created_at >= (('now'::cstring)::date - '5 days'::interval))
        Rows Removed by Filter: 2215612
Planning time: 0.131 ms
Execution time: 27126.458 ms

另一方面，如果我有一个像这样的“硬编码”（或预先计算的）值：

EXPLAIN ANALYZE
SELECT
    COUNT(*)
FROM
    audiences a
WHERE
    a.created_at >= TIMESTAMP '2020-10-16 00:00:00';

它将利用 created_at 上的索引：

Aggregate  (cost=253.18..253.19 rows=1 width=8) (actual time=1014.655..1014.655 rows=1 loops=1)
  ->  Index Only Scan using index_audiences_on_created_at on audiences a  (cost=0.29..240.21 rows=5188 width=0) (actual time=1.308..1011.071 rows=8029 loops=1)
        Index Cond: (created_at >= '2020-10-16 00:00:00'::timestamp without time zone)
        Heap Fetches: 6185
Planning time: 1.878 ms
Execution time: 1014.716 ms

如果可以的话，我会使用 ORM 并生成具有正确值的查询，但我不能。有没有办法可以预先计算这个时间戳并通过普通 SQL 在 WHERE 子句中使用它？

添加一点我的设置的技术信息。

PostgreSQL 版本：9.6.11

created_at 列类型为：timestamp

指数："index_audiences_on_created_at" btree (created_at) WHERE created_at > '2020-10-01 00:00:00'::timestamp without time zone

标签： sqlpostgresql

解决方案

这不是确切的答案。但可以根据具体情况做

正如你有谓词(created_at > '2020-10-01 00:00:00'::timestamp without time zone)，如果过滤条件大于谓词条件。然后你可以在条件前面加上where

EXPLAIN ANALYZE
SELECT
    COUNT(*)
FROM
    audiences a
WHERE
    a.created_at >= TIMESTAMP '2020-10-16 00:00:00'
    and
    a.created_at >= (current_date - INTERVAL '5 days');

注意：可能是代替TIMESTAMP ，你必须把TIMESTAMP without time zone或TIMESTAMP with time zone。取决于列类型

推荐阅读