PostgresqlHow-ToBeginner · 3 min read

How to Create Multicolumn Index in PostgreSQL: Syntax and Examples

In PostgreSQL, you create a multicolumn index using the CREATE INDEX statement followed by the index name, table name, and a list of columns inside parentheses. For example, CREATE INDEX idx_name ON table_name (column1, column2); creates an index on both columns to speed up queries filtering by these columns.

📐

Syntax

The basic syntax to create a multicolumn index in PostgreSQL is:

CREATE INDEX index_name: Names the index you want to create.
ON table_name: Specifies the table where the index will be created.
(column1, column2, ...): Lists the columns to include in the index in the order they should be indexed.

This index helps speed up queries that filter or sort by these columns in the specified order.

sql

CREATE INDEX index_name ON table_name (column1, column2);

💻

Example

This example creates a multicolumn index on the first_name and last_name columns of the employees table. It helps queries that filter by both names run faster.

sql

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  department VARCHAR(50)
);

CREATE INDEX idx_name ON employees (first_name, last_name);

-- Example query that benefits from this index
EXPLAIN ANALYZE SELECT * FROM employees WHERE first_name = 'John' AND last_name = 'Doe';

Output

QUERY PLAN ------------------------------------------------------------- Index Scan using idx_name on employees (cost=0.29..8.31 rows=1 width=72) (actual time=0.012..0.013 rows=0 loops=1) Index Cond: ((first_name = 'John'::varchar) AND (last_name = 'Doe'::varchar)) Planning Time: 0.123 ms Execution Time: 0.030 ms (5 rows)

⚠️

Common Pitfalls

Common mistakes when creating multicolumn indexes include:

Indexing columns in the wrong order. The order matters because PostgreSQL uses the index starting from the first column.
Creating multicolumn indexes when single-column indexes would suffice, which can waste space.
Expecting the index to speed up queries that filter only by the second or later columns without the first column.

Always analyze your query patterns before creating multicolumn indexes.

sql

/* Wrong: Index order does not match query filter order */
CREATE INDEX idx_wrong ON employees (last_name, first_name);

/* Right: Index order matches query filter order */
CREATE INDEX idx_right ON employees (first_name, last_name);

📊

Quick Reference

Part	Description
CREATE INDEX index_name	Defines the name of the index.
ON table_name	Specifies the table to index.
(column1, column2, ...)	Lists columns included in the index in order.
Order matters	Queries filtering on leading columns use the index efficiently.
Use EXPLAIN	Check if your queries use the index.

✅

Key Takeaways

Use CREATE INDEX with multiple columns inside parentheses to create a multicolumn index.

The order of columns in the index affects which queries can use it efficiently.

Multicolumn indexes speed up queries filtering or sorting by all leading columns.

Avoid creating multicolumn indexes without analyzing query patterns first.

Use EXPLAIN to verify if your queries benefit from the index.