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.

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+.

Reviewed by Chris St. John·Last updated Jul 18, 2026

Was this article helpful?

Sql Indexes Access Paths B Tree Indexes

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.

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+.

Reviewed by Chris St. John·Last updated Jul 18, 2026

Was this article helpful?

B-tree Indexes

Search Documentation

B-tree Indexes