Re: Feature request for adoptive indexes

On Tue, Oct 26, 2021 at 3:45 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Of course, we have that today from the solution of one index with the
> extra columns "included". I think the OP has completely failed to make
> any case why that's not a good enough approach.

I think that the design that the OP is talking about (adaptive
indexes, AKA merged indexes with master detail clustering) have been
the subject of certain research papers. As far as I know nothing like
that has ever been implemented in a real DB system.

It seems like a daunting project, primarily because of the concurrency
control considerations. It's no coincidence that GIN indexes (which
have some of the same issues) only support lossy index scans. Lossy
scans don't seem to be compatible with adaptive indexes, since the
whole point is to have multiple distinct "logical indexes" with a
common prefix, but only one physical index, with clustering. I think
you'd need something like ARIES KVL for concurrency control, just for
starters. Even that is something that won't work with anything like
current Postgres.

It's roughly the same story that we see with generalizing TIDs at the
tableam level. People tend to imagine that it's basically just a
matter of coming up with the right index AM data structure, but that's
actually just the easy part.

--
Peter Geoghegan