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

Reference

Rank Your Neki Tables by Read Cost, Rows-Read Ratio, and Index Usage

Use the PlanetScale Query Insights API to pull query-level stats for a Neki branch, then aggregate them by table to surface which tables dominate load, how many rows are read per row returned, and which indexes are used. The underlying signals come from the PlanetScale Query Insights docs; the broader Neki monitoring guide covers where each metric lives.

What you will build

A small TypeScript CLI that turns Neki Query Insights query patterns into a per-table load report.

Set env vars -> GET /organizations/{org}/databases/{db}/branches/{branch}/insights (paginated) -> group by tables -> output ranked JSON with totalDurationMs, rowsReadPerReturned, and index usage.

AI prompt

Build a TypeScript script that calls the PlanetScale Query Insights API for a Neki database branch, aggregates the returned query patterns by table, and prints a ranked JSON report of per-table read cost, rows-read-per-row-returned, and index usage. Read all credentials from the environment, handle pagination, and keep only fields that appeared in the live API response.

Prerequisites

  • Node.js 22 or later with npm
  • A PlanetScale service token with read_database or read_databases access on your Neki database
  • Your PlanetScale organization name, database name, and branch name
  • Neki Query Insights enabled on the target branch (default for PlanetScale Postgres/Neki databases)

Environment variables

  • PLANETSCALE_SERVICE_TOKEN_ID: 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: Neki branch name
  • INSIGHTS_PERIOD: Time window for Query Insights (e.g. 1d, 7d). Defaults to 1d.
  • REPORT_LIMIT: Number of top tables to return. Defaults to 10.

1. Create the project and install dependencies

Set up a fresh Node project and install TypeScript plus the tsx runner so the script can be executed directly.

mkdir neki-table-load && cd neki-table-load
npm init -y
npm install --save-dev typescript tsx @types/node

2. Add the aggregator script in index.ts

Read the PlanetScale service token and branch identifiers from the environment, fetch every page of Query Insights, split each query pattern's cost across the tables it touches, and rank the result by total duration.

interface InsightsResponse {
  type: string;
  next_page: number | null;
  prev_page: number | null;
  data: QueryInsight[];
}

interface IndexUsage {
  name: string;
  count: number;
  percent: number;
}

interface QueryInsight {
  id: string;
  type: string;
  fingerprint: string;
  statement_type: string;
  keyspace: string;
  normalized_sql: string;
  tables: string[];
  qualified_tables: string[];
  table_keyspaces: string[];
  index_usages: IndexUsage[];
  routing_index_usages: IndexUsage[];
  query_count: number;
  error_count: number;
  sum_rows_read: number;
  sum_rows_returned: number;
  sum_rows_affected: number;
  rows_read_per_returned: number;
  sum_total_duration_millis: number;
  sum_cpu_duration_millis: number;
  sum_io_duration_millis: number;
  last_run_at: string | null;
}

interface TableSummary {
  table: string;
  qualifiedTable: string;
  queryPatterns: number;
  totalQueries: number;
  totalErrors: number;
  rowsRead: number;
  rowsReturned: number;
  rowsAffected: number;
  rowsReadPerReturned: number | null;
  totalDurationMs: number;
  cpuDurationMs: number;
  ioDurationMs: number;
  indexes: Map<string, { count: number; totalPercent: number }>;
}

function getEnv(name: string): string {
  const value = process.env[name];
  if (!value) throw new Error(`Missing required environment variable: ${name}`);
  return value;
}

async function fetchAllInsights(
  baseUrl: string,
  auth: string,
  org: string,
  database: string,
  branch: string,
  period: string,
): Promise<QueryInsight[]> {
  const results: QueryInsight[] = [];
  let page = 1;
  const perPage = 100;

  while (true) {
    const url = new URL(
      `${baseUrl}/organizations/${encodeURIComponent(org)}/databases/${encodeURIComponent(database)}/branches/${encodeURIComponent(branch)}/insights`,
    );
    url.searchParams.set("period", period);
    url.searchParams.set("per_page", String(perPage));
    url.searchParams.set("page", String(page));
    url.searchParams.set("sort", "rowsRead");
    url.searchParams.set("dir", "desc");

    const response = await fetch(url.toString(), {
      headers: { Authorization: auth, Accept: "application/json" },
    });

    if (!response.ok) {
      const body = await response.text();
      throw new Error(`PlanetScale API error ${response.status}: ${body}`);
    }

    const payload = (await response.json()) as InsightsResponse;
    results.push(...payload.data);

    if (!payload.next_page || payload.data.length < perPage) break;
    page = payload.next_page;
  }

  return results;
}

