apstndb · October 5, 2020 11:17 · Oct 5, 2020 · Oct 5, 2020
diff --git a/0_render_spanner_plan_using_jq.md b/0_render_spanner_plan_using_jq.md
@@ -20,4 +20,26 @@ Predicates:
   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))
 ```
diff --git a/input.sql b/input.sql
@@ -1,3 +1,3 @@
 SELECT s.SongName, s.Duration
 FROM Songs@{FORCE_INDEX=SongsBySongName} AS s
-WHERE REGEXP_CONTAINS(s.SongName, "^A.*z")
+WHERE REGEXP_CONTAINS(s.SongName, "^A.*z");
diff --git a/input2.sql b/input2.sql
@@ -0,0 +1,8 @@
+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;
diff --git a/0_render_spanner_plan_using_jq.md b/0_render_spanner_plan_using_jq.md
@@ -0,0 +1,23 @@
+```
+$ 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)
+```
diff --git a/input.sql b/input.sql
@@ -0,0 +1,3 @@
+SELECT s.SongName, s.Duration
+FROM Songs@{FORCE_INDEX=SongsBySongName} AS s
+WHERE REGEXP_CONTAINS(s.SongName, "^A.*z")
diff --git a/plan.jq b/plan.jq
@@ -0,0 +1,46 @@
+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:", .[]
+)
No results found