©
This document usesPHP Chinese website manualRelease
Internally, aGINindex contains a B-tree index constructed over keys, where each key is an element of the indexed value (a member of an array, for example) and where each tuple in a leaf page is either a pointer to a B-tree over heap pointers (PT, posting tree), or a list of heap pointers (PL, posting list) if the list is small enough.
Updating aGINindex tends to be slow because of the intrinsic nature of inverted indexes: inserting or updating one heap row can cause many inserts into the index (one for each key extracted from the indexed value). As ofPostgreSQL8.4,GINis capable of postponing much of this work by inserting new tuples into a temporary, unsorted list of pending entries. When the table is vacuumed, or if the pending list becomes too large (larger than work_mem), the entries are moved to the mainGINdata structure using the same bulk insert techniques used during initial index creation. This greatly improvesGINindex update speed, even counting the additional vacuum overhead. Moreover the overhead can be done by a background process instead of in foreground query processing.
The main disadvantage of this approach is that searches must scan the list of pending entries in addition to searching the regular index, and so a large list of pending entries will slow searches significantly. Another disadvantage is that, while most updates are fast, an update that causes the pending list to become"too large"will incur an immediate cleanup cycle and thus be much slower than other updates. Proper use of autovacuum can minimize both of these problems.
If consistent response time is more important than update speed, use of pending entries can be disabled by turning off theFASTUPDATEstorage parameter for aGINindex. See CREATE INDEX for details.
GIN can support"partial match"queries, in which the query does not determine an exact match for one or more keys, but the possible matches fall within a reasonably narrow range of key values (within the key sorting order determined by thecomparesupport method). TheextractQuerymethod, instead of returning a key value to be matched exactly, returns a key value that is the lower bound of the range to be searched, and sets thepmatchflag true. The key range is then searched using thecomparePartialmethod.comparePartialmust return zero for an actual match, less than zero for a non-match that is still within the range to be searched, or greater than zero if the index key is past the range that could match.
