To find slow PostgreSQL queries, enable pg_stat_statements, rank statements by total execution time, and inspect promising candidates with EXPLAIN. Use EXPLAIN ANALYZE only when it is safe to execute the statement. Total time helps prioritize capacity work; individual request latency and SLO violations still matter. Investigate scans, sorts, estimates, and waits before choosing an index or rewrite.
The examples use PostgreSQL 16โ17 syntax. Validate permissions, extension versions, and configuration in your environment. These are examples, not commands executed against your database. EXPLAIN ANALYZE executes the statement; test writes in staging first. A rollback does not undo every possible side effect, including sequence advancement or external calls.
Key takeaways
- Use total execution time for capacity priorities and latency SLOs for user-facing regressions.
- pg_stat_statements stores aggregated statistics, not execution plans.
- EXPLAIN ANALYZE executes SQL; validate potentially disruptive work in staging.
- Measure comparable baseline and post-change windows, retaining the full statement identity.
Enable pg_stat_statements
Add pg_stat_statements to the existing shared_preload_libraries list, preserving other libraries. Restart when changing the preload configuration, then create the extension in each database you inspect. Since PostgreSQL 14, compute_query_id defaults to auto, which lets a loaded statistics module enable query identifiers.
# postgresql.conf for PostgreSQL 16 and 17
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = allRestart the server, then create the extension. The statistics collector is shared across the instance, but the view exists only in databases where you enable the extension.
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SHOW shared_preload_libraries;
SHOW compute_query_id;If you need a monitoring role to read query text from other sessions, grant pg_read_all_stats. Without it, you may only see your own statements or redacted text.
GRANT pg_read_all_stats TO my_monitoring_role;Managed-service setup matrix
| Provider | Parameter or flag | Static or dynamic | Reboot needed | Where to set it |
|---|---|---|---|---|
| AWS RDS / Aurora PostgreSQL | shared_preload_libraries, pg_stat_statements.max, pg_stat_statements.track | shared_preload_libraries is static; the two pg_stat_statements entries are exposed separately in the parameter group | Yes for preload changes | Custom DB parameter group |
| Google Cloud SQL for PostgreSQL | CREATE EXTENSION pg_stat_statements; supported pg_stat_statements.* flags | Depends on the flag | For changes such as pg_stat_statements.max | SQL extension setup and Cloud SQL instance flags |
| Azure Database for PostgreSQL Flexible Server | azure.extensions allowlist plus shared_preload_libraries | Allowlist and preload are server settings | Yes when preload changes | Server parameters on the flexible server |
Use the providerโs current instructions: Cloud SQL extensions and supported flags distinguish SQL extension creation from instance configuration. RDS parameter groups and Azure extension allowlists have their own controls; check restart requirements before changing production settings.
Rank the queries that actually cost you
Sort by total execution time to identify aggregate load. Filter to the current database and top-level statements. This excludes statements nested inside functions or procedures when track = all; it does not remove separate queries issued by an ORM.
SELECT
queryid,
calls,
round(total_exec_time::numeric, 1) AS total_ms,
round(mean_exec_time::numeric, 2) AS mean_ms,
rows,
round((100.0 * total_exec_time
/ nullif(sum(total_exec_time) OVER (), 0))::numeric, 2) AS pct_db_time,
left(query, 120) AS query
FROM pg_stat_statements
WHERE toplevel
AND dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
ORDER BY total_exec_time DESC
LIMIT 20;Statement entries can be evicted when the configured capacity is reached. Inspect pg_stat_statements_info.dealloc, statistics resets, and collection continuity before comparing snapshots. An absent or reset entry is not evidence that its workload vanished. Increase capacity when needed, accounting for memory and restart requirements.
Read the output in two passes. Isolate the few statements that own most of the time. Then compare rows to calls: rows per call near 1 with exploding calls usually means a loop in application code, not a missing index.
How this compares to the slow query log
The slow-query log records individual executions above a threshold, while pg_stat_statements aggregates normalized statement statistics. SQL logs can expose query text and parameter values. Set parameter logging, retention, and access controls deliberately; avoid enabling verbose parameter capture just to populate a dashboard.
The split is simple: rank with pg_stat_statements, then reach for the slow query log when you need the bind parameters of one bad execution. A query that runs ten thousand times at 12 ms never appears in a log set to one second, and it usually owns the instance.
Why mean time misleads
Mean latency answers a different question: how slow was one execution. Capacity pain is cumulative. If statement A averages 2,500 ms and runs 8 times, it burns 20 seconds. If statement B averages 18 ms and runs 9,000 times, it burns 162 seconds. Fixing B moves the system. That is why pg_stat_statements is the first tool, not the last one you open after an incident.
Read EXPLAIN ANALYZE without fooling yourself
Once you pick a statement, capture the plan with execution timing. For inherited systems, you care about where time is spent and whether the chosen path made sense at plan time. Focus on the scan node, sort node, buffer reads, and estimate accuracy. If estimates are close but cost still pushed the wrong path, fix the access path or sort work. If row estimates are wrong, inspect statistics and predicates before you add indexes.
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT id, customer_id, created_at, total_cents
FROM orders
WHERE customer_id = 424242
ORDER BY created_at DESC
LIMIT 50;Healthy versus neglected plan
The following illustrative plan output contrasts a sequential scan plus sort with a matching composite index. Its timings are examples, not a measured benchmark or a promised improvement. The change in access path is what matters; verify actual plans and buffer activity on representative data.
-- Before the index: sequential scan plus top-N sort
Limit (cost=41288.15..41288.28 rows=50 width=28) (actual time=612.884..612.902 rows=50 loops=1)
Buffers: shared hit=2841 read=2087
-> Sort (cost=41288.15..41306.60 rows=7380 width=28) (actual time=612.882..612.893 rows=50 loops=1)
Sort Key: orders.created_at DESC
Sort Method: top-N heapsort Memory: 31kB
Buffers: shared hit=2841 read=2087
-> Seq Scan on public.orders (cost=0.00..41002.99 rows=7380 width=28) (actual time=0.041..599.612 rows=7412 loops=1)
Filter: (customer_id = 424242)
Rows Removed by Filter: 492588
Buffers: shared hit=2841 read=2087
Planning Time: 0.412 ms
Execution Time: 613.055 ms
-- After: CREATE INDEX CONCURRENTLY orders_customer_id_created_at_idx
-- ON public.orders (customer_id, created_at DESC);
Limit (cost=0.43..24.07 rows=50 width=28) (actual time=0.052..0.271 rows=50 loops=1)
Buffers: shared hit=56
-> Index Scan using orders_customer_id_created_at_idx on public.orders
(cost=0.43..3521.06 rows=7380 width=28) (actual time=0.050..0.265 rows=50 loops=1)
Index Cond: (customer_id = 424242)
Buffers: shared hit=56
Planning Time: 0.311 ms
Execution Time: 0.331 msThe arithmetic adds up. Before the index, about 600 ms goes to reading the heap and discarding roughly 492,000 filtered rows, then about 13 ms goes to the top-N sort and the limit. After the index, the executor walks 50 matching keys in index order and stops, so the scan node never touches the other half million rows. The planner had a reasonable cardinality estimate in both cases. What changed was the available path and its cost.
Decide whether an index is the fix
Indexes help when the reduced read work justifies storage and write overhead. There is no universal selectivity percentage at which PostgreSQL must choose an index. Table size, caching, correlation, row width, index coverage, ordering, and cost settings all influence the plan. Measure the actual workload.
| Situation | Likely fix | Why |
|---|---|---|
WHERE customer_id = ? ORDER BY created_at DESC LIMIT 50 | Composite index on (customer_id, created_at DESC) | Filters and returns rows in the needed order |
| Predicate matches a large share of the table | Keep the scan, reduce rows earlier, or partition | An index can add random I/O without reducing enough work |
| Plan mis-estimates rows badly | Refresh stats, inspect correlation, review predicate shape | Bad estimates can make a good index look unattractive |
| Lookup loop with huge call counts | Fix the application query shape | N+1 problems live above the database |
Avoid redundant indexes unless they serve a measured need. Low-cardinality columns are not automatically bad candidates: a selective partial index or a useful composite key can help. Check constraints and workload coverage before removing an apparently unused index.
Create the index safely and verify the result
Before you build anything, list what the table already has. Inherited schemas carry duplicate prefixes and indexes nobody reads.
SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY pg_relation_size(indexrelid) DESC;If the plan and predicate both point to a composite index, build it concurrently. It does not block reads and writes the way a plain CREATE INDEX does, but it waits for every transaction that can see the table and scans the table more than once, so on a busy instance it can run for a long time.
CREATE INDEX CONCURRENTLY orders_customer_id_created_at_idx
ON public.orders (customer_id, created_at DESC);CREATE INDEX CONCURRENTLY cannot run inside a transaction block. It takes SHARE UPDATE EXCLUSIVE locks during its build phases; these permit normal reads and writes but conflict with some DDL and maintenance operations. Long transactions can delay progress. Concurrent builds perform extra work and can leave invalid indexes after failure, so monitor progress and inspect validity before retrying.
SELECT indexrelid::regclass AS index_name, indisvalid
FROM pg_index
WHERE NOT indisvalid;If you find an invalid leftover for the same index, drop it concurrently and rebuild.
DROP INDEX CONCURRENTLY IF EXISTS public.orders_customer_id_created_at_idx;
CREATE INDEX CONCURRENTLY orders_customer_id_created_at_idx
ON public.orders (customer_id, created_at DESC);After the build, rerun the same EXPLAIN (ANALYZE, BUFFERS, VERBOSE). You want the index scan path shown earlier. Then take fresh snapshots to verify the gain at statement level, not in one happy-path run.
Compare representative before-and-after windows
Two snapshots measure one interval, not a before-and-after improvement. Run the following measurement for a representative baseline window, save the results, apply the reviewed change, and repeat for a comparable post-change window. Keep traffic mix, duration, and cache conditions comparable. Each run uses a fresh session and temporary tables.
-- Run in a fresh psql session. Tables are local to this session.
CREATE TEMP TABLE pss_window_start AS
SELECT userid, dbid, queryid, toplevel, calls, total_exec_time
FROM pg_stat_statements
WHERE toplevel
AND dbid = (SELECT oid FROM pg_database WHERE datname = current_database());
CREATE TEMP TABLE pss_window_info AS
SELECT stats_reset, dealloc FROM pg_stat_statements_info;
-- Allow one representative workload interval to pass, then run:
SELECT s.userid, s.dbid, s.queryid,
s.calls - b.calls AS window_calls,
round((s.total_exec_time - b.total_exec_time)::numeric, 1) AS window_ms,
round(((s.total_exec_time - b.total_exec_time)
/ NULLIF(s.calls - b.calls, 0))::numeric, 2) AS mean_ms,
left(s.query, 120) AS query
FROM pg_stat_statements s
JOIN pss_window_start b USING (userid, dbid, queryid, toplevel)
CROSS JOIN pss_window_info old_info
CROSS JOIN pg_stat_statements_info now_info
WHERE s.calls > b.calls
AND s.total_exec_time >= b.total_exec_time
AND now_info.stats_reset = old_info.stats_reset
AND now_info.dealloc = old_info.dealloc
ORDER BY window_ms DESC
LIMIT 20;
-- Save this result, then close the session.
-- Repeat in a NEW session for the post-change comparison window.A changed reset timestamp or eviction count invalidates this simple interval comparison. Targeted resets, missing entries, new query identifiers, and concurrent administrative actions also require investigation. A rewrite may change queryid, so compare the equivalent application operation rather than expecting a join to identify it automatically.
Scope auto_explain so it helps instead of flooding logs
auto_explain is useful when the slow statement is hard to catch interactively, but scope matters. Only the library name belongs in shared_preload_libraries. The logging controls are separate GUCs. If you add the library to shared_preload_libraries, you need a restart before any session can use it.
# postgresql.conf for PostgreSQL 16 and 17
shared_preload_libraries = 'pg_stat_statements,auto_explain'
auto_explain.log_min_duration = '250ms'
auto_explain.log_analyze = on
auto_explain.log_timing = off
auto_explain.log_buffers = on
auto_explain.sample_rate = 0.05
log_parameter_max_length = 0These are example settings, not a blanket production recommendation. log_analyze adds instrumentation overhead even when only some plans are logged. Disabling per-node timing reduces part of that overhead. Test sampling and thresholds, and treat logged SQL as sensitive operational data.
Session scope
If you only want this for one new connection, use session_preload_libraries and set the auto_explain GUCs in that session. This works for new sessions only. A connection pool will not reliably preserve a manual LOAD plus SET sequence across reused backends, so do not treat that as a durable production setup.
Shared scope
If multiple app sessions need plans in logs, keep the library in shared_preload_libraries and tune the logging GUCs normally. Preload changes need a restart, so treat this as a controlled maintenance change. Start with a nonzero threshold and a low sample rate.
Recognize N+1 patterns before you index the wrong thing
From the database side, N+1 has a clear fingerprint: one normalized child query, one queryid, very high calls, low rows per call, and a lookup shape such as WHERE id = $1 or WHERE customer_id = $1. Because pg_stat_statements normalizes literals, the repeated child lookup collapses into one row. That row is the clue.
SELECT
queryid,
calls,
round(mean_exec_time::numeric, 3) AS mean_ms,
round((rows::numeric / nullif(calls, 0)), 3) AS rows_per_call,
left(query, 120) AS query
FROM pg_stat_statements
WHERE toplevel
AND dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
ORDER BY calls DESC
LIMIT 10;Reproduce suspicious request patterns in staging. Calling pg_stat_statements_reset() without arguments resets instance-wide statistics. It requires superuser privileges or an explicit grant on the function; pg_read_all_stats alone does not grant reset rights. Prefer scoped snapshots in production and coordinate any reset with monitoring owners.
SELECT pg_stat_statements_reset();
-- Load one page or run one request path in staging.
SELECT
queryid,
calls,
round(mean_exec_time::numeric, 3) AS mean_ms,
rows,
left(query, 120) AS query
FROM pg_stat_statements
WHERE toplevel
AND dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
ORDER BY calls DESC
LIMIT 5;The fix usually lives in the application. Batch the child lookup, join the needed relation once, or eager-load the association in your ORM. For example, if your API fetches 100 orders and then loads each customer in a loop, replace that loop with one query that joins customers or one batched WHERE customer_id = ANY($1) lookup. An extra index on the child table often makes the symptom cheaper without removing the waste.
How Cast AI turns query evidence into recommendations

