Optimize PostgreSQL Queries with Execution Plan Analysis and Indexing Strategy
Get expert PostgreSQL query optimization with index recommendations, query rewrites, and execution plan analysis for slow queries.
📝 The Prompt
I have a PostgreSQL [VERSION] database with the following slow query that takes [CURRENT_EXECUTION_TIME] to run:
```sql
[PASTE_SLOW_QUERY]
```
**Table schemas involved:**
```sql
[PASTE_CREATE_TABLE_STATEMENTS]
```
**Current indexes:**
```sql
[PASTE_EXISTING_INDEXES]
```
**Data profile:**
- [TABLE_1]: approximately [ROW_COUNT_1] rows, growing at [GROWTH_RATE_1] per month
- [TABLE_2]: approximately [ROW_COUNT_2] rows
- The most selective filter is [MOST_SELECTIVE_COLUMN] with approximately [CARDINALITY] distinct values
- This query runs [FREQUENCY] (e.g., 500 times/hour, nightly batch)
Please provide a comprehensive optimization analysis:
1. **Query Rewrite**: Suggest an optimized version of the query. Consider:
- Replacing subqueries with JOINs or CTEs where beneficial
- Eliminating unnecessary columns from SELECT
- Restructuring WHERE clauses for sargability
- Using EXISTS instead of IN for correlated subqueries if applicable
- Window functions vs. self-joins
2. **Index Recommendations**: Propose specific CREATE INDEX statements with:
- Composite index column ordering rationale (equality columns first, then range)
- Covering indexes (INCLUDE clause) to enable index-only scans
- Partial indexes if the query filters on a consistent condition
- Estimated size impact of each proposed index
3. **EXPLAIN ANALYZE Guidance**: Tell me exactly what to look for in the execution plan—which nodes indicate problems (Seq Scan on large tables, Nested Loop with high row estimates, Hash Join memory spills).
4. **Configuration Tuning**: Suggest relevant postgresql.conf parameters to review (work_mem, effective_cache_size, random_page_cost) given the query pattern.
5. **Trade-offs**: Note any write-performance impact from proposed indexes and maintenance considerations (VACUUM, bloat).
💡 Tips for Better Results
Always include the actual EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) output if available—it gives the AI concrete data about row estimates vs. actuals and buffer usage. Provide realistic row counts and cardinality because optimal index strategies change dramatically at different data scales. Test proposed indexes on a staging environment with production-like data volumes before deploying to production.
🎯 Use Cases
Database administrators and backend developers use this when diagnosing and resolving slow PostgreSQL queries, especially when they need both immediate query fixes and long-term indexing strategies for growing datasets.