你的专栏created_at
属于类型timestamp without time zone
.
But now()
回报timestamp with time zone
。表达方式now() - '1 hour'::interval
正在被胁迫timestamp [without time zone]
,其中携带两个问题:
1.)你没有要求这个,但表达方式不可靠。其结果取决于正在执行查询的会话的当前时区设置。详细信息如下:
- 在 Rails 和 PostgreSQL 中完全忽略时区 https://stackoverflow.com/questions/9571392/ignoring-timezones-altogether-in-rails-and-postgresql/9576170#9576170
为了使表达式清晰,您可以使用:
now() AT TIME ZONE 'Europe/London' -- your time zone here
Or just (请阅读此处的手册) https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT:
LOCALTIMESTAMP -- explicitly take the local time
我会考虑与timestamptz
反而。
两者都不能解决你的第二个问题:
2.) 回答你的问题。约束排除不起作用。手册: https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-CAVEATS
以下注意事项适用于约束排除:
- [...]
- 约束排除仅在查询的情况下才起作用
WHERE
子句包含常量(或外部提供的参数)。例如,一个
与一个比较非不可变函数,例如CURRENT_TIMESTAMP
无法优化,因为规划者无法知道
函数值在运行时可能属于哪个分区。
大胆强调我的。
now()
是 Postgres 的实现CURRENT_TIMESTAMP
。在系统目录中可以看到,它只是STABLE
, not 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; -- from your example
2.)或者通过“伪造”一个不可变的函数:
CREATE FUNCTION f_now_immutable()
RETURNS timestamp
LANGUAGE sql IMMUTABLE AS
$func$
SELECT now() AT TIME ZONE 'UTC'; -- your time zone here
$func$;
进而:
SELECT count(*) FROM events
WHERE created_at > f_now_immutable() - interval '1 hour';
但要小心如何使用它: whilenow()
is STABLE
(在交易期间不会改变),它does事务之间会发生变化,因此请注意不要在准备好的语句(作为参数值除外)或索引或任何可能会影响您的地方使用它。
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'::timestamp
AND created_at < '2015-05-01'::timestamp