pg_stat_statements tells you which SQL statements consume time. It does not tell you whether the database host or the Kubernetes worker beneath it is starved, oversized, or fighting noisy neighbors. That is the gap where infrastructure context matters.
Cast AI Database Optimization provides a read-only Performance Advisor for supported PostgreSQL and MySQL workloads. It prioritizes index and SQL rewrite recommendations and shows before-and-after plan analysis with projected impact. You review, test, and apply the proposed changes; the advisor does not change your schema. Optional proxy pooling and caching are separate capabilities.
Use projected impact to prioritize investigation, then verify outcomes with representative application traffic. For the broader workflow, see database optimization techniques, database observability, and the database optimization tools comparison. To assess your own workload, start a free trial with read-only database analysis.
Conclusion: the repeatable loop for inherited databases
The loop is simple. Enable pg_stat_statements. Rank statements by total time. Read the plan for touched data, sort work, and estimate quality. Apply the smallest fix that changes the path or the query shape. Then run the two-snapshot delta query again and confirm the per-call number moved in the right direction. That is how you turn a slow inherited database into a tractable queue instead of a mystery.
FAQ
How do I find slow queries in PostgreSQL?
Enable pg_stat_statements, then rank statements by total_exec_time rather than mean_exec_time. Open the top entries with EXPLAIN (ANALYZE, BUFFERS) and compare estimated rows to actual rows. A fast query with enormous call volume usually costs more than the single slow report you noticed.
Does pg_stat_statements slow down the database?
It adds overhead that depends on workload and enabled settings. Measure it on your system, especially when enabling planning or I/O timing. Plan instrumentation through auto_explain is separate and can add further overhead.
Pg_stat_statements vs slow query log?
Use both. log_min_duration_statement records individual executions above a threshold, which is what you want when one query occasionally takes 30 seconds and you need its parameters. pg_stat_statements aggregates into one row per normalized query, which answers cumulative load. The log stays silent about ten thousand fast queries.
How do I read EXPLAIN ANALYZE?
Inspect scan and join nodes, estimated versus actual rows, loops, and buffer activity. Misestimates can reflect stale statistics, correlations, or parameter sensitivity. Heavy reads can suggest an inefficient access path, but neither observation proves a single cause.
When should I not add an index?
Avoid adding an index without evidence that it reduces important workload cost. Consider existing indexes, write overhead, constraints, data distribution, and query shape. Low-cardinality columns can still benefit from selective partial or composite indexes.
What tools automate Postgres query optimization?
Native PostgreSQL statistics, pganalyze, and managed monitoring services help diagnose query behavior. Cast AI Database Optimization proposes index and query rewrites with projected plan impact. Human review and representative validation remain necessary before applying changes.

