How do you optimize queries in PostgreSQL?

Optimizing PostgreSQL queries involves a combination of database design, indexing, query rewriting, and runtime tuning. First, ensure proper normalization and use appropriate data types. Create indexes on columns used in JOIN, WHERE, ORDER BY, and GROUP BY clauses; consider composite indexes for multi‑column predicates. Use the ANALYZE command to update statistics so the planner can pick the best plan. Examine the execution plan with EXPLAIN (ANALYZE) to identify slow steps such as sequential scans, nested loops, or large sort operations. Replace expensive operations with more efficient ones: use EXISTS instead of IN when appropriate, avoid SELECT *, and limit the result set early. For large tables, consider partitioning or sharding to reduce scan size. Materialized views can cache expensive aggregations. Finally, tune configuration parameters like work_mem, shared_buffers, and effective_cache_size to match your workload. Regularly vacuum and vacuum analyze to reclaim space and keep statistics fresh. By iteratively profiling and adjusting these aspects, you can significantly reduce query latency and resource consumption.