Re: Feature request for adoptive indexes

> On Oct 26, 2021, at 1:43 PM, Tomas Vondra <tomas(dot)vondra(at)enterprisedb(dot)com> wrote:
>
> I'm still rather skeptical about it - for such feature to be useful the prefix columns must not be very selective, i.e. the posting trees are expected to be fairly large (e.g. 5-7k rows). It pretty much has to to require multiple (many) index pages, in order for the "larger" btree index to be slower. And at that point I'd expect the extra overhead to be worse than simply defining multiple simple indexes.

For three separate indexes, an update or delete of a single row in the indexed table would surely require changing at least three pages in the indexes. For some as-yet-ill-defined combined index type, perhaps the three entries in the index would fall on the same index page often enough to reduce the I/O cost of the action? This is all hard to contemplate without a more precise description of the index algorithm.

Perhaps the OP might want to cite a paper describing a particular index algorithm for us to review?

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company