On Tue, Oct 26, 2021 at 3:45 PM Tom Lane <t...@sss.pgh.pa.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
