PostgreSQL Query & Optimization
When to use this skill
-
Debugging slow queries.
-
Designing indexes for new schemas.
-
Analyzing EXPLAIN output.
-
Writing complex CTEs or recursive queries.
- Indexing Strategy
-
B-Tree: Default. Good for equality and range (< , <= , = , etc.).
-
GIN: Essential for jsonb , array , and full-text search (tsvector ).
-
Partial Indexes: Use WHERE clause in index definition to save space (e.g., WHERE is_active = true ).
-
Covering Indexes: Use INCLUDE to store extra payload columns in the index leaf nodes (avoids heap lookup).
- Query Patterns
-
CTEs: Use Common Table Expressions (WITH ) for readability. Note: Postgres >= 12 optimizes them well (materialization boundary is smarter).
-
EXPLAIN: Always run EXPLAIN (ANALYZE, BUFFERS) to see actual execution times and I/O costs.
-
Nulls: Be aware of NOT IN (...) with nulls (can yield unexpected results); prefer NOT EXISTS .
- Performance Pitfalls
-
Seq Scans: Acceptable for small tables; bad for large ones.
-
N+1: Detecting N+1 queries in application layers.
-
Functions in WHERE: Avoid WHERE function(column) = val ; this kills index usage. Use expression indexes if needed.