Slow performance on partitioned PostgreSQL table when using now() to filter

I have a PostgreSQL v10 table partitioned on a timestamp field:

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

The partitions themselves are created as below:

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

If query this table by hard-coding my timestamp, it performs as expected (171ms).

Note: For consistency, the timestamp displayed below was pre-queried by running 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 '70 minutes';

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

(0.171s)

In contrast, if I use now() at time zone 'utc' directly in the query, it yeilds the same results, but performance degrades spectacularly (11.14s):

select min(bucket_start), max(bucket_start)
from front.probes
where 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)

The query plans show that the "fast" version takes advantage of partition constraints:

```
QUERY PLAN |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
Finalize Aggregate (cost=27177.24..27177.25 rows=1 width=16) |
-> Gather (cost=27177.02..27177.23 row

A PostgreSQL query is not necessarily executed in the same transaction as it is executed, think for example of prepared statements.

Consequently, now() cannot be evaluated at planning time.

PostgreSQL v11 can prune partitions at execution time, so your query should work as intended with PostgreSQL v11. You are out of luck with v10 though.

StackExchange Database Administrators Q#240276, answer score: 3