Re: Why we lost Uber as a user

* Bruce Momjian (bruce(at)momjian(dot)us) wrote:
> On Fri, Jul 29, 2016 at 10:44:29AM -0400, Stephen Frost wrote:
> > Another thought that was kicking around in my head related to that is if
> > we could have indexes that only provide page-level information (similar
> > to BRIN, but maybe a btree) and which also would allow HOT updates.
> > Those indexes would typically be used in a bitmap index scan where we're
> > going to be doing a bitmap heap scan with a recheck, of course, though I
> > wonder if we could come up with a way to do an in-order bitmap index
> > scan where we sort the tuples on the page and then perform some kind of
> > mergejoin recheck (or just pull out whatever the lowest-not-seen each
> > time we sort the tuples on the page).
>
> So allow HOT updates if the updated row is on the same page, even if the
> indexed column was changed, by scanning the page --- got it.

The idea I had was to allow creation of indexes which *only* include the
page ID. Your rephrase seems to imply that we'd have a regular index
but then be able to figure out if a given tuple had any HOT updates
performed on it and, if so, scan the entire page. As I understand it,
it's more complicated than that because we must involve an index when
updating a tuple in some cases (UNIQUE?) and therefore we don't perform
HOT in the case where any indexed column is being changed.

Of course, this only works if these page-level indexes don't support the
features that prevent HOT updates today. If we can tell which existing
indexes have been built in a such a way to prevent HOT updates and which
would work with a HOT updated tuple, then perhaps we could change the
HOT code to check that when it's considering if a tuple can be updated
using HOT or not and not have only specific indexes able to support HOT
updated tuples.

This is clearly all hand-wavy, but if the the BRIN indexes could work in
this way then it seems like we should be able to generalize what it is
about BRIN that allows it and provide a way for other kinds of indexes
to support tuples being HOT updated. It appears to be clearly useful in
some use-cases. That's really what I was trying to get at.

Thanks!

Stephen