relational-query-processor

---

name: relational-query-processor description: Specialized skill for working in the fdb-relational-core SQL processing layer — parser, plan generator, and Cascades planner. Use when debugging query plans, writing planner rules, or understanding the SQL execution path. Some example usages: "Why is this query doing a full table scan?" "Explain this EXPLAIN output" "How do I write a new Cascades rule?" "Why is my query not using the index?"

This skill provides context for working in fdb-relational-core, the SQL processing layer of the Record Layer — covering the parser, plan generator, and Cascades planner.

Key entry points

Class	Role
EmbeddedRelationalStatement	Main SQL statement implementation. executeGet → point read via source.get(); executeScan → range scan via source.openScan().
RelationalDirectAccessStatement	Bypasses the SQL planner entirely — goes directly to FDB.
RecordLayerEngine	Engine setup; manages the connection between JDBC and the record layer.
RelationalPlanCache	Caches compiled query plans. Must be initialized for SQL performance — RelationalPlanCache.buildWithDefaults(). Without it, every query runs the full Cascades planner.

SQL execution path (normal case)

JDBC call
  → EmbeddedRelationalStatement
    → SQL parse + Cascades planner (on cache miss)
      → LogicalQueryPlan.optimize()
        → Simplification.executeRuleSetIteratively()   ← only on cold miss
    → RelationalPlanCache (warm hit: wraps existing plan)
      → PhysicalQueryPlan.withExecutionContext()
        → executePhysicalPlan()
          → FDB read

On a warm cache hit, simplification is skipped entirely. The planner overhead is ~0.3–0.5 ms per call.

Reading EXPLAIN output

Run EXPLAIN <query> via JDBC or in a yaml test:

EXPLAIN SELECT * FROM t WHERE id = 1

Common plan shapes:

• COVERING(idx_name [EQUALS ?] → [...]) — index-only scan, no fetch needed.
• FETCH(COVERING(...)) — index scan followed by a record fetch.
• SCAN(<,>) | FILTER ... — full table scan with a post-scan filter. This usually means the planner could not match a primary key or index scan candidate.
• MAP (_.col AS col) — projection step.

Writing a yaml test that checks the plan

- query: explain select * from t where id = 1
  explain: "COVERING(idx_id [EQUALS ?] → [id: KEY[0], name: VALUE[0]])"

Use @MaintainYamlTestConfig(YamlTestConfigFilters.CORRECT_EXPECTATIONS) on the test method to have the framework fill in the explain: value automatically on first run.

Debugging a plan interactively

Add @DebugPlanner to the @YamlTest method to launch PlannerRepl, which lets you step through the Cascades optimization phases.

Cascades planner overview

The planner is a Cascades-style rule-based optimizer:

1. Logical rules transform the logical plan (e.g., push predicates into index scans).
2. Physical rules convert logical operators to physical plans (e.g., select index vs. scan).
3. Match candidates (e.g., PrimaryScanMatchCandidate, ValueIndexScanMatchCandidate) determine which access paths are available for a given predicate.
4. Simplification runs Derivations.simplifyLocalValues() — only on cold cache misses.

If a query is doing a full table scan when you expect an index scan, common causes:

• The predicate is on a non-indexed column.
• The index exists but the match candidate is not recognizing the predicate shape (check that the column names and types match exactly).
• The RelationalPlanCache is not initialized (every call looks like a cold miss).

Transaction lifecycle in tests

With autoCommit=true, ensureTransactionActive() rolls back and restarts the transaction on every statement, clearing per-transaction caches. Both SQL and direct-access paths share this overhead in benchmarks.

Schema templates

Schema templates persist in FDB across JVM runs. If you see unexpected state in tests, the template may be stale. Creation code should be idempotent (drop-then-create pattern).