Re: WARM and indirect indexes

On Wed, Jan 11, 2017 at 7:55 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

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

That could also be seen as an advantage to indirect indexes. While I
haven't seen the code, I believe indirect index code will only be hit if
someone actually uses them. So there won't be any overhead for other users
who do not wish to use the feature. WARM on the other hand will be "always
on" feature, even for system tables. That clearly has advantages, both from
usability perspective as well as the fact that the code will be heavily
tested. But if there are cases which get adversely affected by WARM, they
will have to pay the price for larger benefit.

To me, a better strategy is probably to focus on one of the patches, get
that in and then evaluate the second patch, both from complexity as well as
performance given that the first patch may have narrowed the gaps.

I was going to ask if we could implement indirect indexes as a separate
IndexAM. But I re-read this thread and found that you'd in fact done it
that way in the first version but then discarded it for performance
reasons. Is there a merit in evaluating that path for indirect indexes
again?

Thanks,
Pavan

--
Pavan Deolasee http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services