PostgreSQL Query Optimization and Indexing Strategy

Optimize PostgreSQL [VERSION: 14/15/16] database performance for [PROJECT NAME] with the following characteristics:\\\\\\\\n\\\\\\\\n**Database Context:**\\\\\\\\n- Main tables: [LIST WITH RECORD COUNTS]\\\\\\\\n- Write volume: [NUMBER] inserts/updates per hour\\\\\\\\n- Read volume: [NUMBER] queries per second\\\\\\\\n- Total size: [VALUE] GB\\\\\\\\n- Hosting: [RDS/SUPABASE/SELF-HOSTED] with [RAM] memory\\\\\\\\n\\\\\\\\n**Problematic Queries (paste SQL or describe):**\\\\\\\\n1) [QUERY 1 — e.g., product search with multiple filters]\\\\\\\\n2) [QUERY 2 — e.g., aggregate report with JOINs]\\\\\\\\n3) [QUERY 3 — e.g., real-time dashboard with counts]\\\\\\\\n\\\\\\\\n**1) EXPLAIN ANALYZE Analysis:**\\\\\\\\n- For each query, show how to interpret the execution plan\\\\\\\\n- Identify: unnecessary Seq Scans, expensive Nested Loops, inefficient Hash Joins\\\\\\\\n- Estimated cost vs. actual cost\\\\\\\\n- Estimated rows vs. actual rows (variance = outdated statistics)\\\\\\\\n\\\\\\\\n**2) Indexing Strategy:**\\\\\\\\n- B-tree indexes for equality and range filters\\\\\\\\n- GIN indexes for full-text search and arrays/JSONB\\\\\\\\n- Partial indexes (WHERE condition) for frequent queries\\\\\\\\n- Composite indexes (column order matters)\\\\\\\\n- Covering indexes (INCLUDE) to avoid table lookups\\\\\\\\n- Estimated size of each index\\\\\\\\n- Trade-off: write impact vs. read gain\\\\\\\\n\\\\\\\\n**3) Query Refactoring:**\\\\\\\\n- CTEs vs. subqueries (materialization in PG 14+)\\\\\\\\n- Using LATERAL JOIN for correlated queries\\\\\\\\n- Materialized Views for heavy reports (refresh strategy)\\\\\\\\n- Table partitioning for large tables by date\\\\\\\\n- Connection pooling with PgBouncer\\\\\\\\n\\\\\\\\n**4) PostgreSQL Configuration:**\\\\\\\\n- shared_buffers, work_mem, effective_cache_size\\\\\\\\n- max_connections vs. connection pooling\\\\\\\\n- Autovacuum tuning for tables with heavy UPDATE/DELETE\\\\\\\\n- pg_stat_statements for continuous monitoring\\\\\\\\n\\\\\\\\nFor each optimization, estimate the expected performance gain (e.g., from 4.2s to < 200ms).