Designing Agent Tools That Query the Database Without Full Scans

Treat the JSON schema as the contract that guarantees an indexable predicate. Use required for the columns that lead your index, enum where the value set is closed, and maximum/minimum on numeric ranges.

{
  "name": "list_orders",
  "description": "List orders for one tenant within a bounded date window. Always call this instead of writing SQL.",
  "parameters": {
    "type": "object",
    "additionalProperties": false,
    "required": ["tenant_id", "created_from", "created_to"],
    "properties": {
      "tenant_id": { "type": "string", "format": "uuid" },
      "created_from": { "type": "string", "format": "date" },
      "created_to": { "type": "string", "format": "date" },
      "status": { "type": "string", "enum": ["new", "paid", "shipped", "cancelled"] },
      "min_total": { "type": "number", "minimum": 0 }
    }
  }
}

Three details matter more than the field names. First, required is doing the index enforcement, so never mark the leading column optional "for flexibility". Second, additionalProperties: false stops the model inventing a filter column that has no index. Third, describe the cap in the tool description itself, because the model reads that text and will often self-correct before your server has to reject it.

Validate the range on the server, not in the schema alone. JSON Schema cannot express "no wider than 31 days" across two fields cleanly, so compute it in code after parsing. A model that receives a clear rejection learns the bound faster than one that receives a generic error.

Do not accept SQL text from the model when a parameterized builder will do. The builder receives validated arguments, emits a fixed statement shape, and applies the caps itself. This is the same discipline as static query verification: sqlc verify warns at build time when a query will produce a sequence scan, and you want the runtime equivalent.

WINDOW = timedelta(days=31)
HARD_LIMIT = 500

def build_orders_query(p):
    start, end = p["created_from"], p["created_to"]
    if end < start or (end - start) > WINDOW:
        raise ToolError("date_range_too_wide", max_days=31, got_days=(end - start).days)
    sql = """
      SELECT id, tenant_id, status, total, created_at
      FROM orders
      WHERE tenant_id = %(tenant_id)s
        AND created_at >= %(start)s
        AND created_at <  %(end)s
    """
    params = {"tenant_id": p["tenant_id"], "start": start, "end": end}
    if "status" in p:
        sql += " AND status = %(status)s"
        params["status"] = p["status"]
    sql += " ORDER BY created_at DESC LIMIT %(limit)s"
    params["limit"] = HARD_LIMIT
    return sql, params

The upper bound is exclusive (< rather than <=) so boundaries never double count, and the LIMIT is appended by the server, which means the model cannot widen it. Note that min_total is accepted by the schema but deliberately not pushed into SQL here: a filter on an unindexed column adds a heap check per row without narrowing the index scan. Either add it to a partial index or leave it out.

The decision rule for which parameters to push into SQL: only parameters whose columns appear in the index you intend the planner to use, plus columns you fetch anyway. Everything else is filtered after the bounded result set comes back, or promoted to a real index if agents ask for it often enough.

A date range is the easiest parameter for a model to widen. Asked for "orders from the last few months", a helpful agent may send a two year window, and a two year window on a large table is a sequential scan wearing a costume. The cap is what makes the predicate selective in practice, not just in theory.

