Re: Speeding up INSERTs and UPDATEs to partitioned tables

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).

Thanks
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:
> >
> > On 23 October 2018 at 11:55, Krzysztof Nienartowicz
> > <krzysztof(dot)nienartowicz(at)gmail(dot)com> wrote:
> > > In the end we hacked the code to re-enable triggers on partitioned
> > > tables and switch off native insert code on partitioned tables. Quite
> > > hackish and would be nice to have it fixed in a more natural manner.
> > > Yes, it looked like locking but not only -
> > > ExecSetupPartitionTupleRouting: ExecOpenIndices/find_all_inheritors
> > > looked like dominant and also convert_tuples_by_name but not sure if
> > > the last one was not an artifact of perf sampling.
> >
> > The ExecOpenIndices was likely fixed in edd44738bc8 (PG11).
> > find_all_inheritors does obtain the partition locks during the call,
> > so the slowness there most likely down to the locking rather than the
> > scanning of pg_inherits.
> >
> > 42f70cd9c3dbf improved the situation for convert_tuples_by_name (PG12).
> >
> > > Will check the patch 0001, thanks.
> >
> > 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)
>
> >
> > Do you mind sharing how many partitions you have and how many columns
> > the partitioned table has?
>
> We have 2 level partitioning: 10 (possibly changing, up to say 20-30)
> range partitions at first level and 20 range at the second level. We
> have potentially hundreds processes inserting at the same time.
>
> >
> >
> > --
> > David Rowley http://www.2ndQuadrant.com/
> > PostgreSQL Development, 24x7 Support, Training & Services