PostgreSQL:CompositeIndex

lastmod 2026-02-26created 2026-02-261 edits

A PostgreSQL composite index (Composite Index) combines two or more columns into a single index. Because the B-Tree structure sorts by the columns in order, it can be used starting from the leading column (leftmost prefix) in order.

Usage

CREATE INDEX idx_example ON orders(customer_id, created_at);

This index is sorted internally as follows:

customer_id | created_at
------------|---------------------
AAA         | 2025-01-01 09:00:00
AAA         | 2025-03-15 14:30:00
BBB         | 2025-02-10 11:00:00
BBB         | 2025-04-20 08:45:00
CCC         | 2025-01-05 16:20:00

Leftmost Prefix Rule

A composite index can be used only for combinations of consecutive columns starting from the left.

-- 인덱스: (customer_id, created_at)

Why it doesn't work without the leading column

A B-Tree is like a phone book. When a phone book is sorted in (last name, first name) order:

• People with the last name "Kim" → can be found right away (leading column)
• "Kim Younghee" → can be found right away (full match)
• People whose first name is "Younghee" → you have to search through everything (leading column missing)

UNIQUE constraints and indexes

In PostgreSQL, a UNIQUE constraint automatically creates an index. So creating a separate index on the same column is redundant.

Single-column UNIQUE

-- UNIQUE 제약 조건이 자동으로 인덱스를 생성한다
CREATE TABLE org_perms (
    name TEXT NOT NULL UNIQUE  -- 내부적으로 UNIQUE INDEX 생성
);

-- 따라서 아래는 중복이다
CREATE INDEX idx_org_perms_name ON org_perms(name);  -- 불필요

Composite UNIQUE

CREATE TABLE org_members (
    org_id  UUID NOT NULL,
    user_id UUID NOT NULL,
    UNIQUE(org_id, user_id)  -- (org_id, user_id) 복합 인덱스 자동 생성
);

PRIMARY KEY and indexes

PRIMARY KEY also creates a UNIQUE INDEX internally.

CREATE TABLE orders (
    id UUID PRIMARY KEY  -- UNIQUE INDEX 자동 생성
);

CREATE INDEX idx_orders_id ON orders(id);  -- 불필요

Designing column order

The column order of a composite index should be decided by query patterns.

Principles

1. Equality (=) conditions first, range (>, <, BETWEEN) conditions later
2. High-cardinality columns (many unique values) first
3. Columns often queried alone first (leftmost prefix rule)

Example

-- 쿼리: WHERE org_id = ? AND created_at > ?
-- 좋은 순서: org_id가 등호 조건이므로 선행
CREATE INDEX idx_good ON org_members(org_id, created_at);

-- 나쁜 순서: 범위 조건이 선행하면 org_id 인덱스 활용 불가
CREATE INDEX idx_bad ON org_members(created_at, org_id);

Composite indexes with 3 or more columns

CREATE INDEX idx_triple ON table_name(a, b, c);

Query to find duplicate indexes

Query to find duplicate indexes in the current database:

SELECT
    a.indexrelid::regclass AS index_a,
    b.indexrelid::regclass AS index_b,
    a.indrelid::regclass   AS table_name
FROM pg_index a
JOIN pg_index b
    ON a.indrelid = b.indrelid
    AND a.indexrelid != b.indexrelid
    AND (
        a.indkey::text = b.indkey::text
        OR a.indkey::text LIKE b.indkey::text || ' %'
    )
WHERE a.indisvalid AND b.indisvalid;

See also

• PostgreSQL

Favorite site

• PostgreSQL: Multicolumn Indexes
• Use The Index, Luke: Concatenated Keys