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 , 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.
best regards
ср, 27 окт. 2021 г. в 20:10, Peter Geoghegan <
pg@bowt.ie>:
On Wed, Oct 27, 2021 at 1:02 AM Pavel Borisov <pashkin.elfe@gmail.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