Hi, hackers.
I was faced with the following bad performance use case with Postgres:
there is a huge append-only table with serial key (ID)
which is permanently appended using multithreaded pgloader.
Ideally (if there is no parallel load), this table should be clustered
by "ID" key: records are stored in the heap in ID increasing order.
So index scan using ID index should be very efficient, because records
with subsequent IDs are expected to belong to the same heap page.
In practice I see a lot of random reads and bad performance (read speed
is about 3Mb/sec, instead of 300Mb/sec for sequential read).
This is the typical page layout:
select ctid,"ID" from T limit 100;
ctid | ID
--------+-----------
(0,1) | 230827730
(0,2) | 230830833
(0,3) | 230840164
(0,4) | 230853499
(0,5) | 230862073
(0,6) | 230864443
(0,7) | 230870332
(0,8) | 230872992
(0,9) | 230873052
(0,10) | 230875245
(0,11) | 230877107
(0,12) | 230878144
(0,13) | 230878612
(0,14) | 230885569
(0,15) | 230885726
(0,16) | 230886305
(0,17) | 230886606
(0,18) | 230892813
(0,19) | 230895012
(0,20) | 230895041
(0,21) | 230895175
(0,22) | 230896732
(0,23) | 230897719
(0,24) | 230897841
(0,25) | 230898386
(0,26) | 230899992
(0,27) | 230900646
(0,28) | 230901053
(0,29) | 230901717
(0,30) | 230904115
(0,31) | 230908189
And this is the result of index scan:
select ctid,"ID" from T where "ID" > 1669762074 limit 100;
ctid | ID
-----------+------------
(1890,22) | 1669762075
(2035,20) | 1669762076
(2104,19) | 1669762077
(2518,27) | 1669762078
(2530,15) | 1669762079
(2552,32) | 1669762080
(2578,30) | 1669762081
(2587,19) | 1669762082
(2676,17) | 1669762083
(2701,15) | 1669762084
(2707,20) | 1669762085
(2804,22) | 1669762086
(2837,28) | 1669762087
(2902,17) | 1669762088
(2972,28) | 1669762089
(2992,36) | 1669762090
(3014,27) | 1669762091
(3038,24) | 1669762092
(3063,22) | 1669762093
(3116,33) | 1669762094
(3131,28) | 1669762095
(3139,19) | 1669762096
(3167,17) | 1669762097
(3170,28) | 1669762098
(3202,21) | 1669762099
(3213,35) | 1669762100
(3220,21) | 1669762101
(3236,21) | 1669762102
(3282,21) | 1669762103
(3314,22) | 1669762104
(3366,26) | 1669762105
So records with subsequent IDs are not located sequentially but still
locality is good enough.
I think that prefetch can solve this problem.
But now effective_io_concurrency parameter is applicable only for
bitmap scan.
Will it be useful to support it also for index scan?
Or there are some other ways to address this problem?
Thanks in advance,
--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
