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: Floris Van Nee <florisvannee@Optiver.com>, David Rowley <david.rowley@2ndquadrant.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-05T10:07:17Z
Lists: pgsql-hackers 
Hi,

On 2019/04/05 18:13, Floris Van Nee wrote:
> One unrelated thing I noticed (but I'm not sure if it's worth a separate email thread) is that the changed default of jit=on in v12 doesn't work very well with a large number of partitions at run-time, for which a large number get excluded at run-time. A query that has an estimated cost above jit_optimize_above_cost takes about 30 seconds to run (for a table with 1000 partitions), because JIT is optimizing the full plan. Without JIT it's barely 20ms (+400ms planning). I can give more details in a separate thread if it's deemed interesting.
> 
> Planning Time: 411.321 ms
> JIT:
>   Functions: 5005
>   Options: Inlining false, Optimization true, Expressions true, Deforming true
>   Timing: Generation 721.472 ms, Inlining 0.000 ms, Optimization 16312.195 ms, Emission 12533.611 ms, Total 29567.278 ms

I've noticed a similar problem but in the context of interaction with
parallel query mechanism.  The planner, seeing that all partitions will be
scanned (after failing to prune with clauses containing CURRENT_TIMESTAMP
etc.), prepares a parallel plan (containing Parallel Append in this case).
 As you can imagine, parallel query initialization (Gather+workers) will
take large amount of time relative to the time it will take to scan the
partitions that remain after pruning (often just one).

The problem in this case is that the planner is oblivious to the
possibility of partition pruning occurring during execution, which may be
common to both parallel query and JIT.  If it wasn't oblivious, it
would've set the cost of pruning-capable Append such that parallel query
and/or JIT won't be invoked.  We are going to have to fix that sooner or
later.

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.