Re: Feature request for adoptive indexes

Hi,

On 10/25/21 16:07, Hayk Manukyan wrote:
> 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

It's not very clear what exactly would the "adaptive index" do, except
that it'd have all three columns. Clearly, the three columns can't be
considered for ordering etc. but need to be in the index somehow. So why
wouldn't it be enough to either to create an index with all six columns?

CREATE INDEX ON job_nlp_year_scan (job, nlp, year, scan_id, issue_flag,
sequence);

or possibly with the columns just "included" in the index:

CREATE INDEX ON job_nlp_year_scan (job, nlp, year) INCLUDE (scan_id,
issue_flag, sequence);

If this does not work, you either need to explain more clearly what
exactly the adaptive indexes does, or show queries that can't benefit
from these existing features.

regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company