Re: speeding up planning with partitions

On Fri, 5 Apr 2019 at 07:33, Floris Van Nee <florisvannee(at)optiver(dot)com> wrote:
> I had a question about the performance of pruning of functions like now() and current_date. I know these are handled differently, as they cannot be excluded during the first phases of planning. However, curerntly, this new patch makes the performance difference between the static timestamp variant and now() very obvious (even more than before). Consider
> select * from partitioned_table where ts >= now()
> or
> select * from partitioned_table where ts >= '2019-04-04'
>
> The second plans in less than a millisecond, whereas the first takes +- 180ms for a table with 1000 partitions. Both end up with the same plan.

The patch here only aims to improve the performance of queries to
partitioned tables when partitions can be pruned during planning. The
now() version of the query is unable to do that since we don't know
what that value will be during the execution of the query. In that
version, you're most likely seeing "Subplans Removed: <n>". This means
run-time pruning did some pruning and the planner generated subplans
for what you see plus <n> others. Since planning for all partitions is
still slow, you're getting a larger performance difference than
before, but only due to the fact that the other plan is now faster to
generate.

If you're never using prepared statements, i.e, always planning right
before execution, then you might want to consider using "where ts >=
'today'::timestamp". This will evaluate to the current date during
parse and make the value available to the planner. You'll need to be
pretty careful with that though, as if you do prepare queries or
change to do that in the future then the bugs in your application
could be very subtle and only do the wrong thing just after midnight
on some day when the current time progresses over your partition
boundary.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services