Re: Faster inserts with mostly-monotonically increasing values

On Fri, Mar 9, 2018 at 2:54 PM, Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com> wrote:
>
>
> On Tue, Mar 6, 2018 at 10:10 AM, Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com>
> wrote:
>>
>>
>>
>> On Tue, Mar 6, 2018 at 7:29 AM, Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
>>>
>>> On Mon, Mar 5, 2018 at 5:48 PM, Claudio Freire <klaussfreire(at)gmail(dot)com>
>>> wrote:
>>>
>>> > I believe PKs are a prime candidate for this optimization, and
>>> > expecting it to apply only when no concurrency is involved is severely
>>> > dumbing down the optimization.
>>>
>>> Pavan justified the patch using a benchmark that only involved a
>>> single client -- hardly typical for a patch that changes the B-Tree
>>> code. If the benefits with many clients can be shown to matter, that
>>> will make this much more interesting to me.
>>
>>
>> Ok. I will repeat those tests with more number of clients and report back.
>>
>
> So I repeated the tests with 1,2,4 and 8 clients, each running the following
> statement and a total of 1024 transactions. So roughly 100M rows are
> inserted.
>
> INSERT INTO testtab(b) SELECT generate_series(1,100000);
>
> The table definition is:
> postgres=# \d+ testtab
> Table "public.testtab"
> Column | Type | Collation | Nullable | Default
> | Storage | Stats target | Description
> --------+--------+-----------+----------+------------------------------------+---------+--------------+-------------
> a | bigint | | not null | nextval('testtab_a_seq'::regclass)
> | plain | |
> b | bigint | | |
> | plain | |
> Indexes:
> "testtab_a_key" UNIQUE CONSTRAINT, btree (a)
>
>
> After taking average of 3-runs:
>
> +---------+--------------------------------+-------------------------------+
> | clients | Patched - time in sec | Master - time in sec |
> +---------+--------------------------------+-------------------------------+
> | 1 | 311.8643602 | 411.832757 |
> +---------+--------------------------------+-------------------------------+
> | 2 | 252.5433 | 300.7875613 |
> +---------+--------------------------------+-------------------------------+
> | 4 | 337.0414279 | 350.9636766 |
> +---------+--------------------------------+-------------------------------+
> | 8 | 444.2035582 | 477.1903417 |
> +---------+--------------------------------+-------------------------------+
>
> So yes, the benefits of the patch go down with higher number of clients, but
> it does not entirely vanish.

What if you implement my suggestion?

That should improve the multi-client case considerably.