iwantcoding.com
🔥 Daily 👥 Rooms 🏆 Top Log in Sign up

Indexes

By · Reviewed & last updated · Part of PostgreSQL Tutorial

Indexes turn O(n) sequential scans into O(log n) lookups. Postgres ships several index types (B-tree default, GIN, GiST, BRIN, hash) for different query patterns. Each costs disk and slows writes — pick deliberately.

B-tree + GIN + partial + covering

EXAMPLE
-- 1) B-tree — default; equality + range + sort
CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_users_created ON users (created_at);
CREATE INDEX idx_users_email_lower ON users (lower(email));     -- function index

-- Composite — order matters (left-most prefix usable)
CREATE INDEX idx_posts_user_created ON posts (user_id, created_at DESC);
-- Helpful for:
--   WHERE user_id = ?
--   WHERE user_id = ? ORDER BY created_at DESC
-- NOT helpful for:
--   WHERE created_at = ?            ← missing left-most prefix

-- 2) UNIQUE — same B-tree + uniqueness constraint
CREATE UNIQUE INDEX uq_users_email ON users (lower(email));

-- 3) PARTIAL — index a subset of rows
CREATE INDEX idx_posts_published ON posts (created_at)
WHERE status = 'published';
-- Tiny + fast for “published” queries; rest of the table doesn't pay

-- 4) COVERING (INCLUDE) — index covers extra columns for index-only scans
CREATE INDEX idx_users_email_inc ON users (email)
INCLUDE (name, created_at);
-- SELECT name, created_at FROM users WHERE email = ?  → no heap fetch

-- 5) GIN — array, JSONB, full-text search
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);          -- text[]
CREATE INDEX idx_posts_payload ON posts USING GIN (payload);    -- jsonb
CREATE INDEX idx_posts_fts ON posts USING GIN (
    to_tsvector('english', body)
);

-- 6) GiST — geometric, full-text, exclusion constraints
CREATE INDEX idx_events_when ON events USING GiST (tstzrange);

-- 7) BRIN — huge, naturally-ordered tables (logs)
CREATE INDEX idx_events_ts_brin ON events USING BRIN (ts);
-- Tiny (KB even for billions of rows); only effective if data is physically ordered.

-- 8) Concurrent creation — no table-level lock (longer)
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

-- 9) Inspect
SELECT * FROM pg_indexes WHERE tablename = 'users';
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'ada@example.com';
-- look for “Index Scan using idx_users_email”

-- 10) Cost — each index slows INSERT/UPDATE/DELETE
-- Audit unused indexes:
SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0 AND schemaname='public';

Why it matters

CREATE INDEX CONCURRENTLY is the only safe way to add an index on a busy production table. The non-concurrent variant locks writes for the duration of the build — a frequent cause of accidental outages.

Tip: Tweak the snippet with Try it Yourself », then sit the quiz at the bottom of the page.

Example

Example
CREATE INDEX idx_users_email ON users (email);
CREATE UNIQUE INDEX uq_users_email ON users (lower(email));
-- Inspect
SELECT * FROM pg_indexes WHERE tablename = 'users';
Try it Yourself »

Exercise

Create a B-tree index on users.email.

CREATE idx_users_email ON users(email);

Discussion

Loading…