Horton

Re: speeding up planning with partitions

Amit Langote <langote_amit_f8@lab.ntt.co.jp>

View thread View on postgr.es
From: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
To: David Rowley <david.rowley@2ndquadrant.com>, Floris Van Nee <florisvannee@optiver.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Imai Yoshikazu <yoshikazu_i443@live.jp>, "jesper.pedersen@redhat.com" <jesper.pedersen@redhat.com>, "Imai, Yoshikazu" <imai.yoshikazu@jp.fujitsu.com>, Amit Langote <amitlangote09@gmail.com>, Alvaro Herrera <alvherre@2ndquadrant.com>, Robert Haas <robertmhaas@gmail.com>, Justin Pryzby <pryzby@telsasoft.com>, Pg Hackers <pgsql-hackers@postgresql.org>
Date: 2019-04-05T03:09:16Z
Lists: pgsql-hackers 
On 2019/04/05 6:59, David Rowley wrote:
> On Fri, 5 Apr 2019 at 07:33, Floris Van Nee <florisvannee@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.

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

Commits

  1. Clean up handling of constraint_exclusion and enable_partition_pruning.

  2. Add test case exercising formerly-unreached code in inheritance_planner.

  3. Speed up planning when partitions can be pruned at plan time.

  4. Avoid crash in partitionwise join planning under GEQO.

  5. Avoid passing query tlist around separately from root->processed_tlist.

  6. Build "other rels" of appendrel baserels in a separate step.

  7. Get rid of duplicate child RTE for a partitioned table.

  8. Rearrange make_partitionedrel_pruneinfo to avoid work when we can't prune.

  9. Don't copy PartitionBoundInfo in set_relation_partition_info.

  10. Move building of child base quals out into a new function

  11. Reorganize planner code moved in b60c39759908

  12. Move inheritance expansion code into its own file

  13. Fix inherited UPDATE/DELETE with UNION ALL subqueries.

  14. Rearrange planner to save the whole PlannerInfo (subroot) for a subquery.