PostgreSQL Slow Queries: Find and Optimize (2026)

To find and optimize slow queries in PostgreSQL, enable the pg_stat_statements extension to identify execution times and use EXPLAIN ANALYZE to read the execution plan. Add missing indexes with CREATE INDEX CONCURRENTLY and keep statistics updated with VACUUM ANALYZE.

|—|—|—|—|

When I analyzed the database of a public healthcare organization with 2,000 workstations, internal APIs took 4 seconds to respond. Healthcare operators waited minutes to load a single clinical record. The culprit? Five queries without indexes running sequential scans on 5-million-row tables. After applying the techniques in this guide, response times dropped to 200 ms. 40% of performance issues in relational databases stem from unoptimized queries and missing indexes (Gartner 2025). Here is how to fix them at the root.

Prerequisites / Test Environment

A PostgreSQL 15+ instance. Superuser access to enable extensions and modify postgresql.conf. A database with enough data to make performance issues visible. A table with at least 100,000 rows is a good starting point. Testing on a staging environment is mandatory before applying changes to production.

The Problem: PostgreSQL Slow Queries That Work in Dev and Die in Production

The development environment has 100 test rows. Production has 5 million rows and 50 concurrent users. A query that takes 2 milliseconds in dev can require 30 seconds in production. The database grows. Queries degrade silently. Nobody notices until users report slowness. The problem worsens because the PostgreSQL optimizer bases its decisions on statistics. If statistics are stale, PostgreSQL picks the wrong plan.

pg_stat_statements — Enabling the Slow Query Log

To find slow queries, you must first log them. pg_stat_statements is the official PostgreSQL extension. It tracks every query executed on the database and records its execution times.

Enable the extension in postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

Set a threshold to log queries exceeding a certain time. To log all queries taking more than 1 second:

log_min_duration_statement = 1000

Restart PostgreSQL. Create the extension in the database:

CREATE EXTENSION pg_stat_statements;

Now you can find the 10 slowest queries by average execution time:

SELECT query, calls, total_exec_time/calls AS avg_ms, rows/calls AS avg_rows
FROM pg_stat_statements ORDER BY avg_ms DESC LIMIT 10;

This command returns the average time in milliseconds. Look for queries with a high number of calls and a high avg_ms. Those are your culprits.

EXPLAIN and EXPLAIN ANALYZE — Reading the Execution Plan

You found the slow query. Now you need to understand why it is slow. EXPLAIN shows the plan estimated by PostgreSQL. EXPLAIN ANALYZE executes the query and shows the actual plan with real times.

Run the command on your problematic query:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT * FROM orders WHERE customer_id = 123;

Read the output from the bottom up. Look for lines with a high actual time. If you see Seq Scan, PostgreSQL is reading the entire table row by row. This is the most expensive operation. Also look for Filter. This means PostgreSQL reads more rows than necessary and discards them afterward. The Rows Removed by Filter line tells you how many rows it read uselessly.

Be careful with EXPLAIN ANALYZE. It executes the query. If you are analyzing an UPDATE or a DELETE, wrap it in a transaction and roll it back to avoid modifying data.

Missing Indexes: How to Find and Create Them

If EXPLAIN ANALYZE shows a Seq Scan on a column used in the WHERE clause, an index is missing. Before creating the index, check if one already exists:

SELECT indexname FROM pg_indexes WHERE tablename = 'orders';

If the index is not there, create it. In production, always use CONCURRENTLY. Create the index without locking writes on the table:

CREATE INDEX CONCURRENTLY idx_orders_customer ON orders(customer_id);

Concurrent creation takes longer, but it causes no downtime. After creating the index, run EXPLAIN ANALYZE again. You should see Index Scan instead of Seq Scan. Execution time will plummet.

Sequential Scan vs Index Scan — When It Is Normal and When It Is a Problem

Not all sequential scans are a problem. If PostgreSQL must read 90% of the rows in a table, a sequential scan is faster than an index scan. Why? Reading the index and then accessing the table requires extra I/O. The optimizer makes this choice autonomously.

A sequential scan is a problem when the query selects few rows. If you search for a specific customer_id in a 5-million-row table, a sequential scan is a disaster. The rule of thumb: if the query returns less than 5-10% of the total rows in the table, you need an index scan. If it returns 90% of the rows, the sequential scan is correct.

Slow JOIN Queries — Optimizing Relationships

Slow JOINs often stem from small tables that multiply rows (cartesian product) or from joins on columns without an index. Check the execution plan. If you see Hash Join or Nested Loop with an actual rows count much higher than the estimated one, statistics are stale.

Ensure the columns used in the JOIN condition are indexed. For frequent JOINs, create a composite index on both columns. A common mistake is joining on columns with different data types. If one is integer and the other is varchar, PostgreSQL cannot use the index and performs an explicit cast, forcing a sequential scan.

VACUUM and ANALYZE — Keeping Statistics Updated

PostgreSQL uses statistics to decide the execution plan. If statistics are stale, decisions are wrong. 68% of developers use EXPLAIN ANALYZE regularly for tuning (State of PostgreSQL Survey 2025), but forget to update statistics first.

ANALYZE collects statistics. VACUUM reclaims space from dead rows. Run both:

VACUUM ANALYZE table_name;

Run this after large data loads, after creating or dropping indexes, or if you see a large discrepancy between estimated rows and actual rows in EXPLAIN ANALYZE. Do not wait for autovacuum. On large tables with many writes, autovacuum might not run often enough.

Common Errors and Troubleshooting

The most frequent error is creating indexes without CONCURRENTLY in production. It locks writes on the table for the entire duration of the creation. If the table has millions of rows, the lock lasts minutes.

Another error is running EXPLAIN without ANALYZE. EXPLAIN only shows an estimate. If statistics are wrong, the estimate is wrong. EXPLAIN ANALYZE shows reality.

If after creating an index PostgreSQL still uses the sequential scan, check statistics. Run VACUUM ANALYZE on the table. The optimizer will not use the index if statistics tell it the sequential scan is faster.

Some postgresql.conf parameters impact query performance directly. shared_buffers defines how much RAM PostgreSQL uses for data. The default value is low. On a dedicated server, set it to 25% of total RAM. work_mem defines memory for sort and hash operations. If queries do ORDER BY or JOIN on large datasets, a low value forces PostgreSQL to write to disk. Increase work_mem with caution. Too much memory allocated for 50 simultaneous connections exhausts RAM. Set effective_cache_size to 75% of RAM. It does not allocate memory, but tells PostgreSQL how much memory the OS has available for caching. This helps the optimizer choose execution plans.

For efficient log analysis, use pgBadger. It generates HTML reports from PostgreSQL logs. It shows the slowest queries, errors, and checkpoints. pgAdmin has a built-in graph for pg_stat_statements. It lets you visualize slow queries without writing SQL. For real-time monitoring, use pg_activity. It is like htop for PostgreSQL. It shows running queries, execution time, and locks. Install it with pip install pg_activity.

Official pg_stat_statements documentation: https://www.postgresql.org/docs/current/pgstatstatements.html

[Read also: Monitor Server Linux Performance: 8 Tools Compared]

Conclusions with Operational Takeaways

To solve PostgreSQL performance issues, follow a method. First, identify slow queries with pg_stat_statements. Then analyze the execution plan with EXPLAIN ANALYZE. Create missing indexes concurrently. Keep statistics updated with VACUUM ANALYZE. Adjust memory parameters in postgresql.conf.

Do not rely on trial and error. Measure times before and after each intervention. An optimized database reduces infrastructure costs and improves user experience.