Re: BUG #17246: Feature request for adoptive indexes

>
> > I need to create following indexes:
>> > Create index job_nlp_year_scan on ingest_scans_stageing
>> > (`job`,`nlp`,`year`,`scan_id`);
>> > Create index job_nlp_year_issue_flag on ingest_scans_stageing
>> > (`job`,`nlp`,`year`,`issue_flag`);
>> > Create index job_nlp_year_sequence on ingest_scans_stageing
>> > (`job`,`nlp`,`year`,`sequence`);
>> > As you can see the first 3 columns are the same (job, nlp, year). so if
>> I
>> > create 3 different indexes db should manage same job_nlp_year structure
>> 3
>> > times.
>>
> I think now in many cases you can effectively use covering index to have
fast index-only scans without index duplication. It will help if you don't
have great selectivity on the last column (most probably you don't). E.g.:

CREATE INDEX ON table_name (`job`,`nlp`,`year`) INCLUDE (`scan_id`,
`issue_flag`, `sequence`)

But I consider the feature can be useful when there is a very little
selectivity in the first index columns. I.e. if (job`,`nlp`,`year') has
many repeats and the most selection is done in the last column. I am not
sure how often this can arise but in general, I see it as a useful b-tree
generalization.

I'm not sure how it should be done. In my view, we need to add an ordered
posting tree as a leaf element if b-tree and now we have index storage only
for tuples. The change of on-disk format was previously not easy in nbtree
and if we consider the change, we need an extra bit to mark posting trees
among index tuples. Maybe it could be done in a way similar to deduplicated
tuples if some bits in the tuple header are still could be freed.

Thoughts?

--
Best regards,
Pavel Borisov

Postgres Professional: http://postgrespro.com <http://www.postgrespro.com>