How to Create GIN Index in PostgreSQL for Faster Searches

To create a GIN index in PostgreSQL, use the CREATE INDEX statement with USING GIN followed by the table and column name. This index type is ideal for speeding up searches on array, JSONB, and full-text search columns.

Syntax

The basic syntax to create a GIN index is:

• CREATE INDEX index_name: Names the index.
• ON table_name: Specifies the table to index.
• USING GIN (column_name): Chooses GIN index type on the specified column.

sql

CREATE INDEX index_name ON table_name USING GIN (column_name);

Example

This example creates a GIN index on a JSONB column to speed up key-value searches.

sql

CREATE TABLE products (id serial PRIMARY KEY, attributes jsonb);

INSERT INTO products (attributes) VALUES
  ('{"color": "red", "size": "M"}'),
  ('{"color": "blue", "size": "L"}'),
  ('{"color": "red", "size": "S"}');

CREATE INDEX idx_gin_attributes ON products USING GIN (attributes);

-- Query using the index
SELECT * FROM products WHERE attributes @> '{"color": "red"}';

Output

 id |        attributes        
----+--------------------------
  1 | {"color": "red", "size": "M"}
  3 | {"color": "red", "size": "S"}
(2 rows)

Common Pitfalls

Common mistakes when creating GIN indexes include:

• Trying to create a GIN index on unsupported data types (e.g., plain text columns).
• Not using the USING GIN clause, which defaults to a B-tree index.
• For full-text search, forgetting to use the to_tsvector() function in the index expression.

Always verify the column type and usage before creating a GIN index.

sql

/* Wrong: Missing USING GIN, creates a B-tree index instead */
CREATE INDEX idx_wrong ON products (attributes);

/* Right: Specify USING GIN for JSONB column */
CREATE INDEX idx_right ON products USING GIN (attributes);

Quick Reference

Part	Description
CREATE INDEX index_name	Defines the name of the index
ON table_name	Specifies the table to add the index to
USING GIN	Chooses the GIN index type
(column_name)	The column to index, typically jsonb, array, or tsvector

Key Takeaways

Use CREATE INDEX with USING GIN to create a GIN index in PostgreSQL.

GIN indexes are best for jsonb, array, and full-text search columns.

Always specify USING GIN; otherwise, a default B-tree index is created.

Verify the column data type supports GIN indexing before creating the index.

For full-text search, create GIN indexes on to_tsvector expressions.