cockroachdb / cockroachdb/cockroach

sql: annotate inlined UDFs in EXPLAIN output

Open
#170,567 1 comment 0 reactions 1 assignee Claimed by @ZhouXing19 View on GitHub
A-sql-routine A-sql-udf 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

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

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.