Tom Lane wrote:
Also, I tentatively reduced the threshold
at which heapscans switch to ring mode to NBuffers/16; that probably
needs more thought.
Yeah. One scenario where threshold < shared_buffers will hurt is if your
shared_buffers >= RAM size / 2. In that scenario, a scan on a table that
would barely fit in shared_buffers, will use the ring instead and not
fit in the OS cache either. Which means that repeatedly scanning that
table will do physical I/O with the patch, but not without it.
"swappiness", using linux terms, also makes a difference. When I started
testing the patch, I saw unexpectedly high gains from the patch with the
- RAM size 4 GB
- shared_buffers 1 GB
- table size 3GB
Without the patch, the table wouldn't fit in shared_buffers, and also
wouldn't fit in the OS cache, so repeatedly scanning the table always
read the table physically from disk, and it took ~20 seconds. With the
patch, however, the ring only actively used a few pages from
shared_buffers, and the kernel swapped out the rest. Thanks to that,
there was more than 3GB of RAM available for OS caching, the table fit
completely in the OS cache, and the query took < 2 seconds. It took me
quite a while to figure out what's going on.
Lastly, I haven't done anything about making
non-btree indexes honor the access strategy during VACUUM scans.
Also there's no attempt to not inflate usage_count, which means that
synchronized scans will spoil the buffer cache as if we didn't have the
buffer ring patch. If there's no easy solution, I think we could live
with that, but Greg's suggestion of bumping the usage_count in PinBuffer
instead of UnpinBuffer sounds like a nice solution to me.
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not