How to Create an Index in PostgreSQL (With Examples)

Introduction

PostgreSQL offers more index types than any other database. Choosing the right one can mean the difference between a 5-second query and a 5-millisecond query.

Create a Basic B-tree Index

B-tree is the default and most common index type. It works for equality (=) and range (<, >, BETWEEN) queries.

-- Default B-tree index:
CREATE INDEX idx_users_email ON users (email);

-- Verify:
\di+ idx_users_email

-- Or query:
SELECT * FROM pg_indexes WHERE tablename = 'users';

Create a Unique Index

Unique indexes prevent duplicate values and also speed up lookups.

-- Unique index:
CREATE UNIQUE INDEX idx_users_email_unique ON users (email);

-- Multi-column unique:
CREATE UNIQUE INDEX idx_team_members
ON team_members (team_id, user_id);

Create a Partial Index

Partial indexes only cover rows matching a WHERE condition. They are smaller and faster than full indexes.

-- Index only active users:
CREATE INDEX idx_active_users
ON users (email)
WHERE active = true;

-- Index only recent orders:
CREATE INDEX idx_recent_orders
ON orders (created_at)
WHERE created_at > '2026-01-01';

Create a GIN Index (for JSONB, arrays, full-text)

GIN (Generalized Inverted Index) is perfect for JSONB columns, arrays, and full-text search.

-- GIN index on JSONB column:
CREATE INDEX idx_products_metadata
ON products USING GIN (metadata);

-- Now JSONB queries use the index:
SELECT * FROM products
WHERE metadata @> '{"category": "electronics"}';

-- Full-text search GIN index:
CREATE INDEX idx_posts_search
ON posts USING GIN (to_tsvector('english', title || ' ' || body));

Create Index Concurrently (No Downtime)

Regular CREATE INDEX locks the table for writes. CONCURRENTLY avoids this at the cost of longer creation time.

-- Non-blocking index creation:
CREATE INDEX CONCURRENTLY idx_orders_customer
ON orders (customer_id);

-- Note: CONCURRENTLY cannot run inside a transaction
-- Note: If it fails, you get an INVALID index. Drop and retry:
-- DROP INDEX idx_orders_customer;

Analyze Query Performance

Use EXPLAIN ANALYZE to see if your index is being used and measure actual execution time.

-- EXPLAIN ANALYZE shows actual execution:
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'john@example.com';

-- Look for:
-- Index Scan or Index Only Scan (good)
-- Seq Scan (full table scan = needs index)
-- Execution Time: actual time in ms

Best Practices

• Use CONCURRENTLY for production index creation to avoid downtime
• Prefer partial indexes when you only query a subset of rows
• Use GIN for JSONB and full-text search, B-tree for everything else
• Run EXPLAIN ANALYZE (not just EXPLAIN) to see actual performance
• Periodically run REINDEX to rebuild bloated indexes

Generate PostgreSQL Queries with AI2SQL

Skip the syntax lookup. Describe what you need in plain English and AI2SQL generates the correct PostgreSQL query instantly.

Frequently Asked Questions