On Fri, 5 Apr 2019 at 07:33, Floris Van Nee <florisvan...@optiver.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
