Debugging Performance with pg_stat_statements

This how-to shows how to get a query identifier through PostgREST and then use it to inspect the same query in pg_stat_statements.

Important

Get the Query Identifier from PostgREST

Request the plan in JSON format with the verbose option:

curl "http://localhost:3000/projects?select=id,name&order=id" \
  -H "Accept: application/vnd.pgrst.plan+json; options=verbose"

The response will contain a top-level Query Identifier field:

[
  {
    "Plan": {
      "Node Type": "Aggregate"
    },
    "Query Identifier": -432192689578025496
  }
]

Use that identifier against pg_stat_statements:

select
  calls,
  total_exec_time,
  mean_exec_time,
  rows,
  query
from pg_stat_statements
where queryid = -432192689578025496;

calls	total_exec_time	mean_exec_time	rows	query
13	0.6355850000000001	0.04889115384615385	13	WITH pgrst_source AS (…)

This lets you correlate a PostgREST request with PostgreSQL runtime statistics such as: