Database Indexing and Query Optimization for Java Developers

Introduction Fixing N+1 queries (see the previous post ) gets your Hibernate app down to a handful of queries per request. The next bottleneck is what each of those queries costs once your tables have millions of rows — and that is almost always a question of indexing. An index turns "scan every row" into "look it up directly." Get the index wrong — or skip it — and a query that took 2ms in development takes 4 seconds in production once real data volume shows up. How Indexes Work: The B-Tree Intuition Without an index, a WHERE clause forces a sequential scan : the database reads every row and checks the condition. That's O(n) — cost grows linearly with table size. An index is a separate, sorted data structure (almost always a B-tree ) that maps column values to row locations. Because it's sorted and balanced, finding a value is a tree walk: O(log n) . On a 10-million-row table, that's the difference between reading 10 million rows and reading roughly 23 tree nodes. The cost is not free: Writes get slower. Every INSERT / UPDATE / DELETE on an indexed column must also update the index structure. Storage grows. Each index is a copy of (part of) the data, sorted differently. An index is a trade: you pay on every write so that specific reads become fast. Indexing a column you rarely filter or sort on is pure cost with no benefit. Reading Query Plans: EXPLAIN ANALYZE Postgres' EXPLAIN ANALYZE shows what the planner actually did — not what you hope it did. Before an index , filtering orders by customer_id : EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 48291 ; Seq Scan on orders (cost=0.00..21453.00 rows=42 width=96) (actual time=0.021..118.442 rows=41 loops=1) Filter: (customer_id = 48291) Rows Removed by Filter: 1199959 Planning Time: 0.112 ms Execution Time: 118.471 ms Seq Scan means Postgres read all ~1.2 million rows and threw away all but 41 of them. actual time is the real elapsed time, not an estimate — 118ms for one lookup. After CREATE INDEX idx_orders