Re: Faster inserts with mostly-monotonically increasing values

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.

Thanks,
Pavan

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