Re: Feature request for adoptive indexes

Hi all
First of all thank you all for fast and rich responses, that is really nice.
I don't have that deep knowledge of how postgres works under the hood so I
will try to explain more user side.
I want to refer for some points mentioned above.
- First INCLUDE statement mostly eliminates the necessity to refer to a
clustered index or table to get columns that do not exist in the index. So
filtering upon columns in INCLUDE statement will not be performant. It can
give some very little performance if we include additional columns but it
is not in level to compare with indexed one. I believe this not for this
case
- Tomas Vondra's Assumption that adaptive should be something between this
two
1) (job, nlp, year, sequence)
2) (job, nlp, year, scan_id, issue_flag, sequence)
is completely valid. I have made fairly small demo with this index
comparison and as I can see the difference is noticeable. Here is git repo
and results
<https://github.com/HaykManukyanAvetiky/index_comparition/blob/main/results.md>
,
I had no much time to do significant one sorry for that ))
- regarding data structure side of things by Pavel Borisov.
I also think that different data structure will be needed. Not sure exactly
at this point which kind of data structure but I will try to explain it
here.
<https://github.com/HaykManukyanAvetiky/index_comparition/blob/main/data_structure.md>

best regards

ср, 27 окт. 2021 г. в 20:10, Peter Geoghegan <pg(at)bowt(dot)ie>:

> On Wed, Oct 27, 2021 at 1:02 AM Pavel Borisov <pashkin(dot)elfe(at)gmail(dot)com>
> wrote:
> > AFAIK Gin is lossy for phrase queries as we don't store word position in
> the posting list. For purely logical queries, where position doesn't
> matter, it's not lossy.
>
> GIN is always lossy, in the sense that it provides only a
> gingetbitmap() routine -- there is no gingettuple() routine. I believe
> that this is fundamental to the overall design of GIN. It would be
> very difficult to add useful gingettuple() functionality now, since
> GIN already relies on lossiness to avoid race conditions.
>
> Here's an example of the problems that "adding gingettuple()" would
> run into: Today, an index's pending list entries can be merged
> concurrently with the entry tree, without worrying about returning the
> same tuples twice. This is only safe/correct because GIN only supports
> bitmap index scans. Without that, you need some other mechanism to
> make it safe -- ISTM you must "logically lock" the index structure,
> using ARIES/KVL style key value locks, or something along those lines.
>
> --
> Peter Geoghegan
>