01
Index Types
Core
| Type | Operators | When to Use? |
|---|
| B-tree | = < > BETWEEN
IN, LIKE 'abc%' | Default. Equality, range queries, sorting (ORDER BY). |
| Hash | = | Equality only. PG10+ WAL-logged; rarely outperforms B-tree. |
| GIN | @> ? && @@ | JSONB, array, full-text search, pg_trgm (LIKE '%x%'). |
| GiST | && @> <-> | Geometry (PostGIS), ranges, KNN, exclusion constraints. |
| SP-GiST | << >> ~= | Skewed data: IP (inet), quad-tree, prefix search. |
| BRIN | = < > | Physically sorted massive tables (time-series). Tiny footprint. |
| Bloom | = | Extension. Multi-column arbitrary equality combinations. |
Type Selection Examples
-- B-tree (default)
CREATE INDEX idx_orders_created ON orders (created_at);
-- JSONB containment search
CREATE INDEX idx_evt_payload ON events
USING gin (payload jsonb_path_ops);
-- Trigram search for LIKE '%term%'
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_cust_name_trgm ON customers
USING gin (name gin_trgm_ops);
-- 1 billion rows log table: ~MB index footprint
CREATE INDEX idx_logs_ts_brin ON logs
USING brin (ts) WITH (pages_per_range = 64);
-- Range exclusion (booking overlap prevention)
CREATE INDEX idx_booking_period ON bookings
USING gist (period);
Inspect Index Sizes
SELECT indexrelid::regclass AS idx,
pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_index WHERE indrelid = 'orders'::regclass;
BRIN Rule: Only effective if physical disk order correlates with column values. Check: pg_stats.correlation ≈ ±1.
02
EXPLAIN Interpretation Guide
Critical
Production Usage
EXPLAIN (ANALYZE, BUFFERS, SETTINGS, VERBOSE)
SELECT * FROM orders WHERE customer_id = 42;
-- Profile DML without committing real data changes
BEGIN; EXPLAIN ANALYZE DELETE FROM t WHERE ...; ROLLBACK;
| Plan Node | Meaning & Warning Signals |
|---|
| Seq Scan | Full table scan. OK on small tables; on large tables with selective filters, index is missing. |
| Index Scan | Index + heap traversal. Ideal for low row count fetches. |
| Index Only Scan | No heap reads needed. If Heap Fetches is high → run VACUUM. |
| Bitmap Heap Scan | Medium selectivity; multiple indexes joined via BitmapAnd/Or. |
| Nested Loop | Small outer set + indexed inner table. Disaster if outer set is large. |
| Hash Join | Large equality joins. Batches > 1 → work_mem spill to disk. |
| Merge Join | High efficiency when both sides are already sorted (index). |
| Sort / HashAgg | external merge Disk: → spilling to disk, increase work_mem. |
Anatomy of an EXPLAIN Output
Index Scan using idx_orders_cust on orders
(cost=0.43..8.45 rows=5 width=64)
(actual time=0.021..0.030 rows=48210 loops=1)
Index Cond: (customer_id = 42)
Rows Removed by Filter: 0
Buffers: shared hit=1204 read=3310
Planning Time: 0.112 ms
Execution Time: 41.733 ms
| Signal | Diagnosis |
|---|
| Estimated rows ≠ actual | 10× deviation → stale table statistics: run ANALYZE |
| Rows Removed by Filter | High count indicates filtered column is missing from index |
| shared read >> hit | Data is not in cache, disk I/O bottleneck dominant |
| loops=N | Execution time × loops = true cumulative node cost |
✓ Best Practice: Export plan with FORMAT JSON and visualize via explain.dalibo.com or explain.depesz.com. PG17 added SERIALIZE and MEMORY telemetry.
03
Index Construction Strategies
| Technique | Application |
|---|
| Composite | Place equality columns first, range column last. |
| Partial | WHERE filters subset — compact, fast index. |
| Expression | Index expressions like lower(email). |
| Covering | INCLUDE (PG11+) enables Index Only Scan. |
| CONCURRENTLY | Build index without table write lock (prod safe). |
Composite — Column Ordering
-- WHERE status = 'paid' AND created_at > now()-'7d'
CREATE INDEX idx_o_status_created
ON orders (status, created_at); -- ✓ Equality first → Range last
Partial & Expression Indexes
-- Only active records (top 3% table rows)
CREATE INDEX idx_o_pending ON orders (created_at)
WHERE status = 'pending';
-- Unique constraint for soft-deleted tables
CREATE UNIQUE INDEX uq_users_email ON users (lower(email))
WHERE deleted_at IS NULL;
-- Query must match indexed expression EXACTLY:
SELECT * FROM users WHERE lower(email) = 'alex@acme.com'
AND deleted_at IS NULL;
Covering Index (INCLUDE)
CREATE INDEX idx_o_cust_cov ON orders (customer_id)
INCLUDE (total, status);
-- SELECT total, status ... WHERE customer_id = $1
-- → Executes pure Index Only Scan without heap fetch
Zero-Downtime Safe Creation
SET maintenance_work_mem = '2GB';
CREATE INDEX CONCURRENTLY idx_o_cust ON orders (customer_id);
-- Monitor build progress in real-time (PG12+)
SELECT phase, blocks_done, blocks_total
FROM pg_stat_progress_create_index;
⚠ Caution: CONCURRENTLY cannot run inside an explicit transaction block. If cancelled, it leaves behind an INVALID index that degrades writes: drop using DROP INDEX CONCURRENTLY.
PostgreSQL Indexing & Query Optimization · Planner, Statistics & Anti-Patterns
04
Planner & Cost Parameters
| Parameter | Default | Recommended |
|---|
| random_page_costNon-sequential page fetch cost. Lower on SSDs → favors index. | 4.0 | 1.1 (SSD) |
| effective_cache_sizeOS + shared_buffers cache heuristic. Allocates no RAM. | 4GB | RAM × 0.75 |
| work_memPer sort/hash operation memory. Multiplies by connections! | 4MB | 16–64MB |
| hash_mem_multiplierMultiplier for hash joins relative to work_mem. | 2.0 | 2.0–4.0 |
| effective_io_concurrencyBitmap Heap Scan prefetch depth for storage. | 1 | 200 (SSD) |
| default_statistics_targetHistogram / MCV sample granularity. | 100 | 100–500 |
| jitAdds compile overhead to short OLTP statements. | on | off (OLTP) |
| max_parallel_workers_per_gatherParallel workers allowed per query node. | 2 | 2–4 |
| plan_cache_modePrepared statement choice: generic vs custom plan. | auto | auto |
postgresql.conf (64 GB RAM · SSD · OLTP)
shared_buffers = 16GB
effective_cache_size = 48GB
work_mem = 32MB
maintenance_work_mem = 2GB
random_page_cost = 1.1
effective_io_concurrency = 200
default_statistics_target = 200
jit = off
Session-Level Diagnostics
-- Why is the planner avoiding the index? Force & compare
SET enable_seqscan = off;
EXPLAIN ANALYZE SELECT ...;
RESET enable_seqscan;
-- Grant generous memory for a single heavy reporting query
SET LOCAL work_mem = '512MB'; -- Active only within this TX
⚠ Caution: enable_* switches are strictly diagnostic tools — never disable them globally in production. Bumping global work_mem can cause OOM; tune per role instead: ALTER ROLE report SET work_mem='256MB'.
05
Statistics & ANALYZE
| pg_stats Column | Significance |
|---|
| null_frac | Ratio of NULL values in column |
| n_distinct | Estimated distinct count (negative = table ratio) |
| most_common_vals | Most common values (MCV) and their frequencies |
| histogram_bounds | Quantile boundaries for range query estimations |
| correlation | Physical disk alignment (±1 → BRIN / cheap Index Scan) |
Inspect Column Statistics
SELECT attname, null_frac, n_distinct, correlation,
most_common_vals, most_common_freqs
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';
Per-Column Sample Precision
-- Increase sample size for heavily skewed column
ALTER TABLE orders ALTER COLUMN customer_id
SET STATISTICS 1000;
ANALYZE orders;
Extended Statistics — Correlated Columns
-- city and zip_code are correlated → standard planner
-- assumes independence and drastically underestimates rows
CREATE STATISTICS st_addr (dependencies, ndistinct, mcv)
ON city, zip_code FROM addresses;
ANALYZE addresses;
SELECT statistics_name, dependencies
FROM pg_stats_ext WHERE tablename = 'addresses';
Last ANALYZE & Modified Rows
SELECT relname, n_mod_since_analyze,
last_analyze, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_mod_since_analyze DESC LIMIT 10;
-- High-write table: more aggressive autoanalyze
ALTER TABLE orders SET (
autovacuum_analyze_scale_factor = 0.02,
autovacuum_analyze_threshold = 1000);
⚠ Caution: Following pg_upgrade or massive bulk loads, stats are missing. Always execute vacuumdb --all --analyze-in-stages immediately to avoid disastrous query plan collapses.
Temp Tables: Temporary tables are never vacuumed or analyzed by autovacuum — run ANALYZE manually after populating.
06
Query Anti-Patterns & Fixes
Traps
| ✗ Bad Practice | ✓ High Performance |
|---|
| WHERE date(created_at) = '2026-10-13' | WHERE created_at >= '2026-10-13'
AND created_at < '2026-10-14' |
WHERE lower(email) = $1
(index on plain email) | Expression index:
(lower(email)) |
WHERE phone = 5551234
(phone text → implicit cast) | WHERE phone = '5551234' |
| WHERE name LIKE '%alex%' | pg_trgm GIN index |
| WHERE a = 1 OR b = 2 | Discrete indexes (BitmapOr)
or UNION ALL |
| WHERE id NOT IN (SELECT ...) | WHERE NOT EXISTS (...) |
| SELECT * ... | Select strictly required columns |
| COUNT(*) > 0 check | EXISTS (SELECT 1 ...) |
Keyset Pagination vs High OFFSET
-- ✗ Page 5000: 100,000 rows scanned & discarded
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 100000;
-- ✓ O(1) constant seek time using composite index
SELECT id, total FROM orders
WHERE (created_at, id) < (:last_ts, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;
Batch Ingestion vs N+1 Loop
-- ✗ 1,000 queries in app loop: WHERE id = $1
-- ✓ Single network roundtrip:
SELECT * FROM products WHERE id = ANY($1::bigint[]);
CTE & Materialization (PG12+)
-- PG12+: CTE is inlined by default for optimization
WITH recent AS NOT MATERIALIZED (
SELECT * FROM orders WHERE created_at > now() - '1 day'::interval
)
SELECT * FROM recent WHERE customer_id = 42;
⚠ Caution: If a NOT IN subquery evaluates even a single NULL, the entire expression evaluates to empty. Always use NOT EXISTS for correctness and performance.
PostgreSQL Indexing & Query Optimization · Maintenance, Triage & Checklist
07
Index Maintenance & Diagnostics
Unused Indexes
SELECT s.schemaname, s.relname AS table, s.indexrelname AS idx,
pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_stat_user_indexes s
JOIN pg_index i USING (indexrelid)
WHERE s.idx_scan = 0
AND NOT i.indisunique AND NOT i.indisprimary
ORDER BY pg_relation_size(s.indexrelid) DESC;
Duplicate & Redundant Indexes
SELECT indrelid::regclass AS table,
array_agg(indexrelid::regclass) AS duplicates
FROM pg_index
GROUP BY indrelid, indkey::text, indclass::text,
coalesce(indexprs::text, ''), coalesce(indpred::text, '')
HAVING count(*) > 1;
Corrupt / INVALID Indexes
SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;
Bloat Remediation — REINDEX
-- PG12+: Rebuild online without blocking concurrent writes
REINDEX INDEX CONCURRENTLY idx_orders_created;
REINDEX TABLE CONCURRENTLY orders;
-- Estimate b-tree bloat (pgstattuple extension)
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT avg_leaf_density, leaf_fragmentation
FROM pgstatindex('idx_orders_created');
-- avg_leaf_density < 50 → Primary candidate for REINDEX
Top Heavy Queries (pg_stat_statements)
SELECT round(total_exec_time::numeric, 0) AS total_ms,
calls, round(mean_exec_time::numeric, 2) AS mean_ms,
rows, shared_blks_read, left(query, 80) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 10;
⚠ Caution: The idx_scan = 0 counter resets whenever statistics are cleared. Before dropping an unused index, inspect read replicas and verify monthly report workloads.
08
Query Optimization Checklist
Slow Query Triage
| Checklist Item | Priority |
|---|
| pg_stat_statements enabled; top 10 heavy statements identifiedORDER BY total_exec_time DESC | Critical |
| EXPLAIN (ANALYZE, BUFFERS) plan profiledestimated rows ≈ actual rows? | Critical |
| Foreign key columns indexedPrevents table locks on ON DELETE CASCADE | Critical |
| Statistics fresh and up to dateCheck pg_stat_user_tables.last_autoanalyze | Critical |
| random_page_cost calibrated for SSDSHOW random_page_cost; -- 1.1 | Important |
| Unused and duplicate indexes purgedCheck pg_stat_user_indexes.idx_scan = 0 | Important |
| Zero INVALID indexes remainingpg_index WHERE NOT indisvalid | Important |
| No sort/hash spills to diskCheck log_temp_files = 0 · temp_blks_written | Important |
| Slow query log & auto_explain activelog_min_duration_statement = 500ms | Recommended |
| Index bloat continuously monitoredpgstatindex() · REINDEX CONCURRENTLY | Recommended |
CriticalFix immediately ImportantThis sprint RecommendedPlanned backlog
Detect Missing Foreign Key Indexes
-- dbaguide.com · missing FK index finder
SELECT c.conrelid::regclass AS table,
c.conname AS fk,
pg_get_constraintdef(c.oid) AS definition
FROM pg_constraint c
WHERE c.contype = 'f'
AND NOT EXISTS (
SELECT 1 FROM pg_index i
WHERE i.indrelid = c.conrelid
AND (i.indkey::int2[])[0:cardinality(c.conkey)-1]
OPERATOR(pg_catalog.@>) c.conkey);
auto_explain — Automated Slow Query Profiler
shared_preload_libraries = 'pg_stat_statements,auto_explain'
auto_explain.log_min_duration = '1s'
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_timing = off # Minimal overhead
auto_explain.sample_rate = 0.1
log_temp_files = 0
✓ Optimization Flow: pg_stat_statements → Top costly query → EXPLAIN (ANALYZE, BUFFERS) → Row misestimate? (run ANALYZE) · Seq scan? (add targeted index) · Disk sort spill? (tune work_mem) → re-measure.