Re: Speeding up INSERTs and UPDATEs to partitioned tables

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