Re: Speeding up INSERTs and UPDATEs to partitioned tables

Thanks for both clarifications!
I skimmed through the commits related to Inserts with partitioning
since 10 and indeed - while not impossible it seems like quite some
work to merge them into PG 10 codebase.
We might consider preparing the patch in-house as otherwise PG 10
based partitioning is a major regression and we'd have to go back to
inheritance based one - which seems the best option for now.
Regards,
Krzysztof

On Tue, Oct 30, 2018 at 1:54 AM Amit Langote
<Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>
> On 2018/10/30 8:41, Krzysztof Nienartowicz wrote:
> > On Thu, Oct 25, 2018 at 5:58 PM Krzysztof Nienartowicz
> > <krzysztof(dot)nienartowicz(at)gmail(dot)com> wrote:
> >> On Tue, Oct 23, 2018 at 4:02 AM David Rowley
> >> <david(dot)rowley(at)2ndquadrant(dot)com> wrote:
> >>>
> >>> I more meant that it might be 0002 that fixes the issue for you. I
> >>> just wanted to check if you'd tried 0001 and found that the problem
> >>> was fixed with that alone.
> >>
> >> Will it apply on PG10? (In fact the code base is PG XL10 but
> >> src/backend/executor/nodeModifyTable.c is pure PG)
> >
> > To complement the info: number of columns varies from 20 to 100 but
> > some of the columns are composite types or arrays of composite types.
> >
> > The flamegraph after applying changes from patch 0002 can be seen
> > here: https://gaiaowncloud.isdc.unige.ch/index.php/s/W3DLecAWAfkesiP
> > shows most of the time is spent in the
> >
> > convert_tuples_by_name (PG10 version).
>
> As David mentioned, the patches on this thread are meant to be applied
> against latest PG 12 HEAD. The insert tuple routing code has undergone
> quite a bit of refactoring in PG 11, which itself should have gotten rid
> of at least some of the hot-spots that are seen in the flame graph you shared.
>
> What happens in PG 10 (as seen in the flame graph) is that
> ExecSetupPartitionTupleRouting initializes information for *all*
> partitions, which happens even before the 1st tuple processed. So if
> there are many partitions and with many columns, a lot of processing
> happens in ExecSetupPartitionTupleRouting. PG 11 changes it such that the
> partition info is only initialized after the 1st tuple is processed and
> only of the partition that's targeted, but some overheads still remain in
> that code. The patches on this thread are meant to address those overheads.
>
> Unfortunately, I don't think the community will agree to back-porting the
> changes in PG 11 and the patches being discussed here to PG 10.
>
> Thanks,
> Amit
>