Faster inserts with mostly-monotonically increasing values

Поиск
Список
Период
Сортировка
От Pavan Deolasee
Тема Faster inserts with mostly-monotonically increasing values
Дата
Msg-id CABOikdM9DrupjyKZZFM5k8-0RCDs1wk6JzEkg7UgSW6QzOwMZw@mail.gmail.com
обсуждение исходный текст
Ответы Re: Faster inserts with mostly-monotonically increasing values
Re: Faster inserts with mostly-monotonically increasing values
Список pgsql-hackers
Дерево обсуждения
Hello All,

On a per-session basis, we cache the last heap block used for inserts and try to use the same block for subsequent inserts. We don't do that for indexes because the target block in the index is determined by the overall structure of the index and the index key being inserted and hence caching is usually not very useful. But for certain typical, yet not-so-uncommon cases we can make similar optimisations. For example, consider a btree index on a "timestamp" column or on a "serial" column. In such cases, each new index entry typically goes beyond the rightmost entry in the index. If we cache the rightmost block and check that first, we can avoid the cost of descending down and looking up the index

Here is a patch that implements the idea. If the last insert happens to be in the rightmost block of an index, then we cache the block and check that first for the next insert. For the cached block to be usable for the insert, it should still be the rightmost, the to-be-inserted key should go into the cached block and there is enough free space in the block. If any of these conditions do not meet, we fall back to the regular code path, i.e. descent down the index from the top.

I benchmarked the patch by creating a simple table with just one bigint column and a btree index on it. Here are the results:

Monotonically Increasing 10M Inserts (time in ms)
======================================
Run Patched  Master
1 17656.222 25737.593
2 18765.002 26843.314
3 20629.567 27058.358
4 21174.998 26680.003
5 21118.865 26660.557

Avg 19868.9308 26595.965 (33.86% improvement)


Monotonically Increasing 100M Inserts (time in ms)
======================================
Run Patched  Master
1 159861.58 248885.763
2 160138.432 256438.625
3 160426.135 250379.589
4 163218.405 249474.695
5 161125.523 247805.675

Avg 160954.015 250596.8694 (55.69% improvement)


So as the size of the index increases, the benefits of the patch also tend to increase. This is most likely because as the index becomes taller and taller, the costs associated with index descent becomes higher.

Patch credit: this work is based on Simon Riggs's original ideas and research.

Thanks,
Pavan

--
 Pavan Deolasee                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services
Вложения

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