BUG #17246: Feature request for adoptive indexes

On Mon, Oct 25, 2021 at 7:20 PM PG Bug reporting form
<noreply@postgresql.org> wrote:

The following bug has been logged on the website:

Bug reference: 17246
Logged by: Hayk Manukyan
Email address: manukyantt@gmail.com
PostgreSQL version: 14.0
Operating system: Any
Description:

Hi everyone. I want to do some feature request regarding indexes, as far as
I know this kind of functionality doesn't exists in Postgres. Here is my
problem :
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.
The Data Structure that I think which can be efficient in this kind of
scenarios is to have 'Adaptive Index' which will be something like
Create index job_nlp_year on ingest_scans_stageing
(`job`,`nlp`,`year`,(`issue_flag`,`scan_id`, `sequence`));
And depend on query it will use or job_nlp_year_scan or
job_nlp_year_issue_flag , or job_nlp_year_sequence ( job, nlp, year and one
of ( `issue_flag` , `scan_id` , `sequence` )
For more description please feel free to refer me

Thanks. What you are proposing looks like a new feature request, it is
relevant to discuss in pgsql-hackers@lists.postgresql.org [1], not
here in the bugs list and it shouldn't be classified as a bug. Feel
free to close this bug and start a discussion in the hackers list.

https://www.postgresql.org/list/

Regards,
Bharath Rupireddy.

Oh sorry,
OK, I cant find the report
Can you close it for me please ?

Best regards

пн, 25 окт. 2021 г. в 17:57, Bharath Rupireddy <
bharath.rupireddyforpostgres@gmail.com>:

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&gt;

The convention here is to inline post.

On Mon, Oct 25, 2021 at 7:31 AM Hayk Manukyan <manukyantt@gmail.com> wrote:

Oh sorry,
OK, I cant find the report

Can you close it for me please ?

This is just a mailing list. There is no formal project managed tracker
backing it. There is nothing to close.

пн, 25 окт. 2021 г. в 17:57, Bharath Rupireddy <
bharath.rupireddyforpostgres@gmail.com>:

Thanks. What you are proposing looks like a new feature request, it is
relevant to discuss in pgsql-hackers@lists.postgresql.org [1]

Actually, my impression was that actual features being implemented with
code belong on -hackers. Feature requests without code should be directed
to -general in order to give the broader community a chance to comment.
That kind of feedback is the way hackers know that something is worthwhile
to pursue.

David J.