Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

pg_stat_statements is in the config but the view cannot be queried

ErrorERROR: relation "pg_stat_statements" does not exist

Short answer

pg_stat_statements needs two separate things. The server must load the module through shared_preload_libraries and be restarted, and each database where you query it needs CREATE EXTENSION pg_stat_statements. Query identifiers must be enabled too. Missing either step gives a missing view or an unloaded module.

  • Applies to PostgreSQL 16
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Check what the running server has loaded, and whether the extension exists in the database you are connected to.

    SHOW shared_preload_libraries;
    \\dx pg_stat_statements
  2. 2Match what you see, then jump to its fix

    1. 1SHOW shared_preload_libraries does not list pg_stat_statementsThe module is not preloaded, or the server was not restartedFix
    2. 2The module is loaded but the view is missing in one database and present in anotherThe extension was never created in this databaseFix
    3. 3The view exists but stays empty while queries runQuery identifiers are turned offFix

What does the error look like?

  • ERROR: relation "pg_stat_statements" does not exist

Cause 1 of 3

The module is not preloaded, or the server was not restarted

1Check

The module needs extra shared memory, so it must be named in shared_preload_libraries and the server restarted. Reloading the config is not enough.

SHOW shared_preload_libraries;

2Fix

Add it in postgresql.conf, then restart the server.

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

sudo systemctl restart postgresql

3Verify

The setting now lists the module.

SHOW shared_preload_libraries;

Confirmed by postgresql.org

Did this fix it?

Cause 2 of 3

The extension was never created in this database

1Check

Statistics are collected across all databases, but the view and functions exist only in databases where the extension was created.

\c mydb
\dx

2Fix

Create the extension in each database where you want to query it.

CREATE EXTENSION pg_stat_statements;

3Verify

The view answers.

SELECT count(*) FROM pg_stat_statements;

Confirmed by postgresql.org

Did this fix it?

Cause 3 of 3

Query identifiers are turned off

1Check

The module is active only when query identifier calculation is enabled, which happens automatically when compute_query_id is auto or on.

SHOW compute_query_id;

2Fix

Set compute_query_id to auto or on in postgresql.conf and apply the change.

# postgresql.conf
compute_query_id = on

# then reload the configuration

3Verify

Run a few queries and read the view.

SELECT query, calls FROM pg_stat_statements ORDER BY calls DESC LIMIT 5;

Confirmed by postgresql.org

Did this fix it?

How do I stop it coming back?

  • Put the preload line in the configuration management, not only in a manual edit.
  • Create the extension in the template database if every new database needs it.
  • After editing shared_preload_libraries, always restart; a reload does nothing for it.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.