postgresql - Postgres 不在区间查询中使用部分时间戳索引(例如，now() - 区间 '7 days')

Question

我有一个简单的表格，用于存储来自在线仪表的降水读数。这是表定义:

    CREATE TABLE public.precip
    (
        gauge_id smallint,
        inches numeric(8, 2),
        reading_time timestamp with time zone
    )

    CREATE INDEX idx_precip3_id
        ON public.precip USING btree
        (gauge_id)

    CREATE INDEX idx_precip3_reading_time
        ON public.precip USING btree
        (reading_time)

CREATE INDEX idx_precip_last_five_days
    ON public.precip USING btree
    (reading_time)
    TABLESPACE pg_default    WHERE reading_time > '2017-02-26 00:00:00+00'::timestamp with time zone

它变得相当大:大约 3800 万条记录可追溯到 18 个月前。查询很少请求超过 7 天的行，我在 reading_time 字段上创建了部分索引，这样 Postgres 就可以遍历一个小得多的索引。但它并没有在所有查询中使用部分索引。它确实在

上使用了部分索引

explain analyze select * from precip where gauge_id = 208 and reading_time > '2017-02-27' 
            Bitmap Heap Scan on precip  (cost=8371.94..12864.51 rows=1169 width=16) (actual time=82.216..162.127 rows=2046 loops=1)   
            Recheck Cond: ((gauge_id = 208) AND (reading_time > '2017-02-27 00:00:00+00'::timestamp with time zone))
           ->  BitmapAnd  (cost=8371.94..8371.94 rows=1169 width=0) (actual time=82.183..82.183 rows=0 loops=1)
                ->  Bitmap Index Scan on idx_precip3_id  (cost=0.00..2235.98 rows=119922 width=0) (actual time=20.754..20.754 rows=125601 loops=1)
                      Index Cond: (gauge_id = 208)
                ->  Bitmap Index Scan on idx_precip_last_five_days  (cost=0.00..6135.13 rows=331560 width=0) (actual time=60.099..60.099 rows=520867 loops=1) 
    Total runtime: 162.631 ms

但它不在下面使用部分索引。相反，它在 reading_time 上使用完整索引

 explain analyze select * from precip where gauge_id = 208 and reading_time > now() - interval '7 days' 

Bitmap Heap Scan on precip  (cost=8460.10..13007.47 rows=1182 width=16) (actual time=154.286..228.752 rows=2067 loops=1)
   Recheck Cond: ((gauge_id = 208) AND (reading_time > (now() - '7 days'::interval)))
      ->  BitmapAnd  (cost=8460.10..8460.10 rows=1182 width=0) (actual time=153.799..153.799 rows=0 loops=1)
              ->  Bitmap Index Scan on idx_precip3_id  (cost=0.00..2235.98 rows=119922 width=0) (actual time=15.852..15.852 rows=125601 loops=1)
                   Index Cond: (gauge_id = 208)
        ->  Bitmap Index Scan on idx_precip3_reading_time  (cost=0.00..6223.28 rows=335295 width=0) (actual time=136.162..136.162 rows=522993 loops=1)
              Index Cond: (reading_time > (now() - '7 days'::interval))
Total runtime: 228.647 ms

请注意，今天是 2017 年 3 月 5 日，因此这两个查询本质上是在请求行。但似乎 Postgres 不会使用部分索引，除非 where 子句中的时间戳是“硬编码”的。在决定使用哪个索引之前，查询规划器是否未评估 now() - interval '7 days'？我按照第一批响应者之一的建议发布了查询计划。
我写了几个函数(存储过程)来总结过去 6 小时、12 小时 .... 72 小时的降雨量。他们都在查询中使用间隔方法(例如，reading_time > now() - interval '7 days')。我不想将此代码移动到应用程序中以向 Postgres 发送硬编码时间戳。这会产生很多不必要的困惑 PHP 代码。

关于如何鼓励 Postgres 使用部分索引的建议？我的计划是每晚重新定义部分索引的日期范围(删除索引 --> 创建索引)，但如果 Postgres 不打算使用它，这似乎有点愚蠢。

谢谢，

亚历克斯

Answer 1

一般来说，当索引列与常量(字面值)、函数调用进行比较时，可以使用索引，这些常量至少标记为STABLE。 (这意味着在单个语句中，多次调用函数 - 使用相同的参数 - 将产生相同的结果)，以及它们的组合。

now() (这是 current_timestamp 的别名)被标记为 STABLE和 timestamp_mi_interval() (这是运算符 <timestamp> - <interval> 的备份函数)被标记为 IMMUTABLE ，甚至比 STABLE 更严格( now() 、 current_timestamp 和 transaction_timestamp 标记事务的开始， statement_timestamp() 标记语句的开始——仍然是 STABLE —— 但是 clock_timestamp() 给出时钟上看到的时间戳，因此它是 VOLATILE )。

所以理论上，WHERE reading_time > now() - interval '7 days'应该能够在 reading_time 上使用索引柱子。确实如此。但是，由于您定义了部分索引，因此 planner needs to prove the following :

However, keep in mind that the predicate must match the conditions used in the queries that are supposed to benefit from the index. To be precise, a partial index can be used in a query only if the system can recognize that the WHERE condition of the query mathematically implies the predicate of the index. PostgreSQL does not have a sophisticated theorem prover that can recognize mathematically equivalent expressions that are written in different forms. (Not only is such a general theorem prover extremely difficult to create, it would probably be too slow to be of any real use.) The system can recognize simple inequality implications, for example "x < 1" implies "x < 2"; otherwise the predicate condition must exactly match part of the query's WHERE condition or the index will not be recognized as usable. Matching takes place at query planning time, not at run time.

这就是您的查询所发生的情况，它有 and reading_time > now() - interval '7 days' .到时候now() - interval '7 days'评估，计划已经发生。并且 PostgreSQL 无法证明谓词 ( reading_time > '2017-02-26 00:00:00+00' ) 将是 true .但是当你使用 reading_time > '2017-02-27'它可以证明这一点。

You could "guide" the planner具有常量值，像这样:

where gauge_id = 208
and   reading_time > '2017-02-26 00:00:00+00'
and   reading_time > now() - interval '7 days'

规划器通过这种方式意识到，它可以使用部分索引，因为 indexed_col > index_condition和 indexed_col > something_else暗示indexed_col将大于(至少)index_condition .也许它会大于 something_else也一样，但使用索引并不重要。

不过，我不确定这是否是您正在寻找的答案。恕我直言，如果您有大量数据(和 PostgreSQL 9.5+)，一个 BRIN index可能更适合您的需求。