On Sat, Feb 3, 2018 at 8:05 PM, Vitaliy Garnashevich <vgarnashevich@xxxxxxxxx> wrote: > Looks like this behavior is not caused by, and does not depend on: > - variable performance in the cloud > - order of rows in the table > - whether the disk is EBS (backed by SSD or HDD), or ordinary SSD > - kernel version > > Does this mean that the default setting for eic on Linux is just inadequate > for how the modern kernels behave? Or am I missing something else in the > tests? > > Regards, > Vitaliy I have analyzed this issue quite extensively in the past, and I can say with high confidence that you're analysis on point 2 is most likely wrong. Now, I don't have all the information to make that a categorical assertion, you might have a point, but I believe you're misinterpreting the data. I mean, that the issue is indeed affected by the order of rows in the table. Random heap access patterns result in sparse bitmap heap scans, whereas less random heap access patterns result in denser bitmap heap scans. Dense scans have large portions of contiguous fetches, a pattern that is quite adversely affected by the current prefetch mechanism in linux. This analysis does point to the fact that I should probably revisit this issue. There's a rather simple workaround for this, pg should just avoid issuing prefetch orders for sequential block patterns, since those are already much better handled by the kernel itself.
