pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17246: Feature request for adoptive indexes
5+ messages / 5 participants
[nested] [flat]
* BUG #17246: Feature request for adoptive indexes
@ 2021-10-25 13:35 PG Bug reporting form <noreply@postgresql.org>
2021-10-25 13:56 ` Re: BUG #17246: Feature request for adoptive indexes Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: PG Bug reporting form @ 2021-10-25 13:35 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: manukyantt@gmail.com
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #17246: Feature request for adoptive indexes
2021-10-25 13:35 BUG #17246: Feature request for adoptive indexes PG Bug reporting form <noreply@postgresql.org>
@ 2021-10-25 13:56 ` Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
2021-10-25 14:08 ` Re: BUG #17246: Feature request for adoptive indexes Hayk Manukyan <manukyantt@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Bharath Rupireddy @ 2021-10-25 13:56 UTC (permalink / raw)
To: manukyantt@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #17246: Feature request for adoptive indexes
2021-10-25 13:35 BUG #17246: Feature request for adoptive indexes PG Bug reporting form <noreply@postgresql.org>
2021-10-25 13:56 ` Re: BUG #17246: Feature request for adoptive indexes Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
@ 2021-10-25 14:08 ` Hayk Manukyan <manukyantt@gmail.com>
2021-10-26 19:15 ` Re: BUG #17246: Feature request for adoptive indexes Pavel Borisov <pashkin.elfe@gmail.com>
2021-10-26 19:37 ` Re: BUG #17246: Feature request for adoptive indexes David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Hayk Manukyan @ 2021-10-25 14:08 UTC (permalink / raw)
To: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
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.
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #17246: Feature request for adoptive indexes
2021-10-25 13:35 BUG #17246: Feature request for adoptive indexes PG Bug reporting form <noreply@postgresql.org>
2021-10-25 13:56 ` Re: BUG #17246: Feature request for adoptive indexes Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
2021-10-25 14:08 ` Re: BUG #17246: Feature request for adoptive indexes Hayk Manukyan <manukyantt@gmail.com>
@ 2021-10-26 19:15 ` Pavel Borisov <pashkin.elfe@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Pavel Borisov @ 2021-10-26 19:15 UTC (permalink / raw)
To: Hayk Manukyan <manukyantt@gmail.com>; +Cc: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
>
> > 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;
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #17246: Feature request for adoptive indexes
2021-10-25 13:35 BUG #17246: Feature request for adoptive indexes PG Bug reporting form <noreply@postgresql.org>
2021-10-25 13:56 ` Re: BUG #17246: Feature request for adoptive indexes Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
2021-10-25 14:08 ` Re: BUG #17246: Feature request for adoptive indexes Hayk Manukyan <manukyantt@gmail.com>
@ 2021-10-26 19:37 ` David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: David G. Johnston @ 2021-10-26 19:37 UTC (permalink / raw)
To: Hayk Manukyan <manukyantt@gmail.com>; +Cc: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2021-10-26 19:37 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-10-25 13:35 BUG #17246: Feature request for adoptive indexes PG Bug reporting form <noreply@postgresql.org>
2021-10-25 13:56 ` Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
2021-10-25 14:08 ` Hayk Manukyan <manukyantt@gmail.com>
2021-10-26 19:15 ` Pavel Borisov <pashkin.elfe@gmail.com>
2021-10-26 19:37 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox