How to find slow queries in PostgreSQL and how to optimize them?

To find slow queries in PostgreSQL, you can use the following methods:

1. Enable logging of slow queries in the PostgreSQL configuration (postgresql.conf):

   • log_min_duration_statement = 1000 — log queries that run longer than 1000 ms.

2. Use pg_stat_statements — an extension that collects statistics on query execution, including average time.

3. EXPLAIN (ANALYZE) — analyze the execution plan of a specific query with actual timing.

After identifying a slow query, optimization may include:

• Adding or adjusting indexes.
• Rewriting the query for a more efficient plan.
• Using aggregate functions or window functions instead of subqueries.
• Breaking complex queries into simpler ones.
• Analyzing and updating statistics with ANALYZE.

Example of using EXPLAIN ANALYZE:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 123;

This will show how long the query takes and which operations are most costly.

Optimization is an iterative process; it is important to first identify bottlenecks, then apply changes and check their impact.