On Wed, Mar 22, 2017 at 12:30 AM, Bruce Momjian <bruce@momjian.us> wrote:
Well, it is really a question of how often you want to do a second WARM update (not possible) vs. the frequency of lazy vacuum. I assumed that would be a 100X or 10kX difference, but I am not sure myself either. My initial guess was that only allowing a single WARM update between lazy vacuums would show no improvementin in real-world workloads, but maybe I am wrong.
It's quite hard to say that until we see many more benchmarks. As author of the patch, I might have got repetitive with my benchmarks. But I've seen over 50% improvement in TPS even without chain conversion (6 indexes on a 12 column table test).
With chain conversion, in my latest tests, I saw over 100% improvement. The benchmark probably received between 6-8 autovac cycles in an 8hr test. This was with a large table which doesn't fit in memory or barely fit in memory. Graphs attached again just in case you missed (x-axis test duration in seconds, y-axis moving average of TPS)
May be we should run another set with just 2 or 3 indexes on a 12 column table and see how much that helps, if at all. Or may be do a mix of HOT and WARM updates. Or even just do HOT updates on small and large tables and look for any regression. Will try to schedule some of those tests.