Explain

POST /projects/{uid}/branches/{branch}/explain takes exactly the request /sql takes and returns everything /sql returns plus the plan: how long each phase took and the tree of nodes the statement was assembled from. Use it when the SQL is not what you expected, or to show your users where a number comes from.

Request

Same envelope, same auth, same ContextRequired rule:

curl -s https://app.0sql.io/projects/tpcds/branches/main/explain \
  -H "Authorization: Bearer zqk_..." \
  -H "Content-Type: application/json" \
  -d '{"spec": {"projections": [
        {"field": "ws_net_paid", "alias": "Web Net Paid"},
        {"field": "ws_sold_date", "alias": "Web Sold Date"},
        {"field": "net_paid", "alias": "Net Paid"}]}}'

zsql explain --expr 'ws_net_paid as Web Net Paid, ws_sold_date as Web Sold Date, net_paid as Net Paid'
zsql sql --explain --spec query.json

Response

All of the /sql fields (sql, datasource, datasource_uid, adapter, optional corrections, and spec + items for an expr), plus:

Phase names: resolve, segment_fork, complex_measure, exclusion, inclusion, snapshot, contribution, temporal, top_n, segment, security (only when a context was sent), reference, alias, sql, final_query.

Node fields

A worked response

The cross-fact blend from the Query API page, abbreviated: long strings are shortened and only the fields that carry information for this plan are shown.

{
  "sql": "WITH ag7098b0d0901f2eb16d14f9356f0bb2a0 AS (...), ag29130fae5cf6548d6ddb1bd37298b407 AS (...) SELECT ... FULL OUTER JOIN ...",
  "datasource": "Warehouse",
  "datasource_uid": "warehouse",
  "adapter": "postgres",
  "timings": {"parser_us": 41, "plan_us": 312, "total_us": 353},
  "phases": [
    {"name": "resolve", "us": 180}, {"name": "segment_fork", "us": 2}, {"name": "complex_measure", "us": 3},
    {"name": "exclusion", "us": 1}, {"name": "inclusion", "us": 1}, {"name": "snapshot", "us": 1},
    {"name": "contribution", "us": 1}, {"name": "temporal", "us": 1}, {"name": "top_n", "us": 1},
    {"name": "segment", "us": 1}, {"name": "reference", "us": 9}, {"name": "alias", "us": 6},
    {"name": "sql", "us": 88}, {"name": "final_query", "us": 17}
  ],
  "nodes": [
    {"id": 1, "alias": "ag7098b0d0901f2eb16d14f9356f0bb2a0", "kind": "aggregation", "root": false,
     "table": "store_sales", "datasource": "warehouse",
     "paths": ["store_sales", "store_sales -> item"],
     "projections": ["Web Sold Date", "Net Paid"], "filters": ["category in_list men ,children, \"books,com\""],
     "inputs": [], "group_by": true, "security_filters": [], "security_masks": [], "identity": "7098b0d0..."},
    {"id": 2, "alias": "ag29130fae5cf6548d6ddb1bd37298b407", "kind": "aggregation", "root": false,
     "table": "web_sales", "datasource": "warehouse",
     "paths": ["web_sales", "web_sales -> item"],
     "projections": ["Web Net Paid", "Web Sold Date"], "filters": ["category in_list men ,children, \"books,com\""],
     "inputs": [], "group_by": true, "security_filters": [], "security_masks": [], "identity": "29130fae..."},
    {"id": 3, "alias": "final", "kind": "merge", "root": true,
     "projections": ["Web Net Paid", "Web Sold Date", "Net Paid"], "filters": [],
     "inputs": [2, 1], "strategies": [{"node": 2, "kind": "base"}, {"node": 1, "kind": "join"}],
     "join_type": "full", "group_by": false, "security_filters": [], "security_masks": []}
  ]
}

Reading it:

• Two aggregation nodes, one per fact table, each with its own table, paths and the shared category filter. That is why the SQL has two ag… CTEs: the measures live on different facts and must be summed separately before they can sit on one row.
• The merge node is the root. Its inputs are the two aggregations and join_type: "full" is the FULL OUTER JOIN in the final pass (the final_pass_measure_join_type dialect setting, overridable through db_settings).
• No security phase ran and every security_filters list is empty: the request carried no context and the branch has no policies.

What to look for

Why a blend split. More than one aggregation node, each with a different table, means the projected measures could not be served from one fact at one grain. The projections of each node show which measures went where. If you expected a single node, check the model’s joins and blending group; see Extended blending groups.

Which table was routed. table and paths on each node show the resolver’s choice among the tables that could answer the query, driven by table cost and partitions. To force a route, pass the table uid in the spec’s hints. See Universe formation and Cost optimization.

What a policy added. With a context, the security phase appears and each node that fired a policy lists its security_filters (the WHERE LOWER(...) IN (...) added) and security_masks (the fields wrapped in CASE). A policy that fires inside a segment or aggregation CTE shows on that node, not on the root. If a node lists a filter you did not expect, the field it projects carries a trigger tag. See Security context.

Where a decorator went. Contribution totals, shifted temporal sides and top-n rankings each get their own node with a purpose (and a transform for temporal nodes), so you can see the extra CTE the decorator cost and what it groups by.

zsql explain

The CLI prints the SQL first, then the tree:

$ zsql explain --expr 'ws_net_paid as Web Net Paid, ws_sold_date as Web Sold Date, net_paid as Net Paid'
-- datasource: Warehouse
WITH ag7098b0d0901f2eb16d14f9356f0bb2a0 AS (
...
)

-- 3 nodes
merge final (root) join full
  aggregation ag29130fae5cf6548d6ddb1bd37298b407 web_sales
  aggregation ag7098b0d0901f2eb16d14f9356f0bb2a0 store_sales
-- server 353 us: parser 41 us, plan 312 us
-- phases (us): resolve 180, segment_fork 2, ..., sql 88, final_query 17

Any fuzzy corrections print first as term → Field Name (~score). --json prints the raw response instead. In the repl, .explain <line or json> does the same.

Next steps

• Discovery for what a query can still add
• Security context
• Universe formation