function aggregateByTable(queries: QueryInsight[]): {
  summaries: TableSummary[];
  unattributed: number;
} {
  const byTable = new Map<string, TableSummary>();
  let unattributed = 0;

  for (const query of queries) {
    if (query.tables.length === 0) {
      unattributed += 1;
      continue;
    }

    const tables = query.tables;
    const qualified = query.qualified_tables;
    const weight = 1 / tables.length;

    for (let i = 0; i < tables.length; i++) {
      const table = tables[i];
      const qualifiedTable = qualified[i] ?? table;
      const existing = byTable.get(table);

      const summary: TableSummary = existing ?? {
        table,
        qualifiedTable,
        queryPatterns: 0,
        totalQueries: 0,
        totalErrors: 0,
        rowsRead: 0,
        rowsReturned: 0,
        rowsAffected: 0,
        rowsReadPerReturned: null,
        totalDurationMs: 0,
        cpuDurationMs: 0,
        ioDurationMs: 0,
        indexes: new Map(),
      };

      summary.queryPatterns += 1;
      summary.totalQueries += query.query_count;
      summary.totalErrors += query.error_count;
      summary.rowsRead += query.sum_rows_read * weight;
      summary.rowsReturned += query.sum_rows_returned * weight;
      summary.rowsAffected += query.sum_rows_affected * weight;
      summary.totalDurationMs += query.sum_total_duration_millis * weight;
      summary.cpuDurationMs += query.sum_cpu_duration_millis * weight;
      summary.ioDurationMs += query.sum_io_duration_millis * weight;

      for (const usage of [...query.index_usages, ...query.routing_index_usages]) {
        if (!usage.name) continue;
        const idx = summary.indexes.get(usage.name);
        if (idx) {
          idx.count += usage.count;
          idx.totalPercent += usage.percent * usage.count;
        } else {
          summary.indexes.set(usage.name, {
            count: usage.count,
            totalPercent: usage.percent * usage.count,
          });
        }
      }

      if (!existing) byTable.set(table, summary);
    }
  }

  for (const summary of byTable.values()) {
    if (summary.rowsReturned > 0) {
      summary.rowsReadPerReturned = summary.rowsRead / summary.rowsReturned;
    }
  }

  const summaries = Array.from(byTable.values()).sort(
    (a, b) => b.totalDurationMs - a.totalDurationMs,
  );

  return { summaries, unattributed };
}

function formatOutput(summaries: TableSummary[], limit: number) {
  return summaries.slice(0, limit).map((s) => ({
    table: s.table,
    qualifiedTable: s.qualifiedTable,
    queryPatterns: s.queryPatterns,
    totalQueries: s.totalQueries,
    totalErrors: s.totalErrors,
    rowsRead: Math.round(s.rowsRead),
    rowsReturned: Math.round(s.rowsReturned),
    rowsAffected: Math.round(s.rowsAffected),
    rowsReadPerReturned: s.rowsReadPerReturned,
    totalDurationMs: Number(s.totalDurationMs.toFixed(3)),
    cpuDurationMs: Number(s.cpuDurationMs.toFixed(3)),
    ioDurationMs: Number(s.ioDurationMs.toFixed(3)),
    indexes: Array.from(s.indexes.entries())
      .map(([name, { count, totalPercent }]) => ({
        name,
        count,
        percent: count > 0 ? Number((totalPercent / count).toFixed(2)) : 0,
      }))
      .sort((a, b) => b.count - a.count),
  }));
}

