Use the PlanetScale Query Insights API to list the top Neki query fingerprints by CPU time and IO time, then merge them into a single prioritized list of queries to optimize before you increase cluster size.
What you will build
A TypeScript script that uses the PlanetScale Query Insights API to find the Neki queries consuming the most CPU and IO.
Read PlanetScale service-token credentials -> GET /v1/organizations/{org}/databases/{db}/branches/{branch}/insights?sort=cpuTime -> GET same endpoint?sort=ioTime -> merge by fingerprint -> print JSON with top_cpu, top_io, and combined_suspects.
AI prompt
Create a TypeScript workflow that ranks Neki query patterns by CPU time and IO time using the PlanetScale Query Insights API. The script should read service-token credentials from the environment, fetch the top queries sorted by cpuTime and ioTime, and emit a JSON report containing separate CPU and IO rankings plus a combined suspects list. Confirm the exact response keys and sort parameter values from a live run.
Prerequisites
- Node.js 22 or later
- A PlanetScale service token with the read_database permission for the target database
- The organization name, database name, and branch name for the Neki database you want to inspect
The API shape used below is documented in the PlanetScale list_branch_queries reference; the sort=cpuTime|ioTime and per-fingerprint response fields were announced in the Query Insights API endpoints changelog.
Environment variables
PLANETSCALE_SERVICE_TOKEN_ID(secret): PlanetScale service token ID from Settings > Service tokensPLANETSCALE_SERVICE_TOKEN(secret): PlanetScale service token secretPLANETSCALE_ORG: PlanetScale organization name slugPLANETSCALE_DATABASE: Neki database name slugPLANETSCALE_BRANCH: Database branch name, e.g. main
1. Create the project
Set up a Node project with TypeScript and tsx so you can run the script with native fetch.
mkdir -p neki-query-ranker/src && cd neki-query-ranker
cat > package.json <<'EOF'
{
"name": "neki-query-insights-ranker",
"version": "1.0.0",
"type": "module",
"private": true,
"scripts": {
"rank": "tsx src/rankQueries.ts"
},
"devDependencies": {
"@types/node": "^22.10.0",
"tsx": "^4.19.0",
"typescript": "^5.7.0"
}
}
EOF
cat > tsconfig.json <<'EOF'
{
"compilerOptions": {
"target": "ES2022",
"module": "NodeNext",
"moduleResolution": "NodeNext",
"strict": true,
"esModuleInterop": true,
"skipLibCheck": true,
"forceConsistentCasingInFileNames": true,
"types": ["node"]
},
"include": ["src/**/*.ts"]
}
EOF
npm install
2. Implement the ranking script
Create src/rankQueries.ts. It calls GET /v1/organizations/{org}/databases/{db}/branches/{branch}/insights twice, once with sort=cpuTime and once with sort=ioTime, and merges the results by query fingerprint.
/**
* Rank Neki query patterns by CPU time and IO time using the PlanetScale
* Query Insights API.
*/
interface InsightsResponse {
type: "list";
current_page: number | null;
per_page: number | null;
next_page: number | null;
prev_page: number | null;
next_page_url: string | null;
prev_page_url: string | null;
data: QueryInsight[];
}
interface QueryInsight {
id: string;
type: string;
fingerprint: string;
statement_type: string;
keyspace: string;
normalized_sql: string;
multishard: boolean;
query_count: number;
error_count: number;
sum_total_duration_millis: number;
sum_cpu_duration_millis: number;
sum_cpu_duration_percent: number;
sum_io_duration_millis: number;
sum_io_duration_percent: number;
sum_rows_read: number;
sum_rows_affected: number;
sum_rows_returned: number;
last_run_at: string | null;
p50_latency: number;
p99_latency: number;
max_latency: number;
}
interface RankedQuery {
rank: number;
fingerprint: string;
statement_type: string;
normalized_sql: string;
query_count: number;
cpu_ms: number;
cpu_percent: number;
io_ms: number;
io_percent: number;
total_ms: number;
multishard: boolean;
}
interface CombinedQuery {
rank: number;
fingerprint: string;
statement_type: string;
normalized_sql: string;
query_count: number;
cpu_ms: number;
cpu_percent: number;
io_ms: number;
io_percent: number;
total_ms: number;
multishard: boolean;
cpu_rank: number | null;
io_rank: number | null;
}
interface Report {
branch: string;
database: string;
organization: string;
period: string;
top_cpu: RankedQuery[];
top_io: RankedQuery[];
combined_suspects: CombinedQuery[];
}
function requireEnv(name: string): string {
const value = process.env[name];
if (!value) {
throw new Error(`Missing required environment variable: ${name}`);
}
return value;
}
async function fetchInsights(
org: string,
database: string,
branch: string,
sort: "cpuTime" | "ioTime",
limit: number,
period: string,
authHeader: string
): Promise<InsightsResponse> {
const path = `/v1/organizations/${encodeURIComponent(
org
)}/databases/${encodeURIComponent(database)}/branches/${encodeURIComponent(
branch
)}/insights`;
const url = new URL(path, "https://api.planetscale.com");
url.searchParams.set("sort", sort);
url.searchParams.set("dir", "desc");
url.searchParams.set("per_page", String(limit));
url.searchParams.set("period", period);
const response = await fetch(url.toString(), {
headers: {
Authorization: authHeader,
Accept: "application/json",
},
});
if (!response.ok) {
const body = await response.text();
throw new Error(
`PlanetScale API returned ${response.status} ${response.statusText}: ${body}`
);
}
return (await response.json()) as InsightsResponse;
}
function toRanked(insight: QueryInsight, rank: number): RankedQuery {
return {
rank,
fingerprint: insight.fingerprint,
statement_type: insight.statement_type,
normalized_sql: insight.normalized_sql,
query_count: insight.query_count,
cpu_ms: insight.sum_cpu_duration_millis,
cpu_percent: insight.sum_cpu_duration_percent,
io_ms: insight.sum_io_duration_millis,
io_percent: insight.sum_io_duration_percent,
total_ms: insight.sum_total_duration_millis,
multishard: insight.multishard,
};
}
async function runRankings() {
const tokenId = requireEnv("PLANETSCALE_SERVICE_TOKEN_ID");
const token = requireEnv("PLANETSCALE_SERVICE_TOKEN");
const org = requireEnv("PLANETSCALE_ORG");
const database = requireEnv("PLANETSCALE_DATABASE");
const branch = requireEnv("PLANETSCALE_BRANCH");
const authHeader = `${tokenId}:${token}`;
const period = process.env["PLANETSCALE_INSIGHTS_PERIOD"] ?? "1d";
const limit = Number(process.env["PLANETSCALE_TOP_N"] ?? "10");
const [cpuResponse, ioResponse] = await Promise.all([
fetchInsights(org, database, branch, "cpuTime", limit, period, authHeader),
fetchInsights(org, database, branch, "ioTime", limit, period, authHeader),
]);
const topCpu = cpuResponse.data.map((q, i) => toRanked(q, i + 1));
const topIo = ioResponse.data.map((q, i) => toRanked(q, i + 1));
const byFingerprint = new Map<
string,
CombinedQuery & { worstRank: number }
>();
function upsert(q: RankedQuery, rankField: "cpu_rank" | "io_rank") {
const existing = byFingerprint.get(q.fingerprint);
if (existing) {
existing[rankField] = q.rank;
existing.worstRank = Math.max(existing.worstRank, q.rank);
existing.cpu_ms = q.cpu_ms;
existing.cpu_percent = q.cpu_percent;
existing.io_ms = q.io_ms;
existing.io_percent = q.io_percent;
existing.total_ms = q.total_ms;
} else {
byFingerprint.set(q.fingerprint, {
rank: 0,
fingerprint: q.fingerprint,
statement_type: q.statement_type,
normalized_sql: q.normalized_sql,
query_count: q.query_count,
cpu_ms: q.cpu_ms,
cpu_percent: q.cpu_percent,
io_ms: q.io_ms,
io_percent: q.io_percent,
total_ms: q.total_ms,
multishard: q.multishard,
cpu_rank: rankField === "cpu_rank" ? q.rank : null,
io_rank: rankField === "io_rank" ? q.rank : null,
worstRank: q.rank,
});
}
}
for (const q of topCpu) upsert(q, "cpu_rank");
for (const q of topIo) upsert(q, "io_rank");
const combined = Array.from(byFingerprint.values())
.sort((a, b) => a.worstRank - b.worstRank)
.map(({ worstRank: _, ...rest }, index) => ({
...rest,
rank: index + 1,
}));
const report: Report = {
branch,
database,
organization: org,
period,
top_cpu: topCpu,
top_io: topIo,
combined_suspects: combined,
};
console.log(JSON.stringify(report, null, 2));
}
runRankings().catch((error) => {
console.error(error instanceof Error ? error.message : String(error));
process.exit(1);
});
3. Run the ranking
Export your PlanetScale credentials and run the script. The output is a JSON object with top_cpu, top_io, and combined_suspects arrays.
export PLANETSCALE_SERVICE_TOKEN_ID="$PLANETSCALE_SERVICE_TOKEN_ID"
export PLANETSCALE_SERVICE_TOKEN="$PLANETSCALE_SERVICE_TOKEN"
export PLANETSCALE_ORG="your-org"
export PLANETSCALE_DATABASE="your-database"
export PLANETSCALE_BRANCH="your-branch"
export PLANETSCALE_INSIGHTS_PERIOD="1d"
npm run rank
Verify the result
Run against v1 on 2026-11-04.
npm install && npm run rank
Observed output:
{
"branch": "[REDACTED]",
"database": "[REDACTED]",
"organization": "[REDACTED]",
"period": "1d",
"top_cpu": [
{
"rank": 1,
"fingerprint": "6325a806224270914b6adef456f1be69ad2946afcb347fd2cbd810d64d5323f9",
"statement_type": "CREATE EXTENSION",
"normalized_sql": "CREATE EXTENSION IF NOT EXISTS ltree",
"query_count": 1,
"cpu_ms": 60.781,
"cpu_percent": 35.758788057067214,
"io_ms": 28.187,
"io_percent": 80.06533162902997,
"total_ms": 45.947,
"multishard": true
}
],
"top_io": [
{
"rank": 1,
"fingerprint": "6325a806224270914b6adef456f1be69ad2946afcb347fd2cbd810d64d5323f9",
"statement_type": "CREATE EXTENSION",
"normalized_sql": "CREATE EXTENSION IF NOT EXISTS ltree",
"query_count": 1,
"cpu_ms": 60.781,
"cpu_percent": 35.758788057067214,
"io_ms": 28.187,
"io_percent": 80.06533162902997,
"total_ms": 45.947,
"multishard": true
}
],
"combined_suspects": [
{
"rank": 1,
"fingerprint": "6325a806224270914b6adef456f1be69ad2946afcb347fd2cbd810d64d5323f9",
"statement_type": "CREATE EXTENSION",
"normalized_sql": "CREATE EXTENSION IF NOT EXISTS ltree",
"query_count": 1,
"cpu_ms": 60.781,
"cpu_percent": 35.758788057067214,
"io_ms": 28.187,
"io_percent": 80.06533162902997,
"total_ms": 45.947,
"multishard": true,
"cpu_rank": 1,
"io_rank": 1
}
]
}
(Trimmed above to the top entry per list; the live run returned three entries in each of top_cpu and top_io and four in combined_suspects.)
Expected: A JSON report ranking the top query fingerprints by CPU time and IO time, with a combined suspects list that de-duplicates queries appearing in both rankings.
How it works
The script authenticates to api.planetscale.com with a service-token id:secret pair. It issues two parallel GET requests to the branch insights endpoint, sorting one by cpuTime and the other by ioTime. The API returns aggregated per-fingerprint statistics including sum_cpu_duration_millis, sum_cpu_duration_percent, sum_io_duration_millis, sum_io_duration_percent, query_count, normalized_sql, and multishard. The script maps those fields into ranked lists, then combines the lists by fingerprint so a query that appears in both CPU and IO top-N is only surfaced once, preserving both ranks. This gives a short list of queries to optimize before considering a larger cluster.
What the run corrected
These are beliefs the documentation supported and the run disproved.
Assumed: The API examples showing Authorization: Bearer <token> are correct for service tokens Observed: Service tokens must be passed as a raw id:secret pair in the Authorization header, with no scheme Evidence: The service-token docs show curl -H 'Authorization: <SERVICE_TOKEN_ID>:<SERVICE_TOKEN>', and the live API returns 401 when Bearer is used
Assumed: Each query object in the response only has the fields listed under the data schema Observed: Every returned query object also includes a 'type' field (e.g. 'HourlyBranchQuery') and a 'dimension_counts' field on the top-level response Evidence: Live JSON response included data[0].type === 'HourlyBranchQuery' and a top-level dimension_counts: null key not shown in the OpenAPI schema snippet
Common issues
401 Unauthorized from the PlanetScale API
Cause: The Authorization header is sent as a Bearer token instead of the service-token id:secret pair
Fix: Use Authorization: <PLANETSCALE_SERVICE_TOKEN_ID>:<PLANETSCALE_SERVICE_TOKEN> with no Bearer prefix
404 Not Found for a database or branch that exists in the UI
Cause: The service token lacks read_database permission or the branch name is not URL-encoded
Fix: Add the read_database database permission to the token and encode path segments with encodeURIComponent
Queries returned with zero cpu_ms and io_ms
Cause: The period parameter is omitted and the default window contains no matching traffic, or Query Insights is not enabled
Fix: Set period to one of the supported values (e.g. 1d) and confirm Query Insights is enabled for the database
Sorting by 'cpuTime' returns an error
Cause: The sort parameter is written in snake_case instead of the API's camelCase enum
Fix: Use sort=cpuTime or sort=ioTime exactly as the OpenAPI spec defines them
Next steps
- Filter out DDL and ANALYZE by adding type=SELECT (or a list of statement types) so the ranking focuses on production traffic
- Fetch the per-fingerprint summary endpoint (/insights/{fingerprint}/summary) to see how CPU and IO trend over smaller time windows
- Add a threshold alert that pages when a query's cpu_percent or io_percent exceeds a budget, so you catch regressions before the bill does
- Cross-reference routing_index_usages and multishard flags with Neki shard topology to target rewrites that reduce cross-shard work
verification:
status: verified
tested_at: "2026-11-04"
product_version: "PlanetScale API v1 (Neki)"
command: "fetch Query Insights sorted by cpuTime and ioTime, merge by fingerprint, emit JSON report with top_cpu, top_io, and combined_suspects"
expected_result: "report with branch/database/organization/period fields; CREATE EXTENSION ltree at rank 1 in both top_cpu (cpu_ms 60.781) and top_io (io_ms 28.187); combined_suspects deduplicates entries and reports cpu_rank and io_rank per fingerprint"