Re: Speeding up INSERTs and UPDATEs to partitioned tables

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