PostgreSQL Query Statistics with pg_stat_statements

Performance / Guide 36 of 47 / Intermediate

• OSUbuntu 26.04 LTS
• PostgreSQL18.6-0ubuntu0.26.04.1
• Test data400,000 rows, 2 core / 4 GB host
• TimeAbout 14 min

pg_stat_statements must be in shared_preload_libraries, which requires a restart. It is the single most valuable extension to have installed before you need it.

This guide includes

Use this when you need to know which statements cost the most. This matters because it is not an ordinary extension - it needs a restart before CREATE EXTENSION does anything - and it aggregates by statement shape rather than by literal.

• loading the extension, which takes two steps rather than one
• running queries with different literals, and reading them back aggregated together
• seeing that statements are grouped by shape rather than by their values

Before you start

• Superuser access, and a restart window.

1. 1

   Load the extension and reset the counters

2. 2

   Run three queries and read them back aggregated

Official sources

• PostgreSQL 18 Documentation - Using EXPLAIN
• PostgreSQL 18 Documentation - Index Types
• PostgreSQL 18 Documentation - Partial Indexes
• PostgreSQL 18 Documentation - Routine Vacuuming
• PostgreSQL 18 Documentation - pg_stat_statements