Query Optimization in PostgreSQL: Real Examples From Real Projects

What you need to know

• EXPLAIN ANALYZE is the diagnostic tool. Run it before and after every optimization to confirm the improvement is real and not coincidental.
• Sequential scans on large tables are almost always the root cause of slow queries. The fix is an index on the filter or sort column.
• N+1 query patterns are invisible in per-query profiling but catastrophic at the application layer. Look at query counts, not just query duration.
• Statistics drift after large data inserts. Run ANALYZE on tables that have changed significantly before debugging query plan issues.
• Index maintenance has a write cost. Do not add indexes speculatively. Each index slows INSERT, UPDATE, and DELETE operations on the table.

The core argument

PostgreSQL query optimization has a repeatable diagnostic process. The frustrating part is that teams skip the diagnostic step and go straight to guessing. They add an index because a blog post said indexes help, or they rewrite the query because the code looks complex, without measuring whether either change actually reduced execution time. The result is a mix of effective and ineffective changes with no understanding of which did what.

The right process is: run EXPLAIN ANALYZE on the slow query, read the output to find the expensive node, understand why that node is expensive (missing index, stale statistics, poor join order), apply the targeted fix, and run EXPLAIN ANALYZE again to confirm the improvement. This takes longer than guessing but produces reliable results. On a project I worked on for Nexli, a query that was taking 4 seconds on a 500K row table dropped to 12 milliseconds after reading the EXPLAIN output and adding a composite index on two filter columns. The guess before reading the output was to add a single-column index, which would have helped but not nearly as much.

The fixes that appear most frequently in my experience across different projects are: missing indexes on foreign keys (an ORM creates the foreign key constraint but not the index, so JOIN operations do sequential scans on the child table), missing indexes on timestamp columns used in date range filters (very common in reporting queries), and N+1 patterns in ORMs that are configured without eager loading. These three account for the majority of slow queries I have diagnosed in production PostgreSQL systems.

Common mistakes

1. Adding indexes without checking if they are used. An index added without confirming that queries use it through EXPLAIN ANALYZE may never be used by the planner, while still adding write overhead. After adding an index, run EXPLAIN ANALYZE on the query that should benefit and verify the plan shows an Index Scan on the new index.

1. Running EXPLAIN without ANALYZE. EXPLAIN without ANALYZE shows the planner's estimated plan based on table statistics, not the actual execution. The estimated plan may differ significantly from what PostgreSQL actually does. Always use EXPLAIN (ANALYZE, BUFFERS) for accurate diagnosis.

1. Optimizing queries that are not actually slow. A query that runs in 5ms is not worth optimizing. Find the actual slow queries through pg_stat_statements or slow query logs, not through reading code and guessing. Optimization effort on fast queries produces no user-visible improvement.

1. Ignoring connection overhead and pool sizing. A query that executes in 1ms but spends 50ms waiting for a connection from an undersized connection pool looks like a slow query in application-level tracing. Check connection pool utilization before diagnosing query performance. The fix may be connection pool sizing, not query optimization.

1. **Using SELECT * in high-frequency queries.** Fetching all columns when only a few are needed transfers more data from PostgreSQL to the application, increases memory allocation, and prevents the planner from using index-only scans. In high-frequency query paths, selecting only the needed columns reduces data transfer and enables covering indexes to serve the query entirely from the index without touching the table.

Where to start

1. Find the actual slow queries. Enable pg_stat_statements if it is not already active. Query it for the slowest queries by total execution time (ORDER BY total_exec_time DESC LIMIT 20). This gives the real list of optimization targets, not guesses. Slow query logs (log_min_duration_statement = 100ms) provide the same information in the application logs.

1. Run EXPLAIN (ANALYZE, BUFFERS) on each slow query. Look for Seq Scan nodes on large tables, large divergence between estimated and actual row counts (statistics issue), and nodes with high actual time. The node with the highest actual time is the optimization target. Hash joins with very large memory usage may indicate a work_mem configuration issue rather than a missing index.

1. Apply the targeted fix and measure the improvement. For sequential scans: add an index on the filter column. For stale statistics: run ANALYZE on the table. For N+1 patterns: add eager loading or rewrite with a JOIN. After each fix, re-run EXPLAIN ANALYZE to confirm the plan changed and the execution time improved. Document the before/after timing so the impact is measurable.

Related reading

• PostgreSQL Performance at Scale: The Tweaks That Move the Needle
• PostgreSQL vs MySQL vs MongoDB for a New SaaS in 2026
• Read Replicas: When They Save You and When They Lie to You
• Observability in 2026: Metrics, Logs, Traces