PostgreSQL Query Optimization and Indexing Strategy

Optimize PostgreSQL [VERSION: 14/15/16] database performance for [PROJECT NAME] with the following characteristics:

**Database Context:**
- Main tables: [LIST WITH RECORD COUNTS]
- Write volume: [NUMBER] inserts/updates per hour
- Read volume: [NUMBER] queries per second
- Total size: [VALUE] GB
- Hosting: [RDS/SUPABASE/SELF-HOSTED] with [RAM] memory

**Problematic Queries (paste SQL or describe):**
1) [QUERY 1 — e.g., product search with multiple filters]
2) [QUERY 2 — e.g., aggregate report with JOINs]
3) [QUERY 3 — e.g., real-time dashboard with counts]

**1) EXPLAIN ANALYZE Analysis:**
- For each query, show how to interpret the execution plan
- Identify: unnecessary Seq Scans, expensive Nested Loops, inefficient Hash Joins
- Estimated cost vs. actual cost
- Estimated rows vs. actual rows (variance = outdated statistics)

**2) Indexing Strategy:**
- B-tree indexes for equality and range filters
- GIN indexes for full-text search and arrays/JSONB
- Partial indexes (WHERE condition) for frequent queries
- Composite indexes (column order matters)
- Covering indexes (INCLUDE) to avoid table lookups
- Estimated size of each index
- Trade-off: write impact vs. read gain

**3) Query Refactoring:**
- CTEs vs. subqueries (materialization in PG 14+)
- Using LATERAL JOIN for correlated queries
- Materialized Views for heavy reports (refresh strategy)
- Table partitioning for large tables by date
- Connection pooling with PgBouncer

**4) PostgreSQL Configuration:**
- shared_buffers, work_mem, effective_cache_size
- max_connections vs. connection pooling
- Autovacuum tuning for tables with heavy UPDATE/DELETE
- pg_stat_statements for continuous monitoring

For each optimization, estimate the expected performance gain (e.g., from 4.2s to < 200ms).