| name | postgres-engineering |
| description | PostgreSQL SQL engineering and performance: EXPLAIN plans, indexing, statistics, joins, partitioning, schema design, constraints, JSONB, full text, GIN/GiST/BRIN/B-tree indexes, pgvector, extensions, and query tuning. |
PostgreSQL Engineering
Use plans and workload evidence before changing schema or indexes. Explain both read performance and write amplification.
How To Approach A Tuning Request
- Get the real plan:
EXPLAIN (ANALYZE, BUFFERS) with production-like data and parameters. Estimated plans alone mislead.
- Compare estimated vs actual rows at each node. A large gap (10x+) is a statistics problem before it is an index problem.
- Find where the time actually goes: the node with the largest exclusive actual time, loops included (
actual time × loops).
- Check whether the predicate is SARGable — a function or cast on the column side defeats plain indexes.
- Propose the smallest change that fixes the plan: statistics refresh, extended statistics, an index, a query rewrite, then schema change last.
- State the write cost of any new index and check for overlap with existing indexes first (
scripts/04-duplicate-indexes.sql).
Plan Red Flags
| Signal in plan | Likely cause | First response |
|---|
| Estimated rows off by 10x+ | Stale stats, correlated columns, skew | ANALYZE; extended statistics for column combinations |
| Seq scan on large table with selective filter | Missing/unusable index, non-SARGable predicate | Check predicate form and available indexes |
| Nested Loop with huge inner loops count | Misestimate upstream chose the wrong join | Fix the estimate; the join strategy follows |
Sort/Hash spills (Disk: in ANALYZE output) | work_mem too small for this node | Query rewrite, index providing order, or targeted work_mem |
Rows Removed by Filter very high | Index doesn't cover the filter | Composite or partial index matching the predicate |
Heap Fetches high on Index Only Scan | Visibility map not maintained | Vacuum more aggressively on that table |
lossy bitmap heap blocks | work_mem too small for exact bitmap | Raise work_mem or make the predicate more selective |
Index Type Selection
| Index type | Use for | Avoid when |
|---|
| B-tree | Equality, range, ordering, uniqueness (default) | Predicate isn't leading-column-prefixed |
| GIN | JSONB containment, arrays, full text, trigram search | Heavy single-row update churn (write amplification) |
| GiST | Ranges, geometric, exclusion constraints, nearest-neighbor | Plain equality (B-tree is smaller/faster) |
| BRIN | Huge append-only tables with physical ordering (time series) | Data physically unordered on the indexed column |
| Hash | Equality only, large keys | Almost always — B-tree usually matches it |
| HNSW/IVFFlat (pgvector) | Vector similarity | Exact results required (they are approximate) |
Composite index column order: equality columns first, then the range/sort column. A partial index (WHERE status = 'pending') beats a full index when queries always filter the same hot subset. Expression indexes (ON lower(email)) make non-SARGable predicates indexable — the query must use the identical expression.
Common Pitfalls
- Adding an index per slow query without checking overlap; every index taxes writes, vacuum, and cache.
- Tuning to an estimated plan when actual row counts diverge wildly.
- Indexing a JSONB column with B-tree and expecting
@> containment to use it (that needs GIN).
- Ignoring
Rows Removed by Join Filter and join order when the real fix is extended statistics.
- Partitioning small tables for "performance"; partitioning pays off for pruning, retention (drop partition), and vacuum parallelism on genuinely large tables.
- Forgetting that partitioned tables need the partition key in every unique constraint.
- Using
NOT IN (subquery) with nullable columns — both wrong and unoptimizable; use NOT EXISTS.
- Marking functions used in indexes or generated columns as
IMMUTABLE when they are not.
References
references/query-indexing.md - plan reading, index recipes, statistics, partitioning, and JSONB indexing.../postgres/references/diagnostics.md - imported domain-expert plan analysis, EXPLAIN ANALYZE, and diagnostics material.../postgres/references/versions/ - version-specific SQL and optimizer features.
Scripts
scripts/01-index-usage.sql - index usage and size.scripts/02-missing-index-signals.sql - sequential scan pressure signals.scripts/03-table-statistics.sql - table statistics freshness.scripts/04-duplicate-indexes.sql - structurally duplicate indexes.scripts/05-foreign-key-index-check.sql - foreign keys without matching leading-column indexes.scripts/06-partition-inventory.sql - partitioned tables, partition children, and bounds.scripts/07-extended-statistics.sql - extended statistics definitions and collected data.scripts/08-function-volatility.sql - function volatility, parallel safety, and security-definer posture.
Cross-Skill Routing
- Identifying which query is slow goes to
postgres-monitoring; this skill fixes the query once known.
work_mem, parallel workers, and JIT server settings go to postgres-infrastructure.- Bloat and vacuum effects on plans go to
postgres-operations.
- Extension availability on managed platforms goes to
postgres-cloud.
