GIN

Component search

GIN indexes are “ inverted indexes ” which are appropriate for data values that contain multiple component values, such as arrays. An inverted index contains a separate entry for each component value, and can efficiently handle queries that test for the presence of specific component values.

Reading PostgreSQL 18.6.

Capabilities · PostgreSQL 18.6

These are same-version documentary conclusions. Conditional capabilities depend on the operator class, indexed type or query; they are not universal guarantees.

Capability	Support	Conditions and evidence
Sorted output	No	Ordinary sorted output is distinct from ordering by an operator, such as nearest-neighbor distance. Same-version documentation In addition to simply finding the rows to be returned by a query, an index may be able to deliver them in a specific sorted order. This allows a query's ORDER BY specification to be honored without a separate sorting step. Of the index types currently supported by PostgreSQL , only B-tree can produce sorted output — the other index types return matching rows in an unspecified, implementation-dependent order. PostgreSQL 18.6 · indexes-ordering
Unique keys	No	This means a unique index. Exclusion constraints use a different contract. Same-version documentation Currently, only B-tree indexes can be declared unique. PostgreSQL 18.6 · indexes-unique
Multiple key columns	Yes	Multiple search keys are distinct from non-key INCLUDE payload columns. Same-version documentation Currently, only the B-tree, GiST, GIN, and BRIN index types support multiple-key-column indexes. Whether there can be multiple key columns is independent of whether INCLUDE columns can be added to the index. Indexes can have up to 32 columns, including INCLUDE columns. (This limit can be altered when building PostgreSQL ; see the file pg_config_manual.h .) PostgreSQL 18.6 · indexes-multicolumn
INCLUDE columns	No	Payload columns do not become search keys. Wide payloads can exceed the index tuple-size limit. Same-version documentation Currently, the B-tree, GiST and SP-GiST index access methods support this feature. In these indexes, the values of columns listed in the INCLUDE clause are included in leaf tuples which correspond to heap tuples, but are not included in upper-level index entries used for tree navigation. PostgreSQL 18.6 · sql-createindex
Index-only scans	No	The query must use covered values; visibility-map state determines whether heap visits can be avoided. Same-version documentation The index type must support index-only scans. B-tree indexes always do. GiST and SP-GiST indexes support index-only scans for some operator classes but not others. Other index types have no support. The underlying requirement is that the index must physically store, or else be able to reconstruct, the original data value for each index entry. As a counterexample, GIN indexes cannot support index-only scans because each index entry typically holds only part of the original data value. PostgreSQL 18.6 · indexes-index-only-scans
Distance ordering	Not established	Availability depends on the chosen operator class and ordering operator. The sampled source does not establish this capability. This is not a “no”.
Parallel index scan	No	A cooperating scan of one index is distinct from a parallel bitmap heap scan or separate serial scans under Parallel Append. Same-version documentation In a parallel index scan or parallel index-only scan , the cooperating processes take turns reading data from the index. Currently, parallel index scans are supported only for btree indexes. Each process will claim a single index block and will scan and return all tuples referenced by that block; other processes can at the same time be returning tuples from a different index block. The results of a parallel btree scan are returned in sorted order within each worker process. PostgreSQL 18.6 · parallel-plans
Parallel index build	Yes	Parallel construction is separate from parallel scans. Worker availability, settings and build phases also matter. Same-version documentation PostgreSQL can build indexes while leveraging multiple CPUs in order to process the table rows faster. This feature is known as parallel index build . For index methods that support building indexes in parallel (currently, B-tree, GIN, and BRIN), maintenance_work_mem specifies the maximum amount of memory that can be used by each index build operation as a whole, regardless of how many worker processes were started. Generally, a cost model automatically determines how many worker processes should be requested, if any. PostgreSQL 18.6 · sql-createindex

Queries, operators and limitations

CREATE INDEX name ON table_name USING gin (column_name);

Syntax template; replace the table and column placeholders with suitable objects and choose a compatible operator class.

Like GiST and SP-GiST, GIN can support many different user-defined indexing strategies, and the particular operators with which a GIN index can be used vary depending on the indexing strategy. As an example, the standard distribution of PostgreSQL includes a GIN operator class for arrays, which supports indexed queries using these operators:

<@   @>   =   &&

(See Section 9.19 for the meaning of these operators.) The GIN operator classes included in the standard distribution are documented in Table 65.3 . Many other GIN operator classes are available in the contrib collection or as separate projects. For more information see Section 65.4 .

Storage options

Set index options with CREATE INDEX … WITH or ALTER INDEX … SET. Defaults and effective behavior depend on the access method and selected PostgreSQL version.

`fastupdate`

Controls usage of the fast update technique described in Section 65.4.4.1 . ON enables fast update, OFF disables it. The default is ON .

Turning fastupdate off via ALTER INDEX prevents future insertions from going into the list of pending index entries, but does not in itself flush existing entries. You might want to VACUUM the table or call the gin_clean_pending_list function afterward to ensure the pending list is emptied.

Same-version option definition

`gin_pending_list_limit`

Overrides the global setting of gin_pending_list_limit for this index. This value is specified in kilobytes.

Same-version option definition

Version comparison

GIN

Parallel index build: No → Yes

This comparison covers capability states and the names of documented storage options. It does not compare every algorithm, performance characteristic, option definition or release-note change.

Documented history

PostgreSQL 17 → 18

Parallel index build: No → Yes

PostgreSQL 10 → 11

Parallel index build: Not established → No (source coverage changed)

PostgreSQL 9.6 → 10

Parallel index scan: Not established → No (source coverage changed)

PostgreSQL 9.5 → 9.6

Index-only scans: Not established → No (source coverage changed)

PostgreSQL 9.4 → 9.5

Storage option recorded: gin_pending_list_limit

Sources and build identity

Extracted from the loaded English manual build 18.6. No runtime capability measurement is claimed.

Source fingerprints

Manual build: PostgreSQL 18.6 Documentation
Combined page SHA-256: 0e39ebd4fddbd990280e62f4503f0bd18765bbd990dccf4cba976b5d47da45e7
Source archive recorded by the manual manifest: 18.6
Archive SHA-256 recorded by the manual manifest: 555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f

indexes-types.html: cee340145a2ef5fbd807f66755b10d22bb049e328c460f572f45ad6193e7e4a1
catalog-pg-am.html: 579364179365a51945e2edb6481df5d198916c2c725e506f166824dedf08994c
hash-index.html: b994550220e1ee05648d09804c9eb2302834e07e261932420a96605d7c9c6140
indexam.html: 0a38f7c0ac0541f31e086c83a624e782c15367456578a6dd3329882516bc1080
indexes-index-only-scans.html: 2322ec12b82f3fd01ee4072049caaa5a0190148ac68d98c0fcd6eda958828a83
indexes-multicolumn.html: c0a799ec49b8ef057a63d465fb1d72a3b81e16d652c28a71734d45592d081202
indexes-opclass.html: 07de06abf3a2aff2a891a2789def434d9b76705e54d45ca5338dc13451ae2962
indexes-ordering.html: 85fac99c7c66ed19dd021f12b5cc53ce18aa5bfa4b56c8c168317f99b8f12d3b
indexes-unique.html: 7c3eac7fa180b22534ff01df0c68155f15b6b360ffe35507c5266059116840db
parallel-plans.html: 62207d207bead82b01b59dc119c4f95856f08655cc11d4699a05a40867ed2070
sql-createindex.html: 6aa00da55815d13d0355dd25cb1cdb603fa36964993f164d1b6f486011282580

Back to Index AM · Recorded in PostgreSQL 9.0 through 20; first presence in this sample does not establish first introduction.