Postgres Gin Index read performance

Question

One advantage of the separate-column approach over an expression index is that ... Another advantage is that searches will be faster, since it will not be necessary to redo the to_tsvector calls to verify index matches.

Why does a gin expression index to_tsvector('english', body) has to "verify index matches"? It seems that index are automatically updated after every update/insert, All indices have same update issue and this might not be the point to be concerned.

Laurenz Albe · Accepted Answer · 2022-09-07 15:37:36Z

1

I think this deals with the "recheck" which is necessary, since the GIN index scan is potentially lossy: it will return values that contain all elements from the tsvector you search for. All these rows get rechecked to see if they really match the tsquery. That means that the to_tsvector function is evaluated for all rows that are returned by the index scan.

answered Sep 7, 2022 at 15:37

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MEDS Over a year ago

Thank you for the quick reply. So for both the "separate-column approach" and "expression index approach", the recheck phase is unaviodable. The second approach needs an extra "redo the to_tsvector calls" to get the ts_vector and this is why the doc says "Another advantage is that searches will be faster"?

Laurenz Albe Over a year ago

I guess so, and I don't know for certain whether there will always be a recheck (I didn't bother to look at the code).

jjanes · Accepted Answer · 2022-09-07 16:15:58Z

1

As the docs say, that is more important for GIST than for GIN.

GIN indexes can still need to be rechecked if work_mem is too small to hold the entire bitmap so they go lossy. They will also need to be rechecked if the pattern uses relative position indicators like <->, <2> etc.

It might also need rechecking if you have many &ed together tokens and it just decides to recheck the more common of them rather than bothering with all the bitmaps for them (I'm not sure if actually does this here or not--I've never witnessed it for @@ but without having inspected the entire code I can't rule out the possibility) or maybe if you have complicated boolean tsquery expressions.

edited Sep 7, 2022 at 16:15

answered Sep 7, 2022 at 16:08

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

2 Comments

MEDS Over a year ago

The new version doc says As inverted indexes, they contain an index entry for each word (lexeme), with a compressed list of matching locations. So position in tsquery can be properly handled by the GIN index? But complicated ts_query might still require a "recheck" on the matching row?

jjanes Over a year ago

@MEDS No, 'locations' there only means the locations of the row within the table, not the location of the lexeme within the document. (That wording does seem to invite confusion.) There is a custom index type which does store the lexeme locations, and so could avoid the recheck. I've had mixed luck with it, it might be worth a try.

Collectives™ on Stack Overflow

Postgres Gin Index read performance

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related