Feature request for adoptive indexes

From: Hayk Manukyan <manukyantt(at)gmail(dot)com>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Feature request for adoptive indexes
Date: 2021-10-25 14:07:18
Message-ID: CAF+kZOHx4noAA2H5hLJSJf=Drt0w8Anhcdh+U=nEB_63HvyM3g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2021-10-25 14:23:40 Re: pg_dump versus ancient server versions
Previous Message Robert Haas 2021-10-25 12:59:56 Re: pg_dump versus ancient server versions