Re: Speeding up INSERTs and UPDATEs to partitioned tables

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.

Do you mind sharing how many partitions you have and how many columns
the partitioned table has?

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services