spanner-graph-query-tuning
Tunes and troubleshoots Spanner Graph on Google Cloud ISO GQL queries and execution plans. Use when optimizing slow Spanner Graph GQL queries, diagnosing distributed cross apply versus hash join execution operators and index seek prefixes, eliminating multi-arm star query Cartesian products with chained query aggregation, bounding super-node fan-out, decorrelating slow subqueries or optional matches, optimizing deep quantified variable-length paths, or applying join method, index, and path factorization query hints. Don't use for relational-to-property-graph schema mapping over existing relati
Try your first task
A source-grounded starter task has not been verified for this skill yet.
SKILL.md
name: spanner-graph-query-tuningmetadata: version: "1.0.0" category: Databasesdescription: >- Tunes and troubleshoots Spanner Graph on Google Cloud ISO GQL queries and execution plans. Use when optimizing slow Spanner Graph GQL queries, diagnosing distributed cross apply versus hash join execution operators and index seek prefixes, eliminating multi-arm star query Cartesian products with chained query aggregation, bounding super-node fan-out, decorrelating slow subqueries or optional matches, optimizing deep quantified variable-length paths, or applying join method, index, and path factorization query hints. Don't use for relational-to-property-graph schema mapping over existing relational tables (use spanner-relational-to-graph) or physical schema, table, and interleaving design (use spanner-graph-schema-best-practices).Spanner Graph Query Tuning Best Practices
Optimizing ISO GQL queries on Spanner Graph requires aligning pattern matching
and path aggregation with Spanner's distributed query execution engine. Small
syntactic choices in MATCH and NEXT statements can mean the difference
between a targeted index seek (SeekableKeySize: [N]) and a full multi-way
cross apply over millions of intermediate paths.
Usage Attribution
MUST READ: Before executing any gcloud, REST API, or MCP command, you MUST
read and follow the instructions in
Usage Attribution.
Prerequisites & Cloud Environment Setup
Before profiling or executing queries against a Spanner instance (see
spanner-basics for general Spanner instance provisioning and MCP setup):
- Google Cloud SDK (
gcloud) & Static Analysis Fallback: Verify whether the CLI is installed (do not installgcloudwithout explicit user permission; see Install the Google Cloud SDK). Ifgcloudor live Spanner database access is unavailable, do not block on executinggcloud spanner databases ddl describeor--query-mode=PROFILE—instead, perform static GQL query analysis and rewrites directly using the user-provided query, DDL, or execution plan and the bundledreferences/guides. - Project, Billing & API: Verify an active Google Cloud project with
billing enabled (required even when provisioning a free trial instance) is
configured (
gcloud config set project {project_id}) and, after the user confirms, enable the Spanner API (gcloud services enable spanner.googleapis.com). Ask the user before changing the active project, billing, or enabled APIs, because these commands change the user's Google Cloud environment and can incur costs. - Authentication & Identity and Access Management (IAM) Roles: Authenticate
via
gcloud auth loginorgcloud auth application-default login(orGOOGLE_APPLICATION_CREDENTIALSfor service accounts). Inspecting DDL and profiling queries requiresroles/spanner.databaseReader(orroles/spanner.databaseUser) on{database_id}. - Instance Region, Pricing & Teardown: For test instances, default to
regional-us-central1unless specified otherwise (see Spanner instance configurations, Google Cloud locations, Spanner pricing, and Google Cloud pricing). After benchmarking on temporary test resources, remind the user to delete temporary test databases (gcloud spanner databases delete {database_id} --instance={instance_id}) or test instances (gcloud spanner instances delete {instance_id}) so they do not incur ongoing Spanner compute and storage charges.
Closed-Loop Query Profiling Workflow
Never apply join or index hints blindly. Always inspect the database DDL,
profile the baseline query, apply structural GQL rewrites first, validate
compilation with PLAN, and re-profile to verify latency and scanned rows:
-
Fetch the schema DDL and profile the baseline query using
gcloud spanner databases ddl describeandgcloud spanner databases execute-sqlwith--query-mode=PROFILE(or--query-mode=PLANif the baseline times out):gcloud spanner databases ddl describe {database_id} --instance={instance_id} gcloud spanner databases execute-sql {database_id} \ --instance={instance_id} \ --query-mode=PROFILE \ --sql="GRAPH MyGraph MATCH ... RETURN ..." -
Inspect key execution plan metrics (detailed in references/profile-and-hints-guide.md):
- Join Type: Check whether traversal steps use
Distributed Cross Apply(nested-loop index seeks per input row) orHash Join(set-based scan + build/probe). SeekableKeySizevs.Residual Condition: In eachTableScanorIndexScan, check whether predicates are pushed down intoSeekableKeySize: [N](index key seek) or evaluated after scanning as aResidual Condition.- Row Amplification (
Rows Returnedvs.Storage Read): Identify whichMATCHhop causes combinatorial path explosion before a downstream filter orGROUP BY.
- Join Type: Check whether traversal steps use
-
Apply structural GQL rewrites first (see Core ISO GQL Query Rewrite Rules and references/gql-rewrite-patterns.md) and validate syntax/compilation quickly with
--query-mode=PLAN. -
Apply statement or element hints only if the optimizer chooses a suboptimal physical plan (see Hint Selection and Guardrails), and re-run
--query-mode=PROFILEto confirm improvement. If a hint degrades latency, revert it immediately.
Core ISO GQL Query Rewrite Rules
Before adding optimizer hints, restructure the GQL query so the cardinality stays small at every step:
Rule 1: Start Traversals from the Lowest-Cardinality Anchor Node
Order your MATCH patterns so the most selective filter (such as
WHERE n.id = @id or a selective indexed property) appears first in the
pattern chain:
-
Prefer:
GRAPH FinGraph MATCH (p:Person {id: @person_id})-[o:Owns]->(a:Account)-[t:Transfers]->(b:Account) RETURN a.id AS account_id, b.id AS to_account_id, t.amount -
Avoid: Placing unselective patterns first or writing disconnected comma-separated patterns, which can delay selectivity application:
GRAPH FinGraph MATCH (a:Account)-[t:Transfers]->(b:Account), (p:Person {id: @person_id})-[o:Owns]->(a:Account) RETURN a.id AS account_id, b.id AS to_account_id, t.amount
Rule 2: Eliminate Multi-Arm Star Query Cartesian Explosions (3 Patterns)
When a query traverses multiple independent 1:N arms from a hub node (for
example, fetching a consumer's emails, phones, addresses, and accounts, or a
router's interfaces and alarms) in a single comma-separated MATCH, Spanner
produces an $M \times N \times K$ Cartesian product (or sequential
DistributedOuterApply joins with OPTIONAL MATCH). Choose one of three
remedies based on the required output shape:
- Chained
NEXT+ Early Aggregation or NestedARRAY { MATCH ... }Subqueries: Collapse each arm to 1 row per anchor node viaGROUP BYacrossNEXTstages, or collect each 1:N arm into an array column. Subqueries cannot redeclare an outer graph variable, so bind a new variable and compare it to the outer one (ARRAY { MATCH (n2)-[:HasEmail]->(e:Email) WHERE n2 = n RETURN e.addr }). - Factorized Execution Hint (
@{factorized_mode=factorize_both}): When flat cross-product output rows are genuinely needed, attach@{factorized_mode=factorize_both}to the conditionally independent arms (MATCH (a)-[e1]->(a1), @{factorized_mode=factorize_both}(a)-[e2]->(a2)) so Spanner evaluates each branch once ($M + N + K$) before forming the final result. Return per-arm columns; withRETURN COUNT(*)Spanner rejects the hint (A join in the query cannot be executed in factorized execution mode). Because the final output still materializes the cross product, the hint usually gains little, while remedy 1 is much faster for aggregated results; prefer remedy 1 and keep the hint only ifPROFILEshows a gain. - Client-Side Parallel Query Decomposition: When returning wide property
lists across 4+ independent entity types for a cluster of anchor nodes,
execute Query 1 to fetch the anchor/cluster node IDs, then issue concurrent
1-hop queries (
WHERE n.id IN UNNEST(@anchor_ids)) for each attribute branch in parallel.
Rule 3: Always Specify Node and Edge Labels Across Every MATCH and NEXT Block
In schemas with multiple node or edge tables (polymorphic labels), omitting a
label (MATCH (a)-[e]->(b)) forces Spanner to generate a Distributed Union
across all node and edge tables in the property graph.
- Even more critical across
NEXT: When passing a node variableninto a subsequentNEXT MATCH (n)-[e:EdgeLabel]->(m:TargetLabel)stage, re-specify the label on the bound node variable (MATCH (n:SourceLabel)-[e:EdgeLabel]->(m:TargetLabel)). Omitting:SourceLabelonnin the secondMATCHcan cause Spanner to scan unrelated node tables that share the same edge label.
Rule 4: Avoid Redundant NEXT Self-Joins When Edge Endpoints Are Already Available
When a 1:1 or N:1 relationship has been folded into a node table
(Table AS Edge), or when you only need the destination key that is already
stored on the source node or edge row, do not chain an extra NEXT MATCH hop
that re-scans the same table by primary key just to project a key column or
check existence. Project the property directly in the initial MATCH stage.
Rule 5: Bound Super-Node Fan-Out with IS_FIRST or Hub Exclusion, and Avoid Unbounded OPTIONAL MATCH + LIMIT
High-degree "super-nodes" (hub nodes with tens of thousands of incident edges) can dominate query latency when a multi-hop traversal expands every neighbor.
-
Per-hop edge sampling inside quantified paths: Use
FILTER IS_FIRST(@max_edges) OVER (PARTITION BY ...)withSOURCE_NODE_ID(selected_e)inside an edge subquery:GRAPH FinGraph MATCH (a1:Account {is_blocked: true}) -[e:Transfers WHERE e IN { MATCH -[selected_e:Transfers]-> FILTER IS_FIRST(@max_edges) OVER ( PARTITION BY SOURCE_NODE_ID(selected_e) ORDER BY selected_e.create_time DESC) RETURN selected_e }]->{1,5}(a2:Account) RETURN a1.id AS src_id, a2.id AS dst_id -
Single-hop or midpoint sampling: Use
FILTER IS_FIRST(@k) OVER (PARTITION BY a.id)between hops. -
Deterministic hub exclusion inside quantified paths: When hub nodes (such as exchanges, payment processors, or shared system accounts) are not meaningful intermediaries, precompute a boolean
is_hubflag on the node table and filter the expansion node at each step:MATCH (src:Account {id: @id})((a:Account WHERE a.is_hub = FALSE)-[e:Transfers]->(b:Account)){1,3}(dst:Account). BothIS_FIRSTand hub exclusion intentionally change result semantics to bound fan-out:IS_FIRSTcaps per-node degree by sampling edges (non-deterministic only whenORDER BYis omitted or has ties), whereas hub exclusion drops paths through hubs while deterministically returning all non-hub paths and avoiding hub neighborhood expansion at every hop. Note that hub exclusion requires maintainingis_huband adds a node-property lookup per hop. -
OPTIONAL MATCH+ORDER BY+LIMITPitfall: Spanner cannot pushLIMITor index sort order through theLEFT OUTER JOIN(DistributedOuterApply) generated byOPTIONAL MATCH. Bound the optional expansion inside anIS_FIRSTor anARRAY { MATCH (a2)-[e]->(x) WHERE a2 = a ORDER BY ... LIMIT k RETURN ... }subquery, or useMATCHwhen optionality is not required.
Rule 6: Seek Prefix Keys Instead of Post-Filtering
Ensure WHERE predicates match the leading columns of the node/edge primary key
or secondary index so the execution plan shows SeekableKeySize: [1] (or [N])
rather than SeekableKeySize: [0] with a Residual Condition. Avoid wrapping
key columns in non-sargable functions (such as CAST(n.id AS STRING) = @id or
LOWER(n.code) = @code) unless backed by a generated column index.
Rule 7: Optimize Deep Quantified Paths ({m, n}) via Lightweight Recursion, Late Materialization, and factorize_left
- Do not reference intermediate node group variables inside deep quantified
paths: Unreferenced intermediate node variables in
((v:Node)-[e:Edge]->(t:Node)){1, 10}are pruned automatically, but referencingvort(even forARRAY_TRANSFORM(t, n -> n.id), or bindingMATCH p = ...and callingNODES(p)) forcesRecursiveScanto scanNodeat every hop and accumulate fullARRAY<GRAPH_NODE>structs (with allNodecolumns) in the recursion buffer. Instead, read intermediate endpoint keys from the edge group variable (ARRAY_TRANSFORM(e, el -> el.to_id)). - Defer wide property lookups across multi-stage queries (late
materialization): When grouping, ranking (
ORDER BY ... LIMIT), or filtering reachable nodes acrossNEXTstages on wide node tables, carry only lightweight key columns (dst.id, satisfied frome.to_idvia foreign-key join elimination without scanningNode) through intermediate stages, and join(dst:Node)in the finalNEXTstage to fetch wide columns only for surviving keys. - Deduplicate converging paths before target node lookup
(
@{factorized_mode=factorize_left}): When many multi-hop paths converge on the same destination nodes, attach@{factorized_mode=factorize_left}before the destination node (-[e:Transfers]->{1,3}@{factorized_mode=factorize_left}(a2:Account)) so Spanner deduplicates destination keys before probing theAccounttable. Return per-path or destination columns: withRETURN COUNT(*)Spanner rejects the hint (A join in the query cannot be executed in factorized execution mode). The payoff grows with the convergence ratio (at moderate convergence it mainly reduces destination lookups and rows scanned, with little change in latency), so confirm the gain withPROFILE.
Rule 8: Avoid ARRAY_AGG with IN UNNEST and Correlated EXISTS Subquery Anti-Patterns
- Never pass node sets across
NEXTviaARRAY_AGG(n.id)+WHERE n.id IN UNNEST(ids): Aggregating IDs into an array and unnesting them in the next stage forces a full table scan with per-rowArrayUnnest. Pass the bound node variable directly (RETURN DISTINCT n NEXT MATCH (n:Label)...). - Decorrelate
EXISTS { MATCH ... }subqueries viaOPTIONAL MATCH: CorrelatedEXISTS/NOT EXISTSsubqueries insideWHERE(especially insideORdisjunctions or afterOPTIONAL MATCH) often execute row-by-row viaCrossApply. Note that a subquery cannot redeclare an outer graph variable (NOT EXISTS { MATCH (a)-[:Transfers]->(b) }fails withredefining graph element variables in a subquery is not allowed), and the accepted formNOT EXISTS { MATCH (a2)-[:Transfers]->(b) WHERE a2 = a }runs as an unpushed correlatedAnti-Semi Applythat re-scans its node table for every outer row. RewriteNOT EXISTSdirectly intoOPTIONAL MATCH (a)-[:Transfers]->(b) FILTER b IS NULL(noNEXTneeded unless following an aggregation likeGROUP BY), which compiles into a set-based hash anti-semi join. For positiveEXISTS, useMATCHand deduplicate withFILTER IS_FIRST(1) OVER (PARTITION BY ...)orDISTINCTif multiple edges can match per outer row.
GQL Semantic & Syntax Guardrails
All GQL query rewrites must preserve these syntax and semantic invariants:
WHEREvs.FILTERwithOPTIONAL MATCH: Place predicates on optional pattern variables insideOPTIONAL MATCH ... WHERE ...(evaluated during the outer join). Placing them in a trailingFILTERclause filters the post-join table and silently turns the outer join into an inner join.ORDER BYPlacement: In GQL,ORDER BYis ignored unless it is the final clause of the query (RETURN ... ORDER BY score DESC LIMIT 10) or is immediately followed byLIMIT(ORDER BY score DESC LIMIT 10 RETURN ..., notORDER BY score DESC RETURN ... LIMIT 10).- Returning Graph Elements (Final Top-Level
RETURNOnly): Returning raw node, edge, or path variables (RETURN n) fails withReturning expressions of type GRAPH_ELEMENT is not allowedonly whenRETURNis the final statement of the top-level GQL query, because Spanner cannot returnGRAPH_ELEMENTcolumns directly to the client. IntermediateRETURN n NEXT ...stages,WITH n, and subqueryRETURNs (such ase IN { MATCH -[selected_e:Transfers]-> ... RETURN selected_e }) do allow raw graph elements—pass them directly and do not wrap intermediate variables inTO_JSON(n). Project explicit properties (RETURN n.id, n.name) orTO_JSON(n)only in the final top-levelRETURN. - Unique Output Column Names (
RETURNandWITH): Every column in aRETURNorWITHstatement needs a distinct name.RETURN a.id, b.idorWITH a.id, b.idfails withAmbiguous name: id; alias duplicates (RETURN a.id AS src_id, b.id AS dst_id). - Reserved Words as Labels:
Order,Group, andContains(among other GQL/SQL keywords) must be escaped in backticks when used as labels or aliases ((o:`Order`),-[:`Contains`]->). - Carrying Values Across
NEXT: A column produced by a previous statement can be referenced only in the outermostWHEREof the nextMATCH: writeMATCH (dst:Part) WHERE dst.part_id = target_part_id, notMATCH (dst:Part {part_id: target_part_id}).
Hint Selection and Guardrails
Use hints only after verifying with --query-mode=PROFILE that the default
optimizer plan is suboptimal. Full syntax and operator diagnostics are in
references/profile-and-hints-guide.md.
Hint placement (verified on Spanner): element hints go first inside the
node or edge pattern ((@{FORCE_INDEX=Idx} n:Label {...}),
-[@{FORCE_INDEX=Idx} e:Label]->); JOIN_METHOD is a traversal hint placed
between elements (-[e:Label]-> @{JOIN_METHOD=HASH_JOIN} (m:Label)) or at
statement level before GRAPH. Writing a hint after the label
(-[e:Label @{...}]->) is a syntax error.
| Workload Pattern | Recommended Hint Strategy | Why |
|---|---|---|
| Selective Anchor Traversal (1 or a few starting nodes, 1–3 hops) | Default optimizer plan (Distributed Cross Apply), or element hint (@{FORCE_INDEX=IdxName} n:Label ...) if the wrong index is chosen | Apply Join performs targeted B-tree point seeks (SeekableKeySize >= 1) proportional only to the anchor fan-out. |
| Unselective / Bulk Graph Traversal (scanning a large fraction of nodes/edges across the graph) | Statement hint @{JOIN_METHOD=HASH_JOIN} before GRAPH, or traversal hint -[e:Label]-> @{JOIN_METHOD=HASH_JOIN} (m:Label), only if PROFILE shows a Distributed Cross Apply probing millions of rows | Replaces millions of random per-row RPC index probes (Distributed Cross Apply) with sequential table/index scans and in-memory hash probes. The optimizer often picks a Hash Join for bulk scans automatically (where forcing APPLY_JOIN is slower). |
Selective Reverse Traversal (<-[]-) | <-[@{FORCE_INDEX=EdgeReverseIdx} e:EdgeLabel]- (optionally with traversal hint @{JOIN_METHOD=APPLY_JOIN}) | Forces Spanner to seek the reverse secondary index on (dest_id, src_id) rather than scanning the forward edge table. |
| Converging Linear / Quantified Paths | -[e:Edge]->{1,3}@{factorized_mode=factorize_left}(dst:Node) (return per-path or destination columns, not COUNT(*)) | Deduplicates converging paths by destination key before looking up dst node properties. Pays off only at high convergence ratios; verify with PROFILE. |
| Multi-Arm Star Pattern (when flat cross-product output is needed) | MATCH (a)-[e1]->(n1), @{factorized_mode=factorize_both}(a)-[e2]->(n2) (return per-arm columns, not COUNT(*)) | Evaluates conditionally independent arms once ($M + N$) instead of via nested loops ($M \times N$). Prefer Rule 2 remedy 1 unless flat cross-product rows are required and PROFILE shows a gain. |
Critical Hint Guardrails (When NOT to Use Hints)
- Do NOT force
@{JOIN_METHOD=HASH_JOIN}on quantified variable-length path patterns (-[e:Edge]->{1,3}) or when foreign-key / schema optimizations allow Spanner to eliminate intermediate node table joins. Overriding the join method inside quantified paths forces full-table build/probe scans at every recursion depth instead of expanding only the active frontier, which is much slower on anchored{1,3}traversals. - Do NOT force
@{JOIN_METHOD=HASH_JOIN}on highly selective point lookups without an anchoring filter on the build side; aHash Joinwithout a selective build-side filter may scan the entire target table. - Always verify with
PROFILE: If a hint increasesTotal elapsed timeorRows scanned, remove it immediately.
Reference Guides
- GQL Rewrite Patterns: Detailed
before-and-after ISO GQL query transformations for anchor ordering, chained
NEXTearly aggregation, label enforcement acrossNEXT, folded-edge projection,IS_FIRSTand hub-exclusion super-node bounding,OPTIONAL MATCHsubquery decorrelation, and quantified-path lightweight recursion and late materialization. - PROFILE and Hints Guide: How to
read
gcloud spanner databases execute-sql --query-mode=PROFILEoutput (Distributed Cross Apply,Hash Join,SeekableKeySize,Residual Condition) and exact syntax for statement-level, node-level, and edge-level hints. - Official Spanner Graph Query Tuning Documentation: Spanner Graph query tuning best practices
If you need product information that's not found in these references, use the
search_documents tool of the Developer Knowledge MCP server.