cockroachdb / cockroachdb/cockroach

sql: show stmt plans within PL/pgSQL control flow in EXPLAIN ANALYZE

Open
#170,568 1 comment 0 reactions 1 assignee Claimed by @ZhouXing19 View on GitHub
A-sql-routine branch-release-26.3 C-enhancement T-sql-queries
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

PL/pgSQL control flow (IF/ELSE, loops, exception handlers) is only partially visible in EXPLAIN ANALYZE. Since #171758, sub-routine sections appear with invocation counts, but they use compiler-generated names with no link to the source construct -- and IF branches usually don't appear at all.

### Example 1: exception handler

```sql
CREATE TABLE t (a INT PRIMARY KEY, b INT);
INSERT INTO t SELECT i, i*10 FROM generate_series(1, 10) AS g(i);

CREATE FUNCTION safe_divide(a INT, b INT) RETURNS INT AS $$
BEGIN
RETURN a / b;
EXCEPTION WHEN division_by_zero THEN
RETURN 0;
END;
$$ LANGUAGE PLpgSQL;

EXPLAIN ANALYZE SELECT safe_divide(a, a-5) FROM t WHERE a IN (4, 5, 6);
```

Today (routine sections only, plan trees elided):
```
• routine name: safe_divide invocations: 3
• routine name: exception_handler_1 invocations: 1
• routine name: nested_block_3 invocations: 3
```
The exception-path count is technically present, but nothing ties `exception_handler_1` to `safe_divide` or to `division_by_zero`. With two WHEN arms the sections are `exception_handler_1` / `exception_handler_3` -- indistinguishable. Two functions with handlers in one query each emit a section named `exception_handler_1`. Savepoint activity is invisible.

Expected:
```
• routine: safe_divide (3 invocations)
│ exception handler (division_by_zero): 1 invocation
│ savepoints created: 3, rolled back: 1
```

### Example 2: IF/ELSE

```sql
CREATE FUNCTION if_lookup(x INT) RETURNS INT VOLATILE LANGUAGE PLpgSQL AS $$
DECLARE result INT;
BEGIN
IF x > 5 THEN
SELECT b INTO result FROM t WHERE a = x;
ELSE
result := -1;
END IF;
RETURN result;
END
$$;

EXPLAIN ANALYZE SELECT if_lookup(a) FROM t; -- 5 rows per branch
```

Today:
```
• routine name: if_lookup invocations: 10
```
No branch info at all -- and the THEN branch's point-scan ran 5 times (header shows `rows decoded from KV: 15` = 10 outer + 5 in-routine) yet its plan appears nowhere in the output. This is specific to branch statements that assign INTO a variable (the typical branch content): a branch with a bare INSERT does surface as a section with the branch-taken count -- cryptically named, like Example 3's sections.

Expected:
```
• routine: if_lookup (10 invocations)
│ branch (x > 5): 5 taken
│ branch (ELSE): 5 taken
```

### Example 3: loops

```sql
CREATE FUNCTION insert_n(n INT) RETURNS INT VOLATILE LANGUAGE PLpgSQL AS $$
BEGIN
FOR i IN 1..n LOOP
INSERT INTO sink VALUES (i);
END LOOP;
RETURN n;
END
$$;

EXPLAIN ANALYZE SELECT insert_n(4);
```

Today, one FOR loop produces three cryptically named sections:
```
• routine name: insert_n invocations: 1
• routine name: stmt_loop_4 invocations: 5
• routine name: stmt_loop_inc_5 invocations: 4
• routine name: _stmt_exec_7 invocations: 4
```
The iteration count is derivable, but the invocations -> iterations mapping differs by loop form (WHILE/FOR include the terminating condition check; `LOOP … EXIT WHEN` does not), and nothing labels these as a loop.

Expected:
```
• routine: insert_n (1 invocation)
│ loop: 4 iterations
└── • body stmt (INSERT INTO sink ...) <- per-iteration plan, 4 invocations
```

### Example 4: subqueries in the IF condition

```sql
CREATE FUNCTION cond_count(x INT) RETURNS INT VOLATILE LANGUAGE PLpgSQL AS $$
DECLARE result INT;
BEGIN
IF (SELECT count(*) FROM t) > 5 THEN
result := 1;
ELSE
result := 0;
END IF;
RETURN result;
END
$$;

EXPLAIN ANALYZE SELECT cond_count(a) FROM t WHERE a <= 2;
```

Today:
```
rows decoded from KV: 22 (...) -- 2 from the outer scan + 2 x 10 from the condition
• routine name: cond_count invocations: 2
```
The condition's count(*) runs once per invocation and dominates the KV work, but its plan appears nowhere -- only the unexplained gap in the header totals betrays it. `IF EXISTS (...)`, `IF x IN (SELECT ...)`, and WHILE conditions behave the same.

Expected:
```
• routine: cond_count (2 invocations)
│ condition ((SELECT count(*) FROM t) > 5): 2 evaluations
└── • body stmt (SELECT count(*) FROM t) <- condition's plan, 2 invocations
```

Depends on: #170565
Parent issue: #168034

Jira issue: CRDB-64078

Epic CRDB-66209

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.