rowScatter

Measure how scattered the rows of a value are across a table's pages, which is what clustering the table on that column would gain.

read-onlyany connectionsweeps instancesweeps groupcan return data values

Synopsis

rowScatter(column, schema, table, [max_rows], [sample_percent], [value])

Description

Measure how scattered the rows of a value are across a table's pages, which is what clustering the table on that column would gain. tableStats' physical_order_correlation says a column is out of physical order; this says what that costs. With 'value': exact -- the rows that match, the distinct pages they sit on, pages_if_packed (the pages those rows would need at the table's average rows per page) and scatter_ratio, pages over that, where 1 is already packed and 40 means a query for that value reads forty times the pages it needs to.

At most max_rows matching rows are read, and 'capped' says whether that limit was reached. Without 'value': sampled by whole pages (TABLESAMPLE SYSTEM, about ten thousand pages unless sample_percent says otherwise), for the values pg_stats lists as most common in the column, each with the share of pages it appears on and its scatter_ratio -- which answers which column is worth clustering on without naming a value. Reads the table's rows through ctid, so it needs SELECT on the table; returns counts only in the exact form, and in the sampled form only values tableStats already returns.

Not for a partitioned table: name one partition.

Parameters

column requiredstring

schema requiredstring

table requiredstring

max_rows optionalinteger

with value: read at most this many matching rows. Defaults to 200000, at most 5000000

sample_percent optionalnumber

without value: the share of the table's pages to sample, above 0 and at most 100. Defaults to whatever reads about ten thousand pages, and to 100 on a table smaller than that

value optionalany

the value whose rows to measure, as a string, number or boolean in the column's own text form; null measures the rows where the column is null. Omit it for the sampled form over the column's most common values

Also accepts connection, instance, group, described once under arguments every tool takes.

Output

How scattered the rows of a value are across a table's pages, exact for one value or sampled for the most common ones. An absent extension or a missing grant is reported as {error, hint} instead.

Scope

A physical replica is byte-identical here, so asking each member of a replication group adds nothing.

Example mocked data

Request

{
  "jsonrpc": "2.0",
  "id": 1,
  "method": "tools/call",
  "params": {
    "name": "rowScatter",
    "arguments": {
      "schema": "shop",
      "table": "orders",
      "column": "customer_id",
      "value": 48213
    }
  }
}

Result

{
  "schema": "shop",
  "table": "orders",
  "column": "customer_id",
  "table_pages": 22870,
  "mode": "exact",
  "rows": 1840,
  "pages": 1795,
  "max_rows": 200000,
  "capped": false,
  "rows_per_page": 52.7,
  "pages_if_packed": 35,
  "scatter_ratio": 51.3,
  "note": "rows of this value were read and the distinct pages they sit on counted; pages_if_packed is what the same rows would need at the table's average rows_per_page, and scatter_ratio is pages over that -- 1 is already packed."
}

Invented values on a fictional shop database, shaped by and checked against this tool's output schema. Real output is returned as structuredContent to clients that negotiate MCP 2025-06-18 or later.

See also

evaluateIndex, predicateStats, suggestIndexes, duplicateIndexes, tableBloat, indexBloat, checkKey, explainQuery