How does now() get evaluated when passed into a function as a parameter

Question

I have a table that is range partitioned on timestamp with timezone field. I was pretty surprised to find that the following where condition caused the planner to query every single 'child' table in the partition:

WHERE reading_time > (now() - '72:00:00'::interval)

As I learned, the planner doesn't know what now() will be at execution time, so it generates the plan to query every child table. That's understandable, but that defeats the purpose of setting up partitions in the first place! If I issue reading_time > '2018-03-31', it'll only do an index scan the tables that have data that meets those conditions.

What happens if I create the following function

CREATE OR REPLACE FUNCTION public.last_72hours(in_time timestamp with time zone)

   Select * from precip where reading_time > (in_time - '72:00:00'::interval)
   --the function will then do work on the returned rows

END;

Then I can call the function with

SELECT last_72hours(now())

When does now() get evaluated? Or, in other words, does the literal time value (e.g., 2018-03-31 1:01:01+5) get passed into the function? If it's the literal value, then Postgres only queries the appropriate child tables, right? But if it's evaluating now() inside the function, then I'm back to the plan that scans the index of every child table. It seems like there's no easy what to see what the planner is doing in the function. Is that correct?

Answer 1

There are several questions here; I'll do my best to answer them all.

PostgreSQL cannot evalutate now() at planning time because there is no way to know when the statement will be executed. Plans can be kept around for an unlimited time.

If you call a function with now() as argument, it will be evaluated at the time of the function call.

If you use a parameter in an SQL statement involving a partitioned table inside a function (so the plan is cached), two things can happen:

1. PostgreSQL decides to switch to a generic plan after the fifth execution of the query. Then no partition pruning can take place.

2. PostgreSQL decides to stick with custom plans so that partition pruning would take place.

One would assume that the second option will usually be chosen, but to find out you can use auto_explain to see the plans actually used.

It might be a good idea to use dynamic SQL so that the query is always replanned with the current parameter values, and partition pruning is certain to be used.