cockroachdb / cockroachdb/cockroach

sql: allow inlining of scalar expressions with function calls and placeholders

Open
#161,657 4 comments 0 reactions 1 assignee Claimed by @michae2 View on GitHub
A-generic-query-plans A-sql-optimizer C-performance O-support P-3 T-sql-queries
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

The optimizer currently only considers inlining scalar expressions that are [very simple](https://github.com/cockroachdb/cockroach/blob/cbe43e5b32873b068ee04b81fd6237be8e53861c/pkg/sql/opt/norm/inline_funcs.go#L155-L176), in order to prevent an expensive scalar expression from being evaluated too many times. But inlining can enable other optimizations.

Here's an example where inlining scalar expressions (xy.x and xy.y) allows us to pick a better index:

```sql
CREATE TABLE efg (
e INT PRIMARY KEY,
f STRING NULL,
g GEOGRAPHY NULL,
INVERTED INDEX (g)
);

EXPLAIN
WITH xy AS (
SELECT 5::float AS x, 10::float AS y
)
SELECT * FROM xy, efg
WHERE st_dwithin(g, st_point(xy.x, xy.y)::geography, xy.x * xy.y, true);
-- uses efg_g_idx
```

If the scalar expressions use placeholders or function calls, we can no longer find the plan with the better index, because the optimizer doesn't consider inlining the expressions:

```sql
-- prevent folding the st_point() call into a constant
SET disable_optimizer_rules = 'FoldFunction';

EXPLAIN
WITH xy AS (
SELECT 5::float AS x, 10::float AS y
), p AS (
SELECT st_point(5::float, 10::float)::geography AS p
FROM xy
)
SELECT * FROM xy, p, efg
WHERE st_dwithin(g, p.p, xy.x * xy.y, true);
-- full scan of efg_pkey

-- prevent replacing the placeholders with constants
SET plan_cache_mode = force_generic_plan;

PREPARE stmt AS
WITH xy AS (
SELECT $1::float AS x, 10::float AS y
)
SELECT * FROM xy, efg
WHERE st_dwithin(g, st_point(xy.x, xy.y)::geography, xy.x * xy.y, true);

EXPLAIN ANALYZE EXECUTE stmt (5);
-- full scan of efg_pkey
```

We should allow inlining of placeholders, and some function calls, in order to discover better query plans.

Jira issue: CRDB-59006

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.