Re: WARM and indirect indexes

On Tue, Jan 10, 2017 at 04:24:42PM -0300, Alvaro Herrera wrote:
> Two options are on the table to attack the problem of updates causing
> write amplification: WARM and indirect indexes. They are completely
> different approaches but have overlapping effects on what scenarios are
> improved. Here's a recap of both features, with the intent that we make
> a well-considered decision about each.
>
> The main effect of both features is that an updated tuple doesn't
> require updating indexes that are on unmodified columns. Indirect
> indexes are a completely new server feature which may enable other
> refinements later on; WARM is a targeted optimization on top of the HOT
> optimization.
>
> The big advantage of WARM is that it works automatically, like HOT: the
> user doesn't need to do anything different than today to get the
> benefit. With indirect indexes, the user needs to create the index as
> indirect explicitely.

Thank you for the summary. I think we have to consider two things with
indirect indexes:

1. What percentage speedup is the _average_ user going to get? You
have to consider people who will use indirect indexes who get no benefit
or a net slowdown, and users who will get a benefit.

2. What percentage of users are going to use indirect indexes?

So, for #1 you might have users who are getting +1%, +50%, and -20%, so
maybe +10% average, and for #2 you might have 0.1%. When you multiply
them out, you get 0.01% average improvement per installation, which is
very small. Obviously, these are just wild guesses, but this is just to
make a point. If you assume WARM has been optimized, #1 gets even
lower.

I am not saying we shouldn't do it, but I am afraid that the complexity
in figuring out when to use indirect indexes, combined with the number
of users who will try them, really hurts its inclusion.

FYI, we have a similar issue in adding GUC variables, which I outlined
in this blog post:

http://momjian.us/main/blogs/pgblog/2009.html#January_10_2009

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +