cockroachdb / cockroachdb/cockroach

sql: insert fast path is not shown in EXPLAIN when there are index recommendations

Open
#127,123 3 comments 0 reactions 0 assignees View on GitHub
branch-release-23.2 branch-release-24.1 branch-release-24.2 C-bug T-sql-queries
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

When an `EXPLAIN INSERT ...` statement show index recommedations, the query plan does not show that the insert fast path is being used when it is in fact used. Below is an example:

```sql
SET experimental_enable_unique_without_index_constraints=true;

CREATE TABLE t (
k INT PRIMARY KEY,
i INT,
v INT AS (i) VIRTUAL,
UNIQUE WITHOUT INDEX (i),
INDEX (v) STORING (i)
);

-- Disable index recommendations.
SET index_recommendations_enabled=off;

-- The query uses the insert fast path.
EXPLAIN INSERT INTO t VALUES (1, 2);
-- info
-- ---------------------------------
-- distribution: local
-- vectorized: true
--
-- • insert fast path
-- into: t(k, i, v)
-- auto commit
-- uniqueness check: t@t_v_idx
-- size: 3 columns, 1 row
-- (8 rows)

-- Enable index recommendations.
SET index_recommendations_enabled=on;

-- The output incorrectly indicates that the fast path is not used in the query
-- plan.
EXPLAIN INSERT INTO t VALUES (1, 2);
-- info
-- ------------------------------------------------------------
-- distribution: local
-- vectorized: true
--
-- • root
-- │
-- ├── • insert
-- │ │ into: t(k, i, v)
-- │ │
-- │ └── • values
-- │ size: 2 columns, 1 row
-- │
-- └── • constraint-check
-- │
-- └── • error if rows
-- │
-- └── • cross join
-- │
-- ├── • values
-- │ size: 1 column, 1 row
-- │
-- └── • limit
-- │ count: 1
-- │
-- └── • filter
-- │ filter: i = 2
-- │
-- └── • scan
-- missing stats
-- table: t@t_v_idx
-- spans: [/2 - /2/0] [/2/2 - /2]
--
-- index recommendations: 1
-- 1. type: index creation
-- SQL command: CREATE INDEX ON defaultdb.public.t (i);
-- (34 rows)
```

Jira issue: CRDB-40286

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.