Menu

How EXPLAIN ANALYZE helped us cut PostgreSQL query time by 80% under peak load
📰

How EXPLAIN ANALYZE helped us cut PostgreSQL query time by 80% under peak load

DEV Community: postgres·Trevor Ogina·6 days ago
#guLTKehO
#devtopostgresql#devto#dev#index#time
Reading 0:00
15s threshold

On an SME advisory platform I worked on, used by more than 1,000 business owners, the database CPU spiked every time traffic peaked. Real-time advisory sessions lagged, and the first instinct on the team was "we need a bigger database."

We didn't need one. Rebuilding the indexes around how the application actually queried data cut query time by 80% and removed the CPU spikes. This is the process I used, and it works on most slow-Postgres problems.

1. Find the queries that matter

Don't guess. Under load, find out which queries take up the database's time. The pg_stat_statements extension is the easiest way:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements ;

SELECT query , calls , round ( total_exec_time :: numeric , 1 ) AS total_ms , round ( mean_exec_time :: numeric , 2 ) AS mean_ms FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10 ;

Enter fullscreen mode

Exit fullscreen mode

Sort by total time, not mean time. A 5 ms query called 200,000 times hurts more than one 2-second report.

2.…

Continue reading — create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.