使用 now() 過濾時，分區 PostgreSQL 表的性能下降

我有一個在時間戳欄位上分區的 PostgreSQL v10 表：

CREATE TABLE front.probes (
   bucket_start timestamp NOT NULL DEFAULT now(),
   -- Extra fields here ...
) PARTITION BY RANGE (bucket_start);

分區本身創建如下：

CREATE TABLE IF NOT EXISTS front.probes_20190611
PARTITION OF front.probes
FOR VALUES FROM ('2019-06-11 00:00:00') TO ('2019-06-12 00:00:00');

如果通過硬編碼我的時間戳來查詢此表，它會按預期執行（171 毫秒）。

注意：為了保持一致性，下面顯示的時間戳是通過執行預先查詢的select now() at time zone 'utc'。

select min(bucket_start), max(bucket_start) 
from front.probes
where bucket_start < '2019-06-11 09:06:47'::timestamp - interval '30 minutes'
and bucket_start >= '2019-06-11 09:06:47'::timestamp - interval '70 minutes';

min                |max                |
-------------------|-------------------|
2019-06-11 08:00:00|2019-06-11 08:35:00|

(0.171s)

相反，如果我now() at time zone 'utc'直接在查詢中使用，它會產生相同的結果，但性能會大幅下降（11.14 秒）：

select min(bucket_start), max(bucket_start)
from front.probes
where bucket_start < now() at time zone 'utc' - interval '30 minutes'
and bucket_start >= now() at time zone 'utc' - interval '70 minutes';

min                |max                |
-------------------|-------------------|
2019-06-11 08:00:00|2019-06-11 08:35:00|

(11.140s)

查詢計劃表明“快速”版本利用了分區約束：

QUERY PLAN                                                                                                                                                                      |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
Finalize Aggregate  (cost=27177.24..27177.25 rows=1 width=16)                                                                                                                   |
 ->  Gather  (cost=27177.02..27177.23 rows=2 width=16)                                                                                                                         |
       Workers Planned: 2                                                                                                                                                      |
       ->  Partial Aggregate  (cost=26177.02..26177.03 rows=1 width=16)                                                                                                        |
             ->  Append  (cost=0.00..24873.83 rows=260639 width=8)                                                                                                             |
                   ->  Parallel Seq Scan on probes_20190611  (cost=0.00..24873.83 rows=260639 width=8)                                                                         |
                         Filter: ((bucket_start < '2019-06-11 08:36:47'::timestamp without time zone) AND (bucket_start >= '2019-06-11 07:56:47'::timestamp without time zone))|

雖然now()版本沒有：

QUERY PLAN                                                                                                                                                                |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
Aggregate  (cost=2119587.51..2119587.52 rows=1 width=16)                                                                                                                  |
 ->  Append  (cost=0.00..2116094.94 rows=698514 width=8)                                                                                                                 |
       ->  Seq Scan on probes_20190605  (cost=0.00..713298.11 rows=1 width=8)                                                                                            |
             Filter: ((bucket_start < (timezone('utc'::text, now()) - '00:30:00'::interval)) AND (bucket_start >= (timezone('utc'::text, now()) - '01:10:00'::interval)))|
       (... full partition list, omitted for clarity ...)
       ->  Seq Scan on probes_20190617  (cost=0.00..21.40 rows=2 width=8)                                                                                                |
             Filter: ((bucket_start < (timezone('utc'::text, now()) - '00:30:00'::interval)) AND (bucket_start >= (timezone('utc'::text, now()) - '01:10:00'::interval)))|

文件顯示返回目前事務開始now()時的日期和時間，因此我希望在製定查詢計劃之前解決它，以便我的查詢可以利用分區約束，但似乎並非如此.

有沒有辦法解決這個問題？如果可能，我們希望避免在“相對”時間查詢中設置手動時間戳。

PostgreSQL 查詢不一定在執行時在同一個事務中執行，例如準備好的語句。

因此，now()無法在計劃時進行評估。

PostgreSQL v11 可以在執行時修剪分區，因此您的查詢應該與 PostgreSQL v11 一起按預期工作。不過，您對 v10 不走運。

引用自：https://dba.stackexchange.com/questions/240276