Re: non-bulk inserts and tuple routing

On 2017/12/19 19:06, Amit Langote wrote:
> Hi.
>
> I have a patch that rearranges the code around partition tuple-routing,
> such that allocation of per-partition objects (ResultRelInfo,
> TupleConversionMap, etc.) is delayed until a given partition is actually
> inserted into (i.e., a tuple is routed to it). I can see good win for
> non-bulk inserts with the patch and the patch is implemented such that it
> doesn't affect the bulk-insert case much.
>
> Performance numbers:
>
> * Uses following hash-partitioned table:
>
> create table t1 (a int, b int) partition by hash (a);
> create table t1_x partition of t1 for values with (modulus M, remainder R)
> ...
>
>
> * Non-bulk insert uses the following code (insert 100,000 rows one-by-one):
>
> do $$
> begin
> for i in 1..100000 loop
> insert into t1 values (i, i+1);
> end loop;
> end; $$;
>
> * Times in milliseconds:
>
> #parts HEAD Patched
>
> 8 6216.300 4977.670
> 16 9061.388 6360.093
> 32 14081.656 8752.405
> 64 24887.110 13919.384
> 128 45926.251 24582.411
> 256 88088.084 45490.894
>
> As you can see the performance can be as much as 2x faster with the patch,
> although time taken still increases as the number of partitions increases,
> because we still lock *all* partitions at the beginning.
>
> * Bulk-inserting 100,000 rows using COPY:
>
> copy t1 from '/tmp/t1.csv' csv;
>
> * Times in milliseconds:
>
> #parts HEAD Patched
>
> 8 458.301 450.875
> 16 409.271 510.723
> 32 500.960 612.003
> 64 430.687 795.046
> 128 449.314 565.786
> 256 493.171 490.187
>
> Not much harm here, although numbers are a bit noisy.
>
> Patch is divided into 4, first 3 of which are refactoring patches.
>
> I know this patch will conflict severely with [1] and [2], so it's fine if
> we consider applying these later. Will add this to next CF.

I rebased the patches, since they started conflicting with a recently
committed patch [1].

Thanks,
Amit

[1]
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=cc6337d2fed5