CREATE INDEX

Creates an index to speed up queries on specific columns. PostgreSQL supports B-tree, Hash, GiST, GIN, and BRIN indexes.

Syntax

postgresql

CREATE INDEX [CONCURRENTLY] name ON table (column);

Example

postgresql

-- Basic index:
CREATE INDEX idx_users_email ON users (email);

-- Partial index (for common query pattern):
CREATE INDEX idx_active_users ON users (created_at)
WHERE active = true;

-- Concurrent (no table lock):
CREATE INDEX CONCURRENTLY idx_orders_user
ON orders (user_id);