基于检查约束的分区修剪未按预期工作 [英] Partition pruning based on check constraint not working as expected

问题描述

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

Why is the table "events_201504" included in the query plan below? Based on my query and the check constraint on that table I would expect the query planner to be able to prune it entirely:

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))

推荐答案

您的列 created_at 是不带时区的时间戳。

但是 now（）返回带有时区的时间戳记 。表达式 now（）-'1 hour':: interval 被强制为 timestamp [无时区] ，其中包含两个问题：

But now() returns timestamp with time zone. The expression now() - '1 hour'::interval is being coerced to timestamp [without time zone], which carries two problems:

1。）您没有要求这样做，但表达式不可靠。其结果取决于执行查询的会话的当前时区设置。

1.) You did not ask for this one, but the expression is unreliable. Its result depends on the current time zone setting of the session the query is being executed in. Details here:

• 完全忽略Rails和PostgreSQL中的时区

• Ignoring timezones altogether in Rails and PostgreSQL

为使表达式清晰，您可以使用：

To make the expression clear, you could use:

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

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

LOCALTIMESTAMP  -- explicitly take the local time

我会考虑改为使用 timestamptz 。

都不能解决您的第二个问题：

I would consider working with timestamptz instead.
Neither solves your second problem:

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

2.) Answer to your question. Constraint exclusion does not work. Per documentation:

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

The following caveats apply to constraint exclusion:

约束排除仅在查询的 WHERE 子句包含常量（或外部提供的参数）。例如，无法优化与不可修改函数（例如
CURRENT_TIMESTAMP ）的
，因为计划者无法知道
会在运行时对函数值进行分区。

Constraint exclusion only works when the query's WHERE clause contains constants (or externally supplied parameters). For example, a comparison against a non-immutable function such as CURRENT_TIMESTAMP cannot be optimized, since the planner cannot know which partition the function value might fall into at run time.

粗体字强调。

now（）是 CURRENT_TIMESTAMP 的Postgres实现。正如您在系统目录中看到的那样，它只是 STABLE ，而不是 IMMUTABLE ：

now() is the Postgres implementation of CURRENT_TIMESTAMP. As you can see in the system catalog, it is only STABLE, not IMMUTABLE:

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

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

解决方案

1。）您可以通过在 WHERE 条件（始终为不可变）中提供一个常量来克服此限制：

Solutions

1.) You can overcome the limitation by providing a constant in the WHERE condition (which is always "immutable"):

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

2。）或通过伪造一个不变的函数：

2.) Or by "faking" an immutable function:

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 （在交易期间不会更改），它会更改之间的交易，因此请注意不要在准备好的语句（作为参数值除外）或索引或任何可能会伤到您的东西中使用它。

Be careful how you use this though: while now() is STABLE (does not change for the duration of a transaction), it does change between transactions, so take care not to use this in prepared statements (except as parameter value) or indexes or anything where it might bite you.

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

3.) Or you can add seemingly redundant constant WHERE clauses to your current query that match the constraint on your partition:

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;

只需确保自己< $ c> now（）-'1 hour':: interval 进入正确的分区，或者您没有得到任何结果，显然。

Just make sure yourself that now() - '1 hour'::interval falls into the right partition or you get no results, obviously.

在旁边：我宁愿在 CHECK 约束和查询中使用此表达式。易于处理并执行相同操作：

Aside: I would rather use this expression in CHECK constraints and query. Easier to handle and does the same:

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

这篇关于基于检查约束的分区修剪未按预期工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！