Re: BUG #17246: Feature request for adoptive indexes

On Mon, Oct 25, 2021 at 7:20 PM PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 17246
> Logged by: Hayk Manukyan
> Email address: manukyantt(at)gmail(dot)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(at)lists(dot)postgresql(dot)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.