Render Cloud Spanner Query Plan using jq

0_render_spanner_plan_using_jq.md

$ gcloud spanner databases execute-sql --project=${PROJECT_ID} --instance=${INSTANCE_ID} ${DATABASE_ID} \
     --sql "$(cat input.sql)" --query-mode=PLAN --format=json | jq -r -f plan.jq
 *0 Distributed Union
 *1   Distributed Cross Apply
  2     Create Batch
  3       Local Distributed Union
  4         Compute Struct
 *5           FilterScan
  6             Index Scan (Index: SongsBySongName)
 24     [Map] Serialize Result
 25       Cross Apply
 26         Batch Scan (Batch: $v2)
 31         [Map] Local Distributed Union
*32           FilterScan
 33             Table Scan (Table: Songs)
Predicates:
  0: Split Range: (STARTS_WITH($SongName, 'A') AND REGEXP_CONTAINS($SongName, '^A.*z'))
  1: Split Range: ($Songs_key_SingerId' = $Songs_key_SingerId)
  5: Seek Condition: STARTS_WITH($SongName, 'A')
     Residual Condition: REGEXP_CONTAINS($SongName, '^A.*z')
 32: Seek Condition: (($Songs_key_SingerId' = $batched_Songs_key_SingerId) AND ($Songs_key_AlbumId' = $batched_Songs_key_AlbumId)) AND ($Songs_key_TrackId' = $batched_Songs_key_TrackId)

$ gcloud spanner databases execute-sql --project=${PROJECT_ID} --instance=${INSTANCE_ID} ${DATABASE_ID} \
     --sql "$(cat input2.sql)" --query-mode=PLAN --format=json | jq -r -f plan.jq
 *0 Distributed Union
  1   Local Distributed Union
  2     Serialize Result
  3       Table Scan (Full scan: true, Table: Singers)
 14       [Scalar] Array Subquery
 15         Local Distributed Union
 16           Compute Struct
*17             FilterScan
 18               Table Scan (Table: Albums)
 31             [Scalar] Array Subquery
 32               Local Distributed Union
 33                 Compute Struct
*34                   FilterScan
 35                     Table Scan (Table: Songs)
Predicates:
  0: Split Range: true
 17: Seek Condition: ($SingerId_1 = $SingerId)
 34: Seek Condition: (($SingerId_2 = $SingerId_1) AND ($AlbumId_1 = $AlbumId))

input.sql

SELECT s.SongName, s.Duration
FROM Songs@{FORCE_INDEX=SongsBySongName} AS s
WHERE REGEXP_CONTAINS(s.SongName, "^A.*z");

input2.sql

SELECT si.*,
  ARRAY(SELECT AS STRUCT a.*,
        ARRAY(SELECT AS STRUCT so.*
              FROM Songs so
              WHERE a.SingerId = so.SingerId AND a.AlbumId = so.AlbumId)
        FROM Albums a
        WHERE a.SingerId = si.SingerId)
FROM Singers si;

plan.jq

def lpad(n): (" " * (n - (tostring | length))) + tostring;
def ispredicate: .type | strings | endswith("Condition") or . == "Split Range";

.. | .planNodes? | values |
. as $planNodes |
(map(select(.kind == "RELATIONAL") | .index // 0) | max | tostring | length) as $maxRelationalNodeIDLength |
# render tree part
(
  {} |
  recurse(
    {depth: ((.depth // 0) + 1), link: $planNodes[.link.childIndex // 0].childLinks[]};
    select($planNodes[.link.childIndex // 0].kind == "RELATIONAL" or .link.type == "Scalar")
  ) |
  {index: (.link.childIndex // 0), type: (.link.type // ""), depth: (.depth // 0)} as {$index, $type, $depth} |
  $planNodes[$index] |
  (.metadata.scan_type | rtrimstr("Scan")) as $scanType |
  {
    idStr: (if .childLinks | any(ispredicate) then "*\($index)" else $index end | lpad($maxRelationalNodeIDLength + 1)),
    displayNameStr: ( [.metadata.call_type, .metadata.iterator_type, $scanType, .displayName] | map(strings) | join(" ")),
    linkTypeStr: ($type | if . != "" then "[\(.)] " else . end),
    indent: ("  " * $depth // ""),
    metadataStr: (
      .metadata // {} |
      del(.["subquery_cluster_node", "scan_type", "iterator_type", "call_type"]) |
      to_entries |
      map(if .key == "scan_target" then .key = $scanType else . end | "\(.key): \(.value)") |
      sort |
      join(", ") |
      if . != "" then " (\(.))" else . end
    )
  } |
  "\(.idStr) \(.indent)\(.linkTypeStr)\(.displayNameStr)\(.metadataStr)"
),
# render predicates part
(
  map(
    .index as $nodeIndex |
    .childLinks // [] | map(select(ispredicate)) | to_entries[] |
    {
       type: .value.type,
       prefix: (if .key == 0 then "\($nodeIndex // 0):" else "" end),
       description: $planNodes[.value.childIndex].shortRepresentation.description,
    } |
    "\(.prefix | lpad($maxRelationalNodeIDLength + 2)) \(.type): \(.description)"
  ) | select(. != []) | "Predicates:", .[]
)