Re: Speeding up INSERTs and UPDATEs to partitioned tables

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.
Will check the patch 0001, thanks.

On Tue, Oct 23, 2018 at 12:36 AM David Rowley
<david(dot)rowley(at)2ndquadrant(dot)com> wrote:
>
> On 15 October 2018 at 23:04, Krzysztof Nienartowicz
> <krzysztof(dot)nienartowicz(at)gmail(dot)com> wrote:
> > We see quite prohibitive 5-6x slowdown with native partitioning on in
> > comparison to trigger based in PG9.5.
> > This is clearly visible with highly parallel inserts (Can share
> > flamediagrams comparing the two).
>
> Does the 0001 patch here fix the problem? I imagined that it would be
> the locking of all partitions that would have killed the performance.
>
> > This basically excludes native partitioning from being used by us. Do you
> > think your changes could be backported to PG10? - we checked and this would
> > need quite a number of changes but given the weight of this change maybe it
> > could be considered?
>
> It's very unlikely to happen, especially so with the 0002 patch, which
> I've so far just attached as a demonstration of where the performance
> could end up.
>
> --
> David Rowley http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services