Extracting and Resetting Slow Query Data from YugabyteDB Anywhere (YBA) via API

YugabyteDB Anywhere (YBA) provides a powerful management and observability layer for your YugabyteDB clusters, including metrics, health dashboards, alerts, and query performance insights.

One of the most valuable tools under “Performance → Slow Queries” is the Slow Queries Dashboard. This view surfaces statements with high latency, large execution counts, or excessive resource consumption… all powered by PostgreSQL’s pg_stat_statements under the hood.

But sometimes you may want to:

● Export this slow query data to analyze it in Excel or Google Sheets
● Compare performance before and after a workload change
● Reset the slow query list before a new round of testing

In this tip, we’ll walk through a few shell commands that use the YBA REST API to retrieve and manage slow query data programmatically.

Prerequisites

Make sure your environment includes:

● curl and jq installed
● Access to your YBA URL
● A valid API token (generated from your YBA user profile)
● The Universe ID for the target cluster

Step 1. Gather Required IDs and API Token

Before running any commands, you’ll need three identifiers:

🔹 API Token

In YBA, click your user icon (top right corner) → User Profile
Click Generate Key (only one exists at a time per user)
Copy the key and store it securely

🔹 Customer ID

You can usually find this in the same User Profile window. If not, run the following:

				
					yba_url=https://<YBA URL>
api_token=<API token>

customer_id=$(curl --url $yba_url/api/v1/customers \
  --header "X-AUTH-YW-API-TOKEN: $api_token" \
  --insecure | jq -r ".[0].uuid")

Example output:

				
					[{"uuid":"c286d970-d070-4d54-a035-1287559f7019","code":"dev","name":"David Roberts"}]

Your customer ID is the value of the "uuid" field.

🔹 Universe ID

Go to Universes → [Your Universe]
Copy the UUID at the end of the browser URL, e.g.

				
					https://<YBA address>/universes/7ffda97b-34cc-413b-9ba1-af4d1e20e752

Step 2. Set Up Environment Variables

Once you have those values, export them into variables for convenience:

				
					# These should already be set from Step 1
yba_url=https://<YBA URL>
api_token=<API token>
customer_id=<customer ID>

# Make sure to set this one too!
universe_id=<universe ID>

Step 3. Retrieve Slow Query Data for a Universe

YBA’s REST endpoint /slow_queries returns the same data shown in the UI dashboard, derived from pg_stat_statements across all YSQL nodes.

				
					universe_id=<universe ID>

slow_queries=$(curl --url https://$yba_url/api/v1/customers/$customer_id/universes/$universe_id/slow_queries \
  --header "X-AUTH-YW-API-TOKEN: $api_token" \
  --insecure)

Step 4. Convert JSON to CSV for Easy Review

The API returns JSON. To open the results in a spreadsheet, convert them to CSV using jq:

				
					echo $slow_queries | jq -r '
  [.ysql.queries[] | del(.yb_latency_histogram)] |
  (map(keys) | add | unique) as $cols |
  map(. as $row | $cols | map($row[.])) as $rows |
  $cols, $rows[] | @csv
' > ./slow_queries.csv

You’ll now have a file named slow_queries.csv containing details like query, avg_latency, calls, and rows_returned.

👉 Open it in Excel, Google Sheets, or Numbers to filter and sort by average latency or call count.

Step 5. Reset Slow Query Data (Optional)

Before starting a new workload test or benchmark, you might want to clear the existing slow query statistics.

You can do this by sending a DELETE request to the same endpoint:

				
					curl --url https://$yba_url/api/v1/customers/$customer_id/universes/$universe_id/slow_queries \
  --header "X-AUTH-YW-API-TOKEN: $api_token" \
  --insecure -X DELETE

This resets YBA’s cached pg_stat_statements data for that universe.

How the Slow Queries Dashboard Works

Under the hood, YBA periodically queries pg_stat_statements on each YSQL node, aggregates the statistics, and stores the top entries in its internal metrics store.

The UI at Performance → Slow Queries then presents this information, including:

● Average latency (ms)
● Total execution count
● Rows returned
● Reads/writes per query

That same data is accessible through the REST API you just used.

More details:

Why This Tip Is Useful

✅ Automate Performance Analysis: Schedule periodic exports via cron or CI/CD scripts.
✅ Compare Query Stats: Track improvements or regressions between versions.
✅ Integrate with Other Tools: Feed slow query data into Grafana or BI dashboards.
✅ Clean Slate Testing: Reset pg_stat_statements before each benchmark.

Have Fun!