B-tree Indexes
Equality and range; multicolumn order rules. Practical PostgreSQL patterns for production queries.
Recipe
CREATE INDEX ON app.orders (status, created_at DESC);When to reach for this: This pattern appears in application or reporting SQL you maintain.
Working Example
EXPLAIN ANALYZE SELECT * FROM app.orders WHERE status = 'open' ORDER BY created_at DESC LIMIT 50;What this demonstrates:
- Runnable SQL against sample schemas
- Idiomatic PostgreSQL syntax
- Patterns safe for EXPLAIN review
Deep Dive
How It Works
- PostgreSQL parses SQL into a query tree.
- The planner estimates costs using statistics.
- Executor returns rows to the client protocol.
Notes
- Prefer readable SQL; optimizers rewrite internally.
- Test with production-like row counts.
Gotchas
- SELECT * in apps - New columns break serializers.. Fix: List columns explicitly.
- NULL comparisons - = NULL is always unknown.. Fix: Use IS NULL or IS DISTINCT FROM.
- Implicit casts - Surprise seq scans on mismatched types.. Fix: Match predicate types to column types.
- Unbounded queries - Missing LIMIT on large tables.. Fix: Paginate or aggregate intentionally.
- Stale statistics - Bad plans after bulk load.. Fix: Run ANALYZE on affected tables.
Alternatives
| Alternative | Use When | Don't Use When |
|---|---|---|
| ORM query builder | Team standardizes on one stack | Complex SQL becomes opaque |
| Materialized view | Repeat expensive aggregates | Needs refresh strategy |
| Warehouse replica | Heavy BI scans | Not for OLTP latency |
FAQs
Should I format SQL in application strings?
Use multiline template strings or SQL files; lint in CI.
When is DISTINCT enough?
When you need unique rows without aggregates.
Does ORDER BY hurt indexes?
Sort can use index order if query matches index columns.
Related
- Indexes Basics - fundamentals
- EXPLAIN Basics - plans
Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.