Optimize now() in WHERE clauses

[megadawn]

Is it just me or...
SELECT public.create_range_partitions('dev.meter_read_delta_pm1', 'time_key', '2016-11-14'::date, '1 day'::interval, 10);

explain analyze
select * from meter_read_delta_pm1
where time_key >= timestamp '2016-11-14 ' and time_key < timestamp '2016-11-17' ;

-> Seq Scan on meter_read_delta_pm1_1 (cost=0.00..13.80 rows=380 width=182) (actual time=0.005..0.005 rows=0 loops=1)
-> Seq Scan on meter_read_delta_pm1_2 (cost=0.00..13.80 rows=380 width=182) (actual time=0.000..0.000 rows=0 loops=1)
-> Seq Scan on meter_read_delta_pm1_3 (cost=0.00..13.80 rows=380 width=182) (actual time=0.001..0.001 rows=0 loops=1)

all good. Now let's put now() function:
explain analyze
select * from meter_read_delta_pm1
where time_key >= now() - interval '2' day and time_key < now()

-> Seq Scan on meter_read_delta_pm1_1 (cost=0.00..18.55 rows=2 width=182) (actual time=0.005..0.005 rows=0 loops=1)"
" Filter: ((time_key <= now()) AND (time_key >= (now() - '2 days'::interval day)))"
" -> Seq Scan on meter_read_delta_pm1_2 (cost=0.00..18.55 rows=2 width=182) (actual time=0.001..0.001 rows=0 loops=1)"
" Filter: ((time_key <= now()) AND (time_key >= (now() - '2 days'::interval day)))"
" -> Seq Scan on meter_read_delta_pm1_3 (cost=0.00..18.55 rows=2 width=182) (actual time=0.001..0.001 rows=0 loops=1)"
" Filter: ((time_key <= now()) AND (time_key >= (now() - '2 days'::interval day)))"
" -> Seq Scan on meter_read_delta_pm1_4 (cost=0.00..18.55 rows=2 width=182) (actual time=0.001..0.001 rows=0 loops=1)"
" Filter: ((time_key <= now()) AND (time_key >= (now() - '2 days'::interval day)))"
" -> Seq Scan on meter_read_delta_pm1_5 (cost=0.00..18.55 rows=2 width=182) (actual time=0.001..0.001 rows=0 loops=1)"
" Filter: ((time_key <= now()) AND (time_key >= (now() - '2 days'::interval day)))"
" -> Seq Scan on meter_read_delta_pm1_6 (cost=0.00..18.55 rows=2 width=182) (actual time=0.002..0.002 rows=0 loops=1)"
" Filter: ((time_key <= now()) AND (time_key >= (now() - '2 days'::interval day)))"
" -> Seq Scan on meter_read_delta_pm1_7 (cost=0.00..18.55 rows=2 width=182) (actual time=0.001..0.001 rows=0 loops=1)"
" Filter: ((time_key <= now()) AND (time_key >= (now() - '2 days'::interval day)))"
" -> Seq Scan on meter_read_delta_pm1_8 (cost=0.00..18.55 rows=2 width=182) (actual time=0.001..0.001 rows=0 loops=1)"
" Filter: ((time_key <= now()) AND (time_key >= (now() - '2 days'::interval day)))"
" -> Seq Scan on meter_read_delta_pm1_9 (cost=0.00..18.55 rows=2 width=182) (actual time=0.001..0.001 rows=0 loops=1)"
" Filter: ((time_key <= now()) AND (time_key >= (now() - '2 days'::interval day)))"
" -> Seq Scan on meter_read_delta_pm1_10 (cost=0.00..18.55 rows=2 width=182) (actual time=0.001..0.001 rows=0 loops=1)"
" Filter: ((time_key <= now()) AND (time_key >= (now() - '2 days'::interval day)))"

[megadawn]

so we can't use volatile functions in WHERE clause?

[funbringer]

so we can't use volatile functions in WHERE clause?

I'm sorry, it's not volatile (it is stable), but it still cannot be optimized at the moment. As for the volatile functions, it's simply impossible.

[funbringer]

so we can't use volatile functions in WHERE clause?

I don't really get this statement. Does this mean that you can't use them? Ofc you can, it just won't be that efficient.

[megadawn]

ok thanks

[funbringer]

pg_pathman partitions don't work with any volatile functions

It's not about pg_pathman, it's in the nature of volatile functions. If it's volatile, it means that it can return literally any value, and thus it can't be thrown away and be replaced with some value.

When you optimize a clause, you must make sure that you don't bring in any unwanted side effects, such as "not really random" random() function etc.

Now, imagine we have a stable function. Let me cite PostgreSQL documentation:

A STABLE function cannot modify the database and is guaranteed to return the same results given the same arguments for all rows within a single statement.

What if we tried to replace it with some value? That would break something like prepared statements. You wouldn't expect it to use a several-hours-late date when you're about to execute it, would you?