Two more server-side settings are worth wiring up because they backstop everything above. Set a statement timeout on the role the agent uses, and consider default_transaction_read_only for read tools so a malformed call cannot write. PgBouncer tracks default_transaction_read_only by default, per the PgBouncer release notes, and transaction pooling keeps agent connections cheap since a server connection is only held for the duration of a transaction (PgBouncer features). If your agent runs many short tool calls, transaction pooling mode is the recommended configuration for exactly this traffic pattern (Heroku's PgBouncer guide).

Return truncation explicitly. If the builder added LIMIT 500 and the result contains exactly 500 rows, tell the model "truncated": true and include the window that was used, so it narrows the range instead of assuming it has the full picture.

The cap makes a bad plan unlikely, not impossible. Statistics can be stale, a tenant can genuinely dominate a table, or an index can be missing in one environment. Run EXPLAIN on the built statement and inspect the plan before executing the real query. Postgres emits a plan tree of scan nodes, and you can request machine-readable JSON output specifically so a program can inspect it (PostgreSQL EXPLAIN documentation).

def check_plan(conn, sql, params, big_tables={"orders", "events", "audit_log"}):
    with conn.cursor() as cur:
        cur.execute("EXPLAIN (FORMAT JSON) " + sql, params)
        plan = cur.fetchone()[0][0]["Plan"]
    for node in walk(plan):
        if node["Node Type"] == "Seq Scan" and node.get("Relation Name") in big_tables:
            raise ToolError(
                "plan_rejected",
                reason="sequential_scan",
                table=node["Relation Name"],
                hint="No usable predicate on an indexed column for this table.",
            )

Keep that check to EXPLAIN without ANALYZE on the request path, because ANALYZE actually runs the statement (Crunchy Data on EXPLAIN ANALYZE). Use EXPLAIN ANALYZE in tests and in the nightly suite, where you can afford to execute, and where you get actual row counts and timings to compare against estimates, as described in this guide to reading query plans. The Datadog writeup on debugging latency with EXPLAIN ANALYZE makes the same split: plans in production, plans plus measurements during investigation.

One caution on EXPLAIN alone: a small table legitimately scans sequentially, and Postgres can choose a scan when the filter matches most rows. Allow-list large tables rather than banning every sequential scan, or you will block correct queries on lookup tables and burn tokens on retries that cannot succeed.

A rejection that says "query rejected" teaches the model nothing and it will retry the same shape. Name the missing predicate, give the allowed values, and say what to do next. This is the highest-leverage string in the whole system.

{
  "error": "plan_rejected",
  "reason": "sequential_scan",
  "table": "orders",
  "missing_predicate": "tenant_id",
  "allowed": {
    "date_range_max_days": 31,
    "status": ["new", "paid", "shipped", "cancelled"]
  },
  "next_step": "Call list_orders again with tenant_id set and created_from/created_to inside a 31 day window."
}

Compare that with {"error": "invalid request"}. The first tells the model exactly which argument closes the window on the index and how wide the range may be. The second invites another guess, another rejected plan, and a latency hit on every attempt. Include the observed problem in the message: the table that was scanned, whether the range was too wide, or whether the LIMIT was missing. Agents converge quickly when the error payload contains the constraint.

Log every rejection with the tool name, the arguments, and the plan node that triggered it. That log is your index backlog: when the same missing predicate appears for a column repeatedly, add the index or add the filter to the builder. Do not let agents "learn" around a missing index by trying variations; fix the index once.

Some questions genuinely have no single-table index behind them: "how did revenue trend by week for the last year", "which customers have the most refunds". Forcing these through a row-level read tool either produces a slow sequential scan or a stream of rejected plans. Pick a fallback designed for the question instead of loosening the gate.

The rollup table is usually the cheapest win. A daily aggregate keyed by (tenant_id, day) is small, has an obvious index, and answers a year-long trend with a bounded range that the same range cap can express. If agents keep needing a column that is not in your index, that is a signal to change the index or the rollup, not to widen the tool. Note too that Postgres can answer a query from the index alone when every referenced column is stored in it, which removes the heap fetches and can turn a borderline scan into a fast path (PostgreSQL index-only scans). Designing a covering index for your most common agent tool is often easier than designing three.

For teams wiring several tools and several model providers behind one endpoint, routing every tool call through a single gateway keeps the schemas, caps and rejection format identical across models; that is what Quanta Inference is built to do. When agents need SQL-writing tools built against a specific schema and index set, an expert from Quanta Skills can scope it with you rather than you hand-rolling each builder.

Take orders with 40 million rows, 12,000 tenants, and an index on (tenant_id, created_at). The average tenant has about 3,300 rows, so a tenant_id equality plus a 31 day window is a handful of index entries.

• Without the predicate: sequential scan over 40 million rows. Using the ratio from the Crunchy Data example (about 560,000 rows filtered in roughly 130ms of scan time), that is tens of seconds and a large amount of shared buffer churn on every call.
• With tenant_id and a 31 day window: index scan touching roughly a few thousand entries at most, then a LIMIT 500 on top. Sub-millisecond to a few milliseconds.
• With tenant_id but a two year range: the index cond still applies to tenant_id, so the plan stays an index scan, but the agent receives mostly the same 500 rows it would have received anyway, now read across a much wider index range. The cap wastes work and hides rows the agent needed from the middle of the range.
• With the EXPLAIN gate on: the wide-range call is allowed (it is an index scan) but the response is flagged truncated, and the model is told to narrow the window. The no-tenant call is rejected before execution with missing_predicate: tenant_id.

The lesson from the fourth case is that plan checks and range caps cover different failures. The plan check catches missing predicates. The range cap and truncation flag catch predicates that exist but are too broad. You need both, which is why the release notes for connection poolers and query verifiers keep pointing at the same split between what you validate statically and what you cap at runtime.