Short answer: PostgreSQL almost never burns CPU on its own. Find the backend
that is doing it, then find its query. Two commands, in this order:
-- 1. which backends are running right now, and are they on CPU or waiting?
SELECT pid, state, wait_event_type, wait_event,
now() - query_start AS running_for, left(query, 60) AS query
FROM pg_stat_activity
WHERE state = 'active' AND backend_type = 'client backend';
-- 2. which statements have consumed the most CPU time since the last reset
SELECT round(total_exec_time::numeric,1) AS total_ms, calls,
round(mean_exec_time::numeric,1) AS mean_ms,
shared_blks_hit, shared_blks_read, left(query,60) AS query
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;A row in pg_stat_activity with state = 'active' and
wait_event_type empty is a backend on the CPU. If
wait_event_type is IO, Lock or LWLock, your
problem is not CPU. That single column decides which article you should be reading.
Table of Contents
A real CPU-bound query, and how it looks in every view
Two million rows, a regex filter that no index can serve, and the whole table already in
shared_buffers:
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT count(*) FROM users WHERE email ~ '@example\.com$';
Aggregate (actual time=1114.671..1114.675 rows=1 loops=1)
Buffers: shared hit=26667
-> Seq Scan on users (actual time=0.007..1103.317 rows=500000 loops=1)
Filter: (email ~ '@example\.com$'::text)
Rows Removed by Filter: 1500000
Buffers: shared hit=26667
Planning Time: 0.259 ms
Execution Time: 1114.701 mshit=26667 and read=0 is the diagnosis: not one block came from disk, so
all 1.1 seconds went into evaluating the regex 2,000,000 times. That is what CPU-bound looks like in
a plan. While it runs, pg_stat_activity shows the backend on CPU with no wait event,
and the operating system agrees:
pid | state | wait_event_type | wait_event | secs | query
-------+--------+-----------------+------------+------+------------------------------
33570 | active | | | 0.9 | SELECT count(*) FROM users...
$ ps -p 33570 -o pid=,%cpu=,time=
33570 94.8 0:00.92That pid column is the link the old version of this page never made: it is a real OS
process id, so top/ps and pg_stat_activity can be joined by
eye.
pg_stat_statements is the part you should install first
Add it to shared_preload_libraries, restart once, then
CREATE EXTENSION pg_stat_statements;. It aggregates by normalised query text, so the
query that runs 40,000 times a minute at 3 ms each outranks the one that runs nightly at 9 seconds —
which is the ranking you actually want when a CPU is pinned:
total_ms | calls | mean_ms | blks_hit | blks_read | query
----------+-------+---------+----------+-----------+------------------------------
3316.2 | 3 | 1105.4 | 80001 | 0 | SELECT count(*) FROM users WHERE email ~ $1Sort by total_exec_time to find where the CPU went, by mean_exec_time to
find the worst single query, and read blks_read to tell the two apart: near-zero reads
with high total time means CPU, not disk.
Fixing it: index what you actually filter on
The old version of this page offered email = 'user@example.com' as the “optimised”
form of email LIKE '%@example.com'. That is not an optimisation, it is a different
question — one row instead of half a million. A leading wildcard or an anchored regex cannot use a
plain B-tree, so index the expression you are testing:
CREATE INDEX users_domain_idx ON users ((split_part(email,'@',2)));
ANALYZE users;
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT count(*) FROM users WHERE split_part(email,'@',2) = 'example.com';
Finalize Aggregate (actual time=43.382..46.336 rows=1 loops=1)
-> Parallel Bitmap Heap Scan on users (actual time=7.890..37.914 rows=166667 loops=3)
Recheck Cond: (split_part(email, '@'::text, 2) = 'example.com'::text)
-> Bitmap Index Scan on users_domain_idx (actual time=7.056..7.056 rows=500000 loops=1)
Execution Time: 46.372 msSame 500,000 rows, same server, measured back to back through
pg_stat_statements: 1104.2 ms mean for the regex, 66.5 ms mean for the indexed
expression. Your numbers will differ; the method is the point.
Autovacuum is a CPU consumer, and it tells you exactly how much
Set log_autovacuum_min_duration = 0 and every autovacuum prints its own accounting.
After a 666,666-row UPDATE:
LOG: automatic vacuum of table "postgres.public.users": index scans: 1
pages: 0 removed, 35556 remain, 35556 scanned (100.00% of total)
tuples: 666666 removed, 2000000 remain, 0 are dead but not yet removable
buffer usage: 109817 hits, 1279 misses, 28373 dirtied
WAL usage: 101535 records, 23704 full page images, 127108280 bytes
system usage: CPU: user: 0.26 s, system: 0.07 s, elapsed: 8.66 ssystem usage: CPU is the number to argue with. Here autovacuum used 0.33 CPU-seconds
over 8.66 wall-clock seconds, so whatever it was waiting on, it was not the CPU. Turning it off because
“it uses CPU” is the classic way to turn a small problem into an outage; check the line first.
Running workers are visible in pg_stat_activity with
backend_type = 'autovacuum worker', and their progress in
pg_stat_progress_vacuum.
Do idle connections cost CPU? Measured: no
“More connections mean more CPU” is repeated everywhere, including in the old version of this
page. Summing the CPU time of every process in the cluster over a fixed 10-second window:
IDLE CLUSTER: CPU seconds consumed over 10s wall clock: 0.03 (7 processes)
100 IDLE CONNS: CPU seconds consumed over 10s wall clock: 0.02 (107 processes)
+10 ACTIVE QUERIES: CPU seconds consumed over 8s wall clock: 76.46 (117 processes)100 genuinely idle connections cost no measurable CPU. Ten concurrent copies of the regex query
consumed 76 CPU-seconds in 8 seconds of wall clock — roughly 9.5 cores of a 14-core machine. Idle
connections cost memory and snapshot-visibility work, and a connection pooler is still worth having;
they are not what is pinning your CPU. Queries are.
Once you know the statement, the order of cheap fixes is: add the missing index, cut the rows
examined (Rows Removed by Filter is the tell), raise work_mem if the plan
shows an external merge sort or a disk-spilling hash aggregate, and only then consider more cores.
Check this yourself. Every command and every block of output on this page is reproduced by /verify/fix-high-cpu-usage.sh. Download it and run it: it creates its own scratch files, prints one line per claim, cleans up after itself, and exits non-zero if any claim here turns out to be wrong. If it disagrees with this page, the page is wrong.
