Re: Faster inserts with mostly-monotonically increasing values

Поиск
Список
Период
Сортировка
От Pavan Deolasee
Тема Re: Faster inserts with mostly-monotonically increasing values
Дата
Msg-id CABOikdOTj31ma+ju4oXQFHe9zp1e6h5L5ApKVzwE8pXV696SXQ@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Faster inserts with mostly-monotonically increasing values  (Pavan Deolasee <pavan.deolasee@gmail.com>)
Ответы Re: Faster inserts with mostly-monotonically increasing values
Список pgsql-hackers
Дерево обсуждения


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. 

Thanks,
Pavan

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

В списке pgsql-hackers по дате отправления: