Join us Nov 24 to watch Insights Agent turn slow queries into PRs. RSVP
Navigation

Reference

Rank Neki Queries by CPU Time and IO Time Before Upsizing

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 tokens
  • PLANETSCALE_SERVICE_TOKEN (secret): PlanetScale service token secret
  • PLANETSCALE_ORG: PlanetScale organization name slug
  • PLANETSCALE_DATABASE: Neki database name slug
  • PLANETSCALE_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"