cockroachdb / cockroachdb/cockroach
sql: annotate inlined UDFs in EXPLAIN output
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
When the optimizer inlines a UDF, its body is merged into the outer plan with no indication that a UDF was ever involved. This makes it hard to understand where plan nodes came from.
```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 lookup_b(x INT) RETURNS INT STABLE AS $$
SELECT b FROM t WHERE a = x;
$$ LANGUAGE SQL;
EXPLAIN SELECT lookup_b(a) FROM t WHERE a < 5;
```
Today:
```
• render
└── • merge join
├── • scan (t@t_pkey, FULL SCAN)
└── • scan (t@t_pkey, [ - /4])
```
The merge join originates from the UDF's point lookup being fused with the outer scan, but there's no way to tell. Expected:
```
• render
│ note: lookup_b inlined as join
└── • merge join
├── • scan (t@t_pkey, FULL SCAN)
└── • scan (t@t_pkey, [ - /4])
```
The inlining happens in `pkg/sql/opt/norm/inline_funcs.go`. The norm rules could annotate the resulting expression with metadata about the source UDF, and the EXPLAIN emitter would read this annotation.
Independent of the execution stats work (#170564, #170565).
Parent issue: #168034
Jira issue: CRDB-64077
Epic CRDB-66209
Contributor guide
Assessment
This issue has not been assessed yet.