Re: speeding up planning with partitions

On Fri, 5 Apr 2019 at 23:07, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>
> 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.

Robert and I had a go at discussing this in [1]. Some ideas were
thrown around in the nature of contorting the Append/MergeAppend's
total_cost in a similar way to how clauselist_selectivity does its
estimates for unknown values. Perhaps it is possible to actually
multiplying the total_cost by the clauselist_selectivity for the
run-time pruning quals. That's pretty crude and highly unusual, but
it's probably going to give something more sane than what's there
today. The run-time prune quals would likely need to be determined
earlier than createplan.c for that to work though. IIRC the reason it
was done there is, because at the time, there wasn't a need to do it
per path.

I don't really have any better ideas right now, so if someone does
then maybe we should take it up on a new thread. It would be good to
leave this thread alone for unrelated things. It's long enough
already.

[1] https://www.postgresql.org/message-id/CA%2BTgmobhXJGMuHxKjbaKcEJXacxVZHG4%3DhEGFfPF_FrGt37T_Q%40mail.gmail.com

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