A database can become slow even when the application code has not changed. Pages take longer to load, API responses become inconsistent, and background jobs start falling behind. In many cases, developers first suspect network problems or application-level bottlenecks. However, the actual problem may be a database query that consumes too many resources.
In PostgreSQL, identifying expensive queries requires more than looking at execution time. A query that runs for several seconds might be waiting for disk access or a lock rather than using the CPU continuously. Similarly, a query that finishes quickly but executes thousands of times can create a significant workload.
The goal is to identify which queries contribute most to database load, understand why they are expensive, and make improvements without introducing new performance problems.
This article explains how to investigate database CPU usage in PostgreSQL using query statistics, active-session monitoring, execution plans, and operating system metrics.
Understand the Difference Between CPU Usage and Query Execution Time
Before investigating queries, it is important to understand what database performance metrics actually tell you.
Query execution time measures how long a query takes to complete. CPU usage measures how much processing time the system spends executing instructions. These measurements are related, but they are not the same.
For example, consider two queries:
Query A has the longer execution time, but Query B may consume more CPU per execution.
A third query might take only 20 milliseconds but execute 10,000 times per minute. Its cumulative cost can be greater than that of a query executed only a few times.
When investigating database CPU consumption, examine three important dimensions:
Total workload: How much execution time and work does the query contribute across all calls?
Per-execution cost: How expensive is one execution?
Execution frequency: How often does the application run the query?
You should also examine actual CPU metrics and wait events before concluding that a query is CPU-bound.
Enable Query Statistics with pg_stat_statements
PostgreSQL provides the pg_stat_statements extension for collecting statistics about SQL statements executed by the server.
It groups similar statements together, allowing developers to identify frequently executed queries and queries with high cumulative execution time. For example, statements that differ only in parameter values can generally be grouped under the same normalized query representation.
This is useful because production applications often generate thousands of SQL statements. Reviewing individual application logs may not reveal which query patterns contribute most to the workload.
Configure pg_stat_statements
On many PostgreSQL installations, pg_stat_statements must be loaded through the shared_preload_libraries configuration parameter.
Check the current configuration:
SHOW shared_preload_libraries;
If pg_stat_statements is not listed, a database administrator may need to update the configuration:
shared_preload_libraries = 'pg_stat_statements'
If the setting already contains other libraries, preserve them when adding pg_stat_statements.
Changing shared_preload_libraries normally requires a PostgreSQL server restart. Follow your managed database provider's instructions if you use a cloud-hosted PostgreSQL service.
After the library is loaded, create the extension in the database where you want to query its statistics:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
You may need additional privileges to change the server configuration or create the extension. Availability and configuration options can also differ between PostgreSQL distributions and managed services.
Once configured, the extension collects statistics as queries execute.
Find Queries with the Highest Total Execution Time
Start by ranking statements according to their cumulative execution time:
SELECT
queryid,
calls,
total_exec_time,
mean_exec_time,
rows,
shared_blks_hit,
shared_blks_read,
LEFT(query, 200) AS query_sample
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;
This query returns several useful measurements:
queryid identifies a normalized query pattern.
calls shows how many times the statement has executed.
total_exec_time shows its accumulated execution time.
mean_exec_time shows its average execution time per call.
rows reports the total number of rows returned or affected, depending on the statement.
shared_blks_hit counts shared-buffer block hits.
shared_blks_read counts shared-buffer block reads that required reading blocks into shared buffers.
The buffer statistics provide clues about database activity, but they do not directly measure CPU consumption. Likewise, total_exec_time is not a measurement of CPU time alone.
Use this result to identify candidates for further investigation rather than treating the first row as automatically responsible for the CPU problem.
One important detail is that pg_stat_statements accumulates statistics over time. Its counters may reflect a longer period than the incident you are investigating. Check the collection interval and any reset history before interpreting the results.
Find Queries That Are Expensive Per Execution
A query with a high total execution time might be expensive because it runs frequently. Another query may run only a few times but take a long time each time.
To identify statements with high average execution time, use a separate query:
SELECT
queryid,
calls,
total_exec_time,
mean_exec_time,
rows,
LEFT(query, 200) AS query_sample
FROM pg_stat_statements
WHERE calls > 0
ORDER BY mean_exec_time DESC
LIMIT 20;
This helps identify queries that may be performing large scans, joining many rows, sorting large result sets, or executing complicated expressions.
However, average execution time can be misleading when a query has very few calls. A statement executed once during an unusual batch operation might rank above a query that runs continuously.
Always compare the number of calls with the execution time.
Compare Frequency and Per-Call Cost
Consider the following simplified example:
Query | Calls | Average execution time | Approximate total execution time |
|---|
Query A | 50,000 | 5 ms | 250 seconds |
Query B | 100 | 800 ms | 80 seconds |
Query C | 10 | 3,000 ms | 30 seconds |
Query A has the highest cumulative execution time because it runs so frequently. Query C has the highest average execution time.
These examples demonstrate why both rankings are useful.
Query A may benefit from reducing unnecessary database calls, batching requests, or improving a frequently used access path. Query C may require a more detailed execution-plan investigation.
The correct optimization depends on the actual workload, not simply on which query appears first in a report.
Identify Queries That Are Running Right Now
Historical statistics help identify recurring problems, but they do not show exactly what is happening during a CPU spike.
Use pg_stat_activity to inspect active database sessions:
SELECT
pid,
application_name,
usename,
state,
wait_event_type,
wait_event,
query_start,
clock_timestamp() - query_start AS query_duration,
LEFT(query, 200) AS query_sample
FROM pg_stat_activity
WHERE state = 'active'
AND pid <> pg_backend_pid()
ORDER BY query_start;
This query shows currently active statements, their start times, application names, and wait-event information.
Use it while the CPU problem is occurring. Capturing several samples over the incident can help reveal whether the same query remains active or whether many short-running statements repeatedly appear.
Interpret Wait Events Carefully
The state and wait-event columns provide useful context.
For example:
A session waiting on a lock may be blocked by another transaction.
A session waiting on an I/O event may be spending time waiting for data access.
An active session with no reported wait event may be executing on the CPU, but this alone does not prove that it is CPU-bound.
The distinction matters because optimizing a query that is waiting for a lock may not reduce CPU usage. Similarly, adding an index may not resolve a bottleneck caused by excessive application requests.
Check the wait events, transaction activity, and query plans before selecting a solution.
Also remember that pg_stat_activity displays the query associated with each session, and access to other users' query text and details may depend on your PostgreSQL privileges.
Correlate PostgreSQL Activity with Operating System CPU Usage
PostgreSQL query statistics describe database activity, but they do not provide a complete breakdown of operating system CPU consumption.
For self-managed PostgreSQL servers, use operating system tools alongside database monitoring.
Common tools include:
top or htop for observing CPU usage by process.
pidstat for examining process-level CPU usage over time.
vmstat for viewing system-wide CPU and memory activity.
Container or virtual-machine monitoring for CPU utilization and throttling.
The exact tools available depend on the operating system and deployment environment.
On a self-managed Linux server, PostgreSQL backend processes can often be matched to operating system process IDs. Comparing those processes with active database sessions can help narrow the investigation.
For example, if a backend process consistently consumes CPU while executing a particular statement, that is stronger evidence than query duration alone.
Cloud-hosted PostgreSQL services may not expose operating system process information. In that case, correlate database statistics with the provider's CPU metrics, query monitoring tools, and PostgreSQL activity views.
Build a Complete Picture
During an incident, collect information from multiple sources:
Information | What it helps you understand |
|---|
Database CPU utilization | Whether the server is experiencing a CPU bottleneck |
pg_stat_statements | Which query patterns contribute the most execution workload |
pg_stat_activity | Which sessions and queries are active right now |
Wait events | Whether sessions are waiting on locks, I/O, or other resources |
Execution plans | How PostgreSQL processes a specific query |
Application metrics | Whether the workload comes from a particular endpoint, service, or job |
No single metric provides the complete answer. The strongest diagnosis comes from correlating these observations over the same time period.
Investigate the Query Execution Plan
After identifying a query that deserves attention, inspect its execution plan.
PostgreSQL's EXPLAIN command shows the plan selected by the query planner. EXPLAIN ANALYZE additionally executes the statement and reports actual execution measurements.
For example:
EXPLAIN (ANALYZE, BUFFERS)
SELECT
order_id,
customer_id,
total_amount
FROM orders
WHERE customer_id = 1250
ORDER BY created_at DESC
LIMIT 50;
This example investigates a query that retrieves recent orders for a customer.
The output can reveal whether PostgreSQL uses an index scan, performs a sequential scan, sorts a large result set, or processes more rows than expected.
The BUFFERS option adds information about buffer activity, which can help distinguish some data-access patterns. However, buffer statistics still do not directly measure CPU time.
What to Look for in the Plan
Pay attention to the following details:
Sequential scans over large tables where the query needs only a small portion of the data.
Large differences between estimated and actual row counts.
Expensive sorts or hash operations.
Repeated execution of a costly plan node.
Large amounts of data processed before filters are applied.
Significant buffer reads or hits relative to the result size.
A sequential scan is not automatically a problem. It can be the best plan when a query needs a large portion of a table. Similarly, an index scan is not always faster, particularly when it requires many scattered data accesses.
Focus on the complete plan and the application's actual access pattern.
Use EXPLAIN ANALYZE Safely
EXPLAIN ANALYZE executes the statement being analyzed. Running it against a production query can add load, and running it on a data-changing statement can modify data.
For example, this command executes the update:
EXPLAIN (ANALYZE, BUFFERS)
UPDATE orders
SET status = 'processed'
WHERE status = 'pending';
Do not assume that EXPLAIN ANALYZE is a read-only inspection tool.
Prefer a representative staging environment when possible. If production investigation is necessary, assess the query's potential impact and use an appropriate safety plan. Wrapping a data-changing statement in a transaction and rolling it back may prevent its changes from being committed, but it does not eliminate execution cost, locking, trigger execution, or every possible external side effect.
Also remember that execution timings reported by EXPLAIN ANALYZE are elapsed timings for plan operations, not a direct measurement of CPU time.
Common Reasons Queries Consume Excessive CPU
Once you have a query and its execution plan, investigate the underlying cause.
Missing or Ineffective Indexes
A query that repeatedly scans a large table may do unnecessary work when it needs only a small subset of rows.
An index may help when the filtering or sorting conditions match an appropriate index design. However, index creation should be based on the query plan and data distribution rather than guesswork.
Additional indexes also consume storage and increase the cost of inserts, updates, and deletes.
Too Many Repeated Queries
Applications sometimes execute the same query repeatedly for individual records instead of retrieving the required data in a single operation.
For example, an API that loads a customer list and then executes a separate query for every customer can generate a large number of database calls.
Depending on the use case, batching, joins, or a different data-access pattern may reduce this overhead.
Inefficient Joins and Aggregations
Joining large datasets, performing repeated calculations, or aggregating more rows than necessary can increase processing work.
Review the join conditions, filtering logic, row estimates, and available indexes. Make sure the query processes only the data needed to produce the result.
Outdated Statistics
PostgreSQL uses table and column statistics to estimate how many rows a query will process. Poor estimates can lead to an inefficient execution plan.
The ANALYZE command refreshes planner statistics for a table:
ANALYZE orders;
PostgreSQL also performs automatic statistics collection through autovacuum under normal configurations. Before running manual maintenance, check whether statistics are stale and whether automatic maintenance is keeping up with the workload.
Excessive Application Load
A query can be individually efficient but still create excessive CPU demand when thousands of requests execute it concurrently.
In this situation, optimizing the SQL may help, but the application may also need request batching, concurrency controls, caching where appropriate, or reduced unnecessary polling.
Measure the impact of each change instead of assuming that a more complicated query rewrite will solve the problem.
Troubleshooting Database CPU Problems
Use the following table to connect common symptoms with practical investigation steps.
Symptom | Possible cause | Recommended investigation |
|---|
CPU stays high during normal traffic | Expensive queries or high query volume | Compare CPU metrics with pg_stat_statements rankings |
One query has very high total execution time | Frequent execution or high per-call cost | Compare calls, mean execution time, and query plans |
One query has a high average execution time | Large scans, joins, sorts, or complex calculations | Inspect EXPLAIN ANALYZE output in a safe environment |
CPU rises when traffic increases | Excessive concurrency or repeated queries | Compare request volume, active sessions, and query frequency |
Queries run for a long time but CPU is low | Lock waits, I/O waits, or another bottleneck | Inspect pg_stat_activity and wait events |
CPU increases after a deployment | Changed SQL, parameters, data volume, or execution plans | Compare query statistics and plans before and after the deployment |
CPU remains high despite adding an index | The index may not address the dominant cost | Recheck the actual plan and verify whether the query uses the index |
Best Practices for Monitoring Database CPU
A reliable investigation process depends on consistent monitoring rather than occasional manual checks.
Follow these practices:
Establish a baseline. Record normal CPU utilization, query latency, call volume, and database workload during representative periods.
Track cumulative and average execution time. These measurements help distinguish frequently executed queries from individually expensive ones.
Collect data during the incident. Historical statistics are useful, but active-session snapshots and CPU metrics can reveal what is happening at that moment.
Compare equivalent time windows. When evaluating a change, compare similar workloads and traffic levels.
Use query plans to validate assumptions. Confirm whether the suspected scan, join, sort, or repeated operation is actually present.
Measure the result after each change. Compare CPU utilization, latency, throughput, and error rates before and after the optimization.
Control monitoring overhead. Avoid repeatedly collecting large amounts of diagnostic data when a smaller, targeted sample is sufficient.
Protect sensitive query information. Query text and parameters can expose business data, so apply appropriate permissions and logging controls.
Common Mistakes to Avoid
Treating Total Execution Time as CPU Time
pg_stat_statements reports execution statistics, not direct CPU consumption per SQL statement. Use it to prioritize investigation, then correlate the results with CPU monitoring and other database evidence.
Optimizing Only the Slowest Query
The query with the longest average execution time may not contribute the most to the overall CPU load. Examine both call volume and per-execution cost.
Adding Indexes Without Checking the Plan
Indexes can improve specific access patterns, but unnecessary indexes increase storage requirements and write overhead. Verify that the proposed index addresses the actual query problem.
Running Diagnostic Queries Without Considering Their Impact
EXPLAIN ANALYZE executes the target statement. Large analytical queries can add substantial load, and data-changing statements can modify records. Choose the investigation environment and method carefully.
Ignoring the Application
Repeated polling, unnecessary database calls, excessive concurrency, and inefficient request handling can create a database bottleneck even when individual SQL statements appear reasonable.
Advantages and Limitations of Query-Level Investigation
Query-level monitoring helps developers identify expensive SQL patterns, prioritize optimization work, and connect database performance problems to application behavior.
Its main advantages include:
Better visibility into recurring query workloads.
More evidence-based index and query changes.
Easier comparison of workload patterns before and after deployments.
Improved collaboration between application and database teams.
However, query statistics have limitations. Execution time does not equal CPU time, aggregated statistics can hide differences between parameter values, and query plans may vary with data distribution or parameter selection. Operating system and database monitoring may also provide different levels of detail depending on the hosting environment.
For these reasons, query-level statistics should be combined with CPU metrics, wait-event analysis, and execution-plan inspection.
Summary
Finding PostgreSQL queries that consume too much database CPU requires more than sorting statements by execution time. Start with pg_stat_statements to identify query patterns with high cumulative or average execution time. Compare call frequency, inspect active sessions, and correlate the findings with operating system or cloud monitoring metrics.
Once a query has been identified, use its execution plan to investigate unnecessary scans, expensive joins, sorting operations, or repeated work. Apply a targeted improvement, then measure the results under a comparable workload.
The most effective approach is to establish what the database is doing, determine which activity contributes to the problem, and validate the improvement with measurements. This avoids unnecessary indexes and query rewrites while helping maintain predictable database performance in production.
Join the conversation! Your thoughts help the community grow.