Re: Performance regression with PostgreSQL 11 and partitioning

Le 30/05/2018 à 03:57, David Rowley a écrit :
> On 26 May 2018 at 09:17, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> I'm inclined to think that we should flush find_appinfos_by_relids
>> altogether, along with these inefficient intermediate arrays, and instead
>> have the relevant places in adjust_appendrel_attrs call some function
>> defined as "gimme the AppendRelInfo for child rel A and parent rel B,
>> working directly from the PlannerInfo root data". That could use a hash
>> lookup when dealing with more than some-small-number of AppendRelInfos,
>> comparable to what we do with join_rel_list/join_rel_hash.
>
> For partitioned tables, a child just has a single parent, so to speed
> up find_appinfos_by_relids we'd simply just need a faster way to find
> the AppendRelInfo by child relid.
>
> I've been working on a patch series which I plan to submit to PG12
> aimed to speed up partitioning. Ideally, I'd have liked to just tag
> the AppendRelInfo onto the child RelOptInfo, but that falls down
> during inheritance planning.
>
> In the end I just made an array to store AppendRelInfo's by their
> child_relid which is created and populated during
> setup_simple_rel_arrays.
>
> Probably the patch could go a bit further and skip array allocation
> when the append_rel_list is empty, but I've been busy working on
> another bunch of stuff to improve planning time. I'd planned to give
> this another look before submitting in the PG12 cycle, so state ==
> WIP.
>
> A quick test of the attached on Thomas' 4k part test, I get:
>
> Unpatched
> tps = 5.957508 (excluding connections establishing)
>
> Patched:
> tps = 15.368806 (excluding connections establishing)
>
> Using that, if Thomas sees the same speedup then that puts PG11 at
> 55.4ms vs his measured 66ms in PG10.
>
> The attached should apply with some fuzz to master.

Thanks for this quick patch David, unfortunately I didn't have time to
play around with it until now.

On the 4k part test, the planning time falls down to 55 ms instead of
the 4 seconds from the current head.
I also have better results with pgbench. Your patch solves this planning
time issue.

Thanks David !