Clearing pg_stat_statements Across All Nodes in YugabyteDB

When working with query performance in YugabyteDB, the pg_stat_statements extension is your best friend. It collects execution statistics for all SQL statements, helping you analyze query patterns, latencies, and resource usage.

But sometimes you want a clean slate… for example, before running benchmarks, reproducing query plans, or monitoring changes after a config tweak. In PostgreSQL, this is as simple as:

				
					SELECT pg_stat_statements_reset();

Why You Need to Reset on All Nodes in YugabyteDB

YugabyteDB is a distributed PostgreSQL-compatible database, and extensions like pg_stat_statements live locally on each YSQL node, not in globally replicated catalog tables.

That means calling pg_stat_statements_reset() on one node only clears stats on that node. To fully reset across the cluster, you must connect to each YSQL server and invoke the reset.

👉 For more background on how catalogs and extensions differ in YugabyteDB, check out:
🔗 Exploring Catalog Tables vs. Extension Views in YugabyteDB

Privileges Required

Resetting pg_stat_statements requires superuser privileges (or equivalent in YB). Ordinary users with pg_stat_statements access can view statistics, but cannot reset them.

Clearing Stats from the Command Line

Option 1: Simple Bash Script

Here’s a helper script that discovers all cluster hosts from the yb_servers() function, then loops over each node and runs pg_stat_statements_reset().

				
					#!/usr/bin/env bash
# reset_pgss_all_nodes.sh — reset pg_stat_statements on every YB TServer

set -euo pipefail

SEED_HOST="127.0.0.1"   # change me
PORT="5433"             # change if needed
DB="yugabyte"           # change if needed
USER="yugabyte"         # must be superuser (or equivalent)

# If you need TLS or a password, rely on .pgpass or export PGPASSWORD before running.
# Example: export PGPASSWORD='secret'

# 1) Discover hosts from the seed
hosts=$(ysqlsh -h "$SEED_HOST" -p "$PORT" -U "$USER" -d "$DB" -tA -v ON_ERROR_STOP=1 \
  -c "SELECT host FROM yb_servers();" \
  | sed '/^[[:space:]]*$/d' | sort -u)

if [[ -z "${hosts}" ]]; then
  echo "No hosts returned by yb_servers(); check SEED_HOST/PORT/creds." >&2
  exit 1
fi

echo "Discovered hosts:"
printf '  %s\n' $hosts

# 2) Reset pg_stat_statements on each host
for h in $hosts; do
  echo "Resetting on ${h}..."
  ysqlsh -h "$h" -p "$PORT" -U "$USER" -d "$DB" -v ON_ERROR_STOP=1 \
    -c "SELECT pg_stat_statements_reset();"
done

echo "Done."

Sample run:

				
					[root@localhost yb]# ./reset_pgss_all_nodes.sh
Discovered hosts:
  127.0.0.1
  127.0.0.2
  127.0.0.3
Resetting on 127.0.0.1...
 pg_stat_statements_reset
--------------------------

(1 row)

Resetting on 127.0.0.2...
 pg_stat_statements_reset
--------------------------

(1 row)

Resetting on 127.0.0.3...
 pg_stat_statements_reset
--------------------------

(1 row)

Done.

Option 2: One-Liner 🚀

If you just want something quick and compact:

				
					ysqlsh -h 127.0.0.1 -p 5433 -U yugabyte -d yugabyte -tA \
  -c "SELECT host FROM yb_servers();" \
  | xargs -I{} ysqlsh -h {} -p 5433 -U yugabyte -d yugabyte -q -tA \
    -c "SELECT pg_stat_statements_reset();" >/dev/null

This pipes the list of hosts from yb_servers() straight into xargs, which runs the reset on each host.

Sample run:

				
					[root@localhost yb]# ysqlsh -h 127.0.0.1 -p 5433 -U yugabyte -d yugabyte -tA \
-c "SELECT host FROM yb_servers();" \
| xargs -I{} ysqlsh -h {} -p 5433 -U yugabyte -d yugabyte -q -tA \
  -c "SELECT pg_stat_statements_reset();" >/dev/null

There is no output using this option.

Option 3: Compact Loop with Friendly Output 😎

Want something still short, but with clear logging? Use a for loop:

				
					for h in $(ysqlsh -h 127.0.0.1 -p 5433 -U yugabyte -d yugabyte -tA \
              -c "SELECT host FROM yb_servers();"); do
  echo "Resetting on $h..."
  ysqlsh -h "$h" -p 5433 -U yugabyte -d yugabyte -q -tA \
    -c "SELECT pg_stat_statements_reset();" >/dev/null
done
echo "Done."

Sample run:

				
					[root@localhost yb]# for h in $(ysqlsh -h 127.0.0.1 -p 5433 -U yugabyte -d yugabyte -tA \
              -c "SELECT host FROM yb_servers();"); do
  echo "Resetting on $h..."
  ysqlsh -h "$h" -p 5433 -U yugabyte -d yugabyte -q -tA \
    -c "SELECT pg_stat_statements_reset();" >/dev/null
done
echo "Done."
Resetting on 127.0.0.1...
Resetting on 127.0.0.2...
Resetting on 127.0.0.3...
Done.

Alternatives in YBA and Aeon

If you’re using Yugabyte’s management platforms, you don’t need to script this manually:

● YugabyteDB Anywhere (YBA): Navigate to the Slow Queries dashboard and click the Reset stats button. This automatically runs pg_stat_statements_reset() on all nodes.
● YugabyteDB Aeon (DBaaS): Go to Perf Advisor → Slow Queries and click Reset. Again, stats are cleared cluster-wide with a single click.

Wrapping up

● pg_stat_statements_reset() must be run on every node in a YugabyteDB cluster.
● Requires admin/superuser privileges.
● Use the Bash script, the one-liner, or the friendly loop above to clear from the CLI.
● Or, use the UI in YBA or Aeon for a one-click global reset.

Now you can start your benchmarks or troubleshooting with a clean query-statistics slate!

Have Fun!