async function main() {
  const tokenId = getEnv("PLANETSCALE_SERVICE_TOKEN_ID");
  const token = getEnv("PLANETSCALE_SERVICE_TOKEN");
  const org = getEnv("PLANETSCALE_ORG");
  const database = getEnv("PLANETSCALE_DATABASE");
  const branch = getEnv("PLANETSCALE_BRANCH");
  const period = process.env["INSIGHTS_PERIOD"] ?? "1d";
  const limit = Math.max(1, parseInt(process.env["REPORT_LIMIT"] ?? "10", 10));
  const baseUrl = "https://api.planetscale.com/v1";

  const queries = await fetchAllInsights(
    baseUrl,
    `${tokenId}:${token}`,
    org,
    database,
    branch,
    period,
  );

  const { summaries, unattributed } = aggregateByTable(queries);

  console.log(
    JSON.stringify(
      {
        period,
        queryPatternsAnalyzed: queries.length,
        unattributedQueryPatterns: unattributed,
        tablesReported: summaries.length,
        tables: formatOutput(summaries, limit),
      },
      null,
      2,
    ),
  );
}

main().catch((err) => {
  console.error(err instanceof Error ? err.message : String(err));
  process.exit(1);
});

3. Run the report against your Neki branch

Export the credentials and run the script. The output is a JSON object that ranks tables by total query duration and includes rows-read-per-row-returned plus index usage.

export PLANETSCALE_SERVICE_TOKEN_ID="your-token-id"
export PLANETSCALE_SERVICE_TOKEN="your-token-secret"
export PLANETSCALE_ORG="your-org"
export PLANETSCALE_DATABASE="your-neki-db"
export PLANETSCALE_BRANCH="main"
npx tsx index.ts

Verify the result

Run against PlanetScale API v1 on 2026-11-04.

npx tsx index.ts

Observed output:

{
  "period": "1d",
  "queryPatternsAnalyzed": 100,
  "unattributedQueryPatterns": 22,
  "tablesReported": 30,
  "tables": [
    {
      "table": "orders",
      "qualifiedTable": "postgres.public.orders",
      "queryPatterns": 19,
      "totalQueries": 26,
      "totalErrors": 1,
      "rowsRead": 2442,
      "rowsReturned": 438,
      "rowsAffected": 603,
      "rowsReadPerReturned": 5.575042800076089,
      "totalDurationMs": 244.657,
      "cpuDurationMs": 19.83,
      "ioDurationMs": 1.232,
      "indexes": [
        { "name": "orders", "count": 9, "percent": 100 },
        { "name": "public.order_events.order_events_pkey", "count": 1, "percent": 100 },
        { "name": "public.orders.orders_pkey", "count": 1, "percent": 100 },
        { "name": "order_events", "count": 1, "percent": 100 },
        { "name": "order_items", "count": 1, "percent": 100 },
        { "name": "public.order_events.order_events_order_id_idx", "count": 1, "percent": 100 },
        { "name": "public.order_items.order_items_pkey", "count": 1, "percent": 100 }
      ]
    },
    {
      "table": "order_events",
      "qualifiedTable": "postgres.public.order_events",
      "queryPatterns": 10,
      "totalQueries": 10,
      "totalErrors": 0,
      "rowsRead": 1729,
      "rowsReturned": 112,
      "rowsAffected": 201,
      "rowsReadPerReturned": 15.493651979088872,
      "totalDurationMs": 111.068,
      "cpuDurationMs": 8.409,
      "ioDurationMs": 0.184,
      "indexes": [
        { "name": "order_events", "count": 2, "percent": 100 },
        { "name": "public.order_events.order_events_pkey", "count": 1, "percent": 100 },
        { "name": "public.orders.orders_pkey", "count": 1, "percent": 100 },
        { "name": "public.order_events.order_events_order_id_idx", "count": 1, "percent": 100 },
        { "name": "public.order_items.order_items_pkey", "count": 1, "percent": 100 }
      ]
    }
  ]
}

