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.

1. 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).

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

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