PostgreSQL indexes and EXPLAIN: optimize queries with real data

Indexes are not free#

Indexes can make reads faster, but they add storage and slow down writes. Add indexes based on real query patterns and plans, not intuition.

Start from query patterns#

List important queries: filters, sorting, pagination, joins, and detail lookups. Blog posts usually need indexes for slug lookups, publish dates, and category filters.

CREATE UNIQUE INDEX idx_posts_slug ON posts(slug);
CREATE INDEX idx_posts_published_at ON posts(published_at DESC);
CREATE INDEX idx_posts_category ON posts(category);

Composite indexes#

If a query commonly filters by category and sorts by published date, a composite index may help. Column order matters because PostgreSQL can use the leftmost parts of an index most effectively.

CREATE INDEX idx_posts_category_published
ON posts(category, published_at DESC);

EXPLAIN#

EXPLAIN shows the planned query strategy. EXPLAIN ANALYZE executes the query and reports actual timing, so use it carefully with expensive or write queries.

EXPLAIN ANALYZE
SELECT id, title, slug, published_at
FROM posts
WHERE category = 'DevOps'
  AND published_at IS NOT NULL
ORDER BY published_at DESC
LIMIT 10;

Constraints protect correctness#

Use unique, foreign key, check, and not-null constraints for important invariants. Application validation improves UX; database constraints protect data when scripts, jobs, or bugs write data.

Primary references#

• PostgreSQL Docs: Indexes
• PostgreSQL Docs: Using EXPLAIN
• PostgreSQL Docs: Constraints