predicateStats

read-onlyany connectionsweeps instancesweeps replication groupsweeps group

Synopsis

predicateStats([limit], [order_by], [query_id])

Description

Return the WHERE and JOIN predicates pg_qualstats has sampled in this database, one entry per relation, column, operator and way of evaluating it: how often it ran, how many rows it was evaluated against, how many it filtered out, and how far the planner's selectivity estimate was from what happened. evaluated_as "filter" is a predicate applied to rows after they were fetched -- a high filtered_pct there is rows read only to be thrown away, which is what an index would avoid; "index" means an index already serves it. mean_err_estimate_ratio is the factor by which the planner's row estimate missed -- 166 when 60 rows were expected and 10,000 arrived -- and 0 when it did not miss; mean_err_estimate_num is the same miss in rows.

A large factor is the planner misjudging selectivity, which ANALYZE, a larger statistics target or extended statistics (listExtendedStatistics) fixes and an index does not. NEVER returns a constant: a predicate reads table.column op ?, and pg_qualstats' constvalue column and example-query functions are not read. query_ids joins to statementStats, at most 10 per predicate with queries giving the full count. pg_qualstats samples one query in pg_qualstats.sample_rate (by default 1/max_connections), so counts are samples rather than totals.

Requires pg_qualstats in shared_preload_libraries. Without it the extension does not fail -- it silently reports only the calling session's own predicates -- so this tool detects that from the extension's own settings, which any role can read, and refuses rather than return an empty answer that looks like a real one.

Parameters

limit optionalinteger: how many predicates to return. Defaults to 20, at most 500
order_by optionalstring: ranking: filtered (default, rows removed), execution_count, occurrences, or err_estimate_ratio (the planner's worst selectivity misestimate)
query_id optionalstring: only predicates of this statement, as a decimal string from statementStats

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

Output

Per-predicate statistics from pg_qualstats, without constants. An absent extension or a missing grant is reported as {error, hint} instead.

Field	Type
order_by	string \| null
predicates	array \| null
settings	object \| null

Scope

The counters here are each server's own, so members of a replication group legitimately disagree and the answer is their sum, not the primary's copy.

Example mocked data

Request

{
  "jsonrpc": "2.0",
  "id": 1,
  "method": "tools/call",
  "params": {
    "name": "predicateStats",
    "arguments": {
      "limit": 2
    }
  }
}

Result

{
  "predicates": [
    {
      "predicate": "shop.orders.status = ?",
      "relation": "shop.orders",
      "column": "status",
      "operator": "=",
      "join": false,
      "evaluated_as": "filter",
      "occurrences": 41022,
      "execution_count": 1840220331,
      "filtered": 1801992101,
      "filtered_pct": 97.9,
      "filtered_per_call": 43927,
      "mean_err_estimate_ratio": 1.4,
      "max_err_estimate_ratio": 3.1,
      "mean_err_estimate_num": 212.5,
      "queries": 2,
      "query_ids": [
        "-8811029349218800117",
        "5520184417300021190"
      ]
    },
    {
      "predicate": "shop.orders.customer_id = ?",
      "relation": "shop.orders",
      "column": "customer_id",
      "operator": "=",
      "join": false,
      "evaluated_as": "index",
      "occurrences": 44102,
      "execution_count": 882046,
      "filtered": 0,
      "filtered_pct": 0.0,
      "filtered_per_call": 0,
      "mean_err_estimate_ratio": 0,
      "max_err_estimate_ratio": 0,
      "mean_err_estimate_num": 0,
      "queries": 1,
      "query_ids": [
        "3301928477102934411"
      ]
    }
  ],
  "order_by": "filtered",
  "settings": {
    "sample_rate": "0.01",
    "enabled": "on",
    "max": "1000"
  },
  "preloaded": true,
  "extension_version": "2.1.4"
}

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.