Обсуждение: BUG #17246: Feature request for adoptive indexes
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
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>:
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.
> 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?
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.