September 26, 2024: PostgreSQL 17 Released!
Supported Versions: 16 / 15 / 14 / 13 / 12
Unsupported versions: 11 / 10 / 9.6 / 9.5 / 9.4 / 9.3 / 9.2 / 9.1 / 9.0 / 8.4 / 8.3 / 8.2
This documentation is for an unsupported version of PostgreSQL.
You may want to view the same page for the current version, or one of the other supported versions listed above instead.

52.5. Limitations

GIN doesn't support full index scans. The reason for this is that extractValue is allowed to return zero keys, as for example might happen with an empty string or empty array. In such a case the indexed value will be unrepresented in the index. It is therefore impossible for GIN to guarantee that a scan of the index can find every row in the table.

Because of this limitation, when extractQuery returns nkeys = 0 to indicate that all values match the query, GIN will emit an error. (If there are multiple ANDed indexable operators in the query, this happens only if they all return zero for nkeys.)

It is possible for an operator class to circumvent the restriction against full index scan. To do that, extractValue must return at least one (possibly dummy) key for every indexed value, and extractQuery must convert an unrestricted search into a partial-match query that will scan the whole index. This is inefficient but might be necessary to avoid corner-case failures with operators such as LIKE or subset inclusion.

GIN assumes that indexable operators are strict. This means that extractValue will not be called at all on a NULL value (so the value will go unindexed), and extractQuery will not be called on a NULL comparison value either (instead, the query is presumed to be unmatchable).

A possibly more serious limitation is that GIN cannot handle NULL keys — for example, an array containing a NULL cannot be handled except by ignoring the NULL.