Re: Indirect indexes

On Wed, Oct 19, 2016 at 06:58:05PM +0200, Simon Riggs wrote:
> >> I agree. Also, I think the recheck mechanism will have to be something like
> >> what I wrote for WARM i.e. only checking for index quals won't be enough and we
> >> would actually need to verify that the heap tuple satisfies the key in the
> >> indirect index.
> >
> > I personally would like to see how far we get with WARM before adding
> > this feature that requires a DBA to evaluate and enable it.
> 
> Assuming WARM is accepted, that *might* be fine.

First, I love WARM because everyone gets the benefits by default.  For
example, a feature that improves performance by 10% but is only used by
1% of users has a usefulness of 0.1% --- at least that is how I think of
it.

> What we should ask is what is the difference between indirect indexes
> and WARM and to what extent they overlap.
> 
> My current understanding is that WARM won't help you if you update
> parts of a JSON document and/or use GIN indexes, but is effective
> without needing to add a new index type and will be faster for
> retrieval than indirect indexes.
> 
> So everybody please chirp in with benefits or comparisons.

I am not sure we have even explored all the limits of WARM with btree
indexes --- I haven't heard anyone talk about non-btree indexes yet.

-- 
  Bruce Momjian  <bruce@momjian.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 +