Re: speeding up planning with partitions

On 2019/04/05 6:59, David Rowley wrote:
> 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.

Yeah, the time for generating plan for a query that *can* use pruning but
not during planning is still very much dependent on the number of
partitions, because access plans must be created for all partitions, even
if only one of those plans will actually be used and the rest pruned away
during execution.

> If you're never using prepared statements,

Or if using prepared statements is an option, the huge planning cost
mentioned above need not be paid repeatedly. Although, we still have ways
to go in terms of scaling generic plan execution to larger partition
counts, solution(s) for which have been proposed by David but haven't made
it into master yet.

Thanks,
Amit