How to Create Index on Array in PostgreSQL for Faster Queries

In PostgreSQL, you create an index on an array column using a GIN index type. Use the syntax CREATE INDEX index_name ON table_name USING GIN (array_column); to speed up queries that search for elements inside arrays.

Syntax

To create an index on an array column in PostgreSQL, you use the CREATE INDEX statement with the USING GIN clause. This tells PostgreSQL to use a Generalized Inverted Index (GIN), which is efficient for array element searches.

• index_name: The name you give to your index.
• table_name: The table that contains the array column.
• array_column: The column of type array you want to index.

sql

CREATE INDEX index_name ON table_name USING GIN (array_column);

Example

This example shows how to create a table with an integer array column, insert some data, create a GIN index on the array column, and then query for rows containing a specific element in the array.

sql

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  tags INTEGER[]
);

INSERT INTO products (tags) VALUES
  (ARRAY[1, 2, 3]),
  (ARRAY[2, 3, 4]),
  (ARRAY[4, 5, 6]);

CREATE INDEX idx_products_tags ON products USING GIN (tags);

-- Query to find products with tag 3
SELECT * FROM products WHERE tags @> ARRAY[3];

Output

 id |   tags   
----+----------
  1 | {1,2,3}
  2 | {2,3,4}
(2 rows)

Common Pitfalls

One common mistake is trying to create a regular B-tree index on an array column, which is not supported and will cause an error. Another is forgetting to use the GIN index type, which is necessary for indexing array elements efficiently.

Also, using the wrong operator in queries can prevent the index from being used. For arrays, use operators like @> (contains) or && (overlaps) to leverage the GIN index.

sql

/* Wrong: B-tree index on array column (will fail) */
CREATE INDEX idx_wrong ON products (tags);

/* Right: GIN index on array column */
CREATE INDEX idx_right ON products USING GIN (tags);

Quick Reference

Command	Description
CREATE INDEX idx_name ON table USING GIN (array_column);	Creates a GIN index on an array column.
SELECT * FROM table WHERE array_column @> ARRAY[element];	Find rows where array contains the element.
SELECT * FROM table WHERE array_column && ARRAY[elements];	Find rows where arrays overlap.
B-tree index on array column	Not supported, causes error.

Key Takeaways

Use a GIN index to efficiently index array columns in PostgreSQL.

Create the index with: CREATE INDEX index_name ON table USING GIN (array_column);

Use array operators like @> or && in queries to benefit from the GIN index.

Do not use B-tree indexes on array columns; they are unsupported.

Indexing arrays speeds up searches for elements inside the arrays.