(Trimmed above to the top two entries; the live run reported ten tables including invoices, columns, tx_accounts, order_items, pg_tables, tables, pg_namespace, and pg_proc.)

Expected: A JSON report ranking tables by total query duration, with rowsReadPerReturned and the indexes used by queries touching each table.

How it works

The PlanetScale Query Insights API returns one object per normalized query pattern. Each object includes the tables the query touched, total rows read/returned/affected, total/CPU/IO duration in milliseconds, and index_usages plus routing_index_usages arrays. The script walks every page of results, splits each query pattern's cost evenly across its tables, and accumulates per-table totals. It computes rowsReadPerReturned from the aggregated rowsRead and rowsReturned, and rolls up index usage by name. The final JSON is sorted by totalDurationMs so the tables that dominate load appear first.

What the run corrected

These are beliefs the documentation supported and the run disproved.

  • Assumed: The response uses simple keys like rows_read and rows_returned for totals. Observed: The live response uses sum_rows_read, sum_rows_returned, sum_total_duration_millis, and rows_read_per_returned. Evidence: The first item in the live /insights response contained sum_rows_read: 3578, sum_rows_returned: 158, and rows_read_per_returned: 22.645569620253166.

  • Assumed: index_usages would always contain fully-qualified index names. Observed: Index usage entries can be a fully-qualified index, a bare table name (indicating a scan), or an empty string for some join plans. Evidence: Live entries included {"name":"public.tx_accounts.nick_uniq",...}, {"name":"orders",...}, and {"name":"",...}.

  • Assumed: Every query pattern would report at least one table in the tables array. Observed: Some patterns (metadata, EXPLAIN, connection setup) return an empty tables array. Evidence: The live run found 22 unattributed query patterns out of 100; their tables and qualified_tables arrays were both empty.

  • Assumed: Pagination would expose a next_page_url to follow. Observed: The list wrapper exposes next_page as an integer page number and prev_page; next_page_url is not present. Evidence: The live response wrapper was {"type":"list","next_page":null,"prev_page":null,"data":[...]}.

Common issues

API returns 403 Forbidden when fetching insights

Cause: The service token lacks read_database or read_databases access on the Neki database.

Fix: Grant the token Database read access (or organization read_databases) in PlanetScale Settings > Service tokens.

ioDurationMs is always 0 and I/O columns are missing

Cause: I/O timing is only collected when the branch has track_io_timing enabled.

Fix: Enable track_io_timing in the branch's cluster configuration Parameters tab and re-run after the metric collection window passes.

Index list contains table names or empty strings instead of real index names

Cause: Query Insights emits a table name for full scans and an empty name for some multi-table plans; these are real API values, not parsing errors.

Fix: Treat table-name entries as scan indicators and skip empty names in your aggregation, or map them explicitly in your reporting layer.

The script only returns 100 query patterns and stops

Cause: Pagination is keyed by the integer next_page field, not by a next_page_url string.

Fix: Loop while next_page is present and request the next integer page, as shown in fetchAllInsights.

Next steps

  • Drill into the top table by calling GET /insights/{fingerprint} for its query patterns to see exactly which normalized SQL is driving the cost.
  • Add the type or tablet_type filters to separate read-heavy SELECTs from write load and isolate replica vs primary traffic.
  • Compare 1d and 7d reports side-by-side to spot tables whose load grew after a recent data increase.
  • Feed tables with high rowsReadPerReturned and empty/table-name index entries into the Schema Recommendations API or traffic-control budgets.
verification:
  status: verified
  tested_at: "2026-11-04"
  product_version: "PlanetScale API v1 (Neki)"
  command: "fetch all pages of /insights for a Neki branch sorted by rowsRead, aggregate per-table with cost split evenly across each query's tables, emit a JSON report with rowsReadPerReturned and index usage per table"
  expected_result: "report with period=1d, queryPatternsAnalyzed=100, unattributedQueryPatterns=22, tablesReported=30; top table 'orders' at totalDurationMs 244.657 and rowsReadPerReturned ~5.58; 'order_events' second at 111.068ms with rowsReadPerReturned ~15.49; indexes aggregated by name per table"