我的应用向白水船民报告降雨和溪流信息。Postgres 是我的数据存储，用于每隔 15 分钟提供一次仪表读数。随着时间的推移，这些表变得非常大，Postgres 10 中范围分区的可用性激发了我离开我的共享托管服务并在 Linode 从头开始构建服务器。在我将读数划分为 2 周块后，我对这些大表的查询变得更快。几个月后，我检查了查询计划，并非常惊讶地发现在查询中使用now()导致PG扫描了分区表上的所有索引。什么鬼?!?!分区数据的重点不是为了避免这种情况吗？
这是我的设置：我的分区表
CREATE TABLE public.precip
(
gauge_id smallint,
inches numeric(8, 2),
reading_time timestamp with time zone
) PARTITION BY RANGE (reading_time)
我每两周创建一次分区，所以到目前为止我有大约 50 个分区表。我的分区之一：
CREATE TABLE public.precip_y2017w48 PARTITION OF public.precip
FOR VALUES FROM ('2017-12-03 00:00:00-05') TO ('2017-12-17 00:00:00-05');
然后，在gauge_id和reading_time上为每个分区编制索引
我有很多疑问，例如
WHERE gauge_id = xxx
AND precip.reading_time > (now() - '01:00:00'::interval)
AND precip.reading_time < now()
正如我提到的，postgres 扫描reading_time上每个"子"表的所有索引，而不是只查询查询范围内具有时间戳的子表。如果我输入文字值(例如，precip.reading_time> '2018-03-01 01：23：00')而不是 now()，它只会扫描相应子表的索引。我已经做了一些阅读，我知道now()是易失性的，并且计划器不会知道执行查询时的值是多少。我还读到查询计划很昂贵，因此 postgres 会缓存计划。我可以理解为什么PG被编程为这样做。但是，我读到的一个反驳论点是，重新计划的查询可能比最终忽略分区的查询便宜得多。我同意 - 在我的情况下可能就是这种情况。
作为解决方法，我创建了此函数：
CREATE OR REPLACE FUNCTION public.hours_ago2(i integer)
RETURNS timestamp with time zone
LANGUAGE 'plpgsql'
COST 100
IMMUTABLE
ROWS 0
AS $BODY$
DECLARE X timestamp with time zone;
BEGIN
X:= now() + cast(i || ' hours' as interval);
RETURN X;
END;
$BODY$;
请注意不可变语句。 现在，当问题查询时，例如
select * from stream
where gauge_id = 2142 and reading_time > hours_ago2(-3)
and reading_time < hours_ago2(0)
PG 仅搜索存储该时间范围数据的分区表。这是我最初设置分区时的目标。布亚。但这安全吗？查询规划器是否会缓存 hours_ago2(-3) 的结果，并在数小时内一遍又一遍地使用它？如果缓存几分钟也没关系。同样，我的应用程序报告雨水和溪流信息;它不处理金融交易或任何其他"关键"类型的数据处理。我已经测试了像 select hours_ago2(-3) 这样的简单语句，它每次都返回新值。所以它看起来很安全。但真的是这样吗？
这是不安全的，因为在计划时，您不知道该语句是否会在同一事务中执行。
如果处于缓存查询计划的情况，这将返回错误的结果。查询计划是为命名预准备语句和 PL/pgSQL 函数中的语句缓存的，因此在数据库会话期间，您最终可能会得到过期的值。
例如：
CREATE TABLE times(id integer PRIMARY KEY, d timestamptz NOT NULL);
PREPARE x AS SELECT * FROM times WHERE d > hours_ago2(1);
函数在计划时进行评估，结果是执行计划中的一个常量(对于不可变函数，这很好)。
EXPLAIN (COSTS off) EXECUTE x;
QUERY PLAN
---------------------------------------------------------------------------
Seq Scan on times
Filter: (d > '2018-03-12 14:25:17.380057+01'::timestamp with time zone)
(2 rows)
SELECT pg_sleep(100);
EXPLAIN (COSTS off) EXECUTE x;
QUERY PLAN
---------------------------------------------------------------------------
Seq Scan on times
Filter: (d > '2018-03-12 14:25:17.380057+01'::timestamp with time zone)
(2 rows)
第二个查询绝对不会返回您想要的结果。
我认为您应该首先评估now()(或者更好的客户端等效函数)，执行日期算术并将结果作为参数提供给查询。在 PL/pgSQL 函数中，使用动态 SQL。
将查询更改为使用'now'::timestamptz而不是now()。此外，timestamptz的区间数学不是不可变的。
将查询更改为以下内容：
WHERE gauge_id = xxx
AND precip.reading_time > ((('now'::timestamptz AT TIME ZONE 'UTC') - '01:00:00'::interval) AT TIME ZONE 'UTC')
AND precip.reading_time < 'now'::timestamptz