基于检查约束的分区修剪未按预期工作

Question

为什么下面的查询计划中包含表“ events_201504”？根据我的查询和对该表的检查约束，我希望查询计划程序能够完全修剪它：

database=# \d events_201504
                                   Table "public.events_201504"
    Column     |            Type             |                           Modifiers
---------------+-----------------------------+---------------------------------------------------------------
 id            | bigint                      | not null default nextval('events_id_seq'::regclass)
 created_at    | timestamp without time zone |
Indexes:
    "events_201504_pkey" PRIMARY KEY, btree (id)
    "events_201504_created_at" btree (created_at)
Check constraints:
    "events_201504_created_at_check" CHECK (created_at >= '2015-04-01 00:00:00'::timestamp without time zone AND created_at <= '2015-04-30 23:59:59.999999'::timestamp without time zone)
Inherits: events

时间和配置：

database=# select now();
              now
-------------------------------
 2015-05-25 16:49:20.037815-05

database=# show constraint_exclusion;
 constraint_exclusion
----------------------
 on

查询计划：

database=# explain select count(1) from events where created_at > now() - '1 hour'::interval;
                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=3479.86..3479.87 rows=1 width=0)
   ->  Append  (cost=0.00..3327.90 rows=60784 width=0)
         ->  Seq Scan on events  (cost=0.00..0.00 rows=1 width=0)
               Filter: (created_at > (now() - '01:00:00'::interval))
         ->  Index Only Scan using events_201504_created_at on events_201504  (cost=0.57..4.59 rows=1 width=0)
               Index Cond: (created_at > (now() - '01:00:00'::interval))
         ->  Index Only Scan using events_201505_created_at on events_201505  (cost=0.57..3245.29 rows=60765 width=0)
               Index Cond: (created_at > (now() - '01:00:00'::interval))

Answer 1

您的专栏created_at是timestamp without time zone。

但是now()回报timestamp with time zone。该表达式now() - '1 hour'::interval被强制转换为timestamp [without time zone]，它带来 两个问题 ：

1.） 您没有要求这个，但是表达是不可靠的。其结果取决于在其中执行查询的会话的当前时区设置。
为了使表达清晰，可以使用：

now() AT TIME ZONE 'Europe/London' -- your time zone here

或者只是（在此处阅读手册）：

LOCALTIMESTAMP  -- explicitly take the local time

我会考虑与其合作timestamptz。
都不能解决您的第二个问题：

2.）回答您的问题。
约束排除不起作用。每个文档：

以下警告适用于约束排除：

仅当查询的WHERE子句包含常量（或外部提供的参数）时，约束排除才有效。例如， 无法优化 与
非不变函数CURRENT_TIMESTAMP的比较，因为计划者无法知道函数值在运行时可能属于哪个分区。

大胆强调我的。

now()是的Postgres实现CURRENT_TIMESTAMP。如您在系统目录中所看到的，它仅仅是STABLE，不是IMMUTABLE：

SELECT proname, provolatile FROM pg_proc WHERE proname = 'now';

proname | provolatile
--------+------------
now     | s              -- meaning: STABLE

解决方案

1.） 您可以通过在WHERE条件中提供常量（始终是“不可变的”）来克服此限制：

select count(*) from events
where created_at > '2015-05-25 15:49:20.037815'::timestamp;  -- derived from your example

2.） 或通过“伪造”不可变的功能：

CREATE FUNCTION f_now_immutable()
  RETURNS timestamp AS
$func$
SELECT now() AT TIME ZONE 'UTC'  -- your time zone here
$func$  LANGUAGE sql IMMUTABLE;

接着：

select count(*) from events
where created_at > f_now_immutable() - interval '1 hour'

不过请务必谨慎使用：虽然now()是STABLE（在事务期间 不会 更改），但它在事务之间 确实会
发生变化，因此请注意不要在准备好的语句（作为参数值除外）或索引或它所处的任何地方使用它可能会咬你。

3） 或者，您可以WHERE在当前查询中添加看似冗余的常量子句，以匹配分区上的约束：

SELECT count(*)
FROM   events
WHERE  created_at > now() - '1 hour'::interval
**AND    created_at >= '2015-04-01 00:00:00'::timestamp
AND    created_at <= '2015-04-30 23:59:59.999999'::timestamp**;

只需确保您自己now() - '1 hour'::interval属于正确的分区，否则您将不会获得任何结果。

撇开：我宁愿在CHECK约束和查询中使用此表达式。更易于处理，并且可以执行以下操作：

       created_at >= '2015-04-01 0:0'::timestamp
AND    created_at <  '2015-05-01 0:0'::timestamp