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 following configuration:
- 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.

  Heikki Linnakangas

---------------------------(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

Reply via email to