Deep dive into how partial indexes (WHERE clauses) and expression indexes (functional indexes) interact with query planner statistics, covering real patterns like soft-delete filtering, multi-tenant row isolation, JSONB field indexing, and case-insensitive search — with EXPLAIN ANALYZE output showing the before/after planner decisions and index size reductions
title: "PostgreSQL Partial & Expression Indexes: The Query Optimization Your ORM Is Hiding From You"
published: true
description: "Partial indexes with WHERE clauses and expression indexes on computed values can cut index size by 90% and turn sequential scans into sub-millisecond seeks. Here's how to write them by hand."
tags: postgresql, performance, architecture, api
canonical_url: https://mvpfactory.co/blog/postgresql-partial-expression-indexes
Originally published on EugeneTheEngineer.com. 2026 update: This case study started the PostgreSQL...
Why your queries are slow and how the right index fixes it — B-trees, composite indexes, and the EXPLAIN habit every developer…
Master SQL performance with B-tree indexes, composite indexes, EXPLAIN ANALYZE, N+1 query elimination, CTEs vs subqueries, and…
How B-tree indexes work, reading Postgres EXPLAIN ANALYZE output, composite index column order, and defining indexes correctly in…
Four index strategies, three table sizes, pgbench. The partial index won — but not for the reason I gave when I recommended it,…
A slow query lands in your lap. You run EXPLAIN ANALYZE and get back a wall of indented text that...
