Re: Faster inserts with mostly-monotonically increasing values - Mailing list pgsql-hackers

On Fri, Mar 9, 2018 at 2:54 PM, Pavan Deolasee <pavan.deolasee@gmail.com> wrote:
>
>
> On Tue, Mar 6, 2018 at 10:10 AM, Pavan Deolasee <pavan.deolasee@gmail.com>
> wrote:
>>
>>
>>
>> On Tue, Mar 6, 2018 at 7:29 AM, Peter Geoghegan <pg@bowt.ie> wrote:
>>>
>>> On Mon, Mar 5, 2018 at 5:48 PM, Claudio Freire <klaussfreire@gmail.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.