sql,opt: add setting avoid_full_table_scans_in_mutations

Fixes #79683 Release note (sql change): Added a new session setting avoid_full_table_scans_in_mutations, which when set to true, causes the optimizer to avoid planning full table scans for mutation queries if any other plan is possible. It now defaults to true.
cockroachdb · Dec 19, 2024 · 1d779c6 · 1d779c6
1 parent 4c795a3
commit 1d779c6
Show file tree

Hide file tree

Showing 77 changed files with 2,627 additions and 1,907 deletions.
diff --git a/pkg/ccl/logictestccl/testdata/logic_test/multi_region_remote_access_error b/pkg/ccl/logictestccl/testdata/logic_test/multi_region_remote_access_error
@@ -502,7 +502,7 @@ DELETE FROM messages_rbr WHERE account_id NOT IN (SELECT account_id FROM message
 
 # Delete accessing all regions should fail.
 retry
-statement error pq: Query has no home region\. Try adding a filter on messages_rbr\.crdb_region and/or on key column \(messages_rbr\.account_id\)\. For more information, see https://www.cockroachlabs.com/docs/stable/cost-based-optimizer.html#control-whether-queries-are-limited-to-a-single-region
+statement error pq: Query has no home region\. Try adding a filter on messages_rbr\.crdb_region and/or on key column \(messages_rbr\.message\)\. For more information, see https://www.cockroachlabs.com/docs/stable/cost-based-optimizer.html#control-whether-queries-are-limited-to-a-single-region
 DELETE FROM messages_rbr WHERE message = 'Hello World!'
 
 # Insert should fail accessing all rows in messages_rbr.

diff --git a/pkg/ccl/logictestccl/testdata/logic_test/partitioning_implicit b/pkg/ccl/logictestccl/testdata/logic_test/partitioning_implicit
@@ -279,7 +279,7 @@ vectorized: true
 │           │
 │           ├── • scan
 │           │     missing stats
-│           │     table: t@t_b_idx
+│           │     table: t@t_pkey
 │           │     spans: FULL SCAN
 │           │
 │           └── • scan buffer
@@ -296,7 +296,7 @@ vectorized: true
             │
             ├── • scan
             │     missing stats
-            │     table: t@t_c_key
+            │     table: t@t_pkey
             │     spans: FULL SCAN
             │
             └── • scan buffer
@@ -764,21 +764,18 @@ vectorized: true
 │   │
 │   └── • error if rows
 │       │
-│       └── • cross join
+│       └── • cross join (right semi)
 │           │
-│           ├── • values
-│           │     size: 1 column, 1 row
+│           ├── • filter
+│           │   │ filter: (b = 1) AND ((pk != 1) OR (partition_by != 1))
+│           │   │
+│           │   └── • scan
+│           │         missing stats
+│           │         table: t@t_pkey
+│           │         spans: FULL SCAN
 │           │
-│           └── • limit
-│               │ count: 1
-│               │
-│               └── • filter
-│                   │ filter: (b = 1) AND ((pk != 1) OR (partition_by != 1))
-│                   │
-│                   └── • scan
-│                         missing stats
-│                         table: t@t_b_key
-│                         spans: FULL SCAN (SOFT LIMIT)
+│           └── • values
+│                 size: 1 column, 1 row
 │
 └── • constraint-check
     │
@@ -837,7 +834,7 @@ vectorized: true
 │           │
 │           ├── • scan
 │           │     missing stats
-│           │     table: t@t_b_key
+│           │     table: t@t_pkey
 │           │     spans: FULL SCAN
 │           │
 │           └── • scan buffer

diff --git a/pkg/ccl/logictestccl/testdata/logic_test/partitioning_implicit_read_committed b/pkg/ccl/logictestccl/testdata/logic_test/partitioning_implicit_read_committed
@@ -251,12 +251,13 @@ update t
  ├── unique w/tombstone indexes: t_c_key
  └── project
       ├── scan t
-      │    └── constraint: /12/11
-      │         ├── [/'one'/2 - /'one'/2]
-      │         ├── [/'two'/2 - /'two'/2]
-      │         ├── [/'three'/2 - /'three'/2]
-      │         ├── [/'four'/2 - /'four'/2]
-      │         └── [/'five'/2 - /'five'/2]
+      │    ├── constraint: /12/11
+      │    │    ├── [/'one'/2 - /'one'/2]
+      │    │    ├── [/'two'/2 - /'two'/2]
+      │    │    ├── [/'three'/2 - /'three'/2]
+      │    │    ├── [/'four'/2 - /'four'/2]
+      │    │    └── [/'five'/2 - /'five'/2]
+      │    └── flags: avoid-full-scan
       └── projections
            └── 4
 

diff --git a/pkg/sql/exec_util.go b/pkg/sql/exec_util.go
@@ -3564,6 +3564,10 @@ func (m *sessionDataMutator) SetDisallowFullTableScans(val bool) {
 	m.data.DisallowFullTableScans = val
 }
 
+func (m *sessionDataMutator) SetAvoidFullTableScansInMutations(val bool) {
+	m.data.AvoidFullTableScansInMutations = val
+}
+
 func (m *sessionDataMutator) SetAlterColumnTypeGeneral(val bool) {
 	m.data.AlterColumnTypeGeneralEnabled = val
 }

diff --git a/pkg/sql/logictest/testdata/logic_test/information_schema b/pkg/sql/logictest/testdata/logic_test/information_schema
@@ -3905,6 +3905,7 @@ always_distribute_full_scans                               off
 application_name                                           ·
 authentication_method                                      cert-password
 avoid_buffering                                            off
+avoid_full_table_scans_in_mutations                        on
 backslash_quote                                            safe_encoding
 bypass_pcr_reader_catalog_aost                             off
 bytea_output                                               hex

diff --git a/pkg/sql/opt/exec/execbuilder/testdata/autocommit b/pkg/sql/opt/exec/execbuilder/testdata/autocommit
@@ -734,7 +734,7 @@ WHERE message       LIKE '%r$rangeid: sending batch%'
 ----
 dist sender send  r74: sending batch 1 Scan to (n1,s1):1
 dist sender send  r74: sending batch 1 Put to (n1,s1):1
-dist sender send  r74: sending batch 1 Scan to (n1,s1):1
+dist sender send  r74: sending batch 1 Get to (n1,s1):1
 dist sender send  r74: sending batch 1 EndTxn to (n1,s1):1
 
 query B

diff --git a/pkg/sql/opt/exec/execbuilder/testdata/delete b/pkg/sql/opt/exec/execbuilder/testdata/delete
@@ -288,6 +288,9 @@ CREATE TABLE xyz (
 
 # Ensure that we can use a hint to avoid a full table scan.
 
+statement ok
+SET avoid_full_table_scans_in_mutations = false
+
 # Without the hint, we plan a full table scan.
 query T
 EXPLAIN (VERBOSE) DELETE FROM xyz WHERE (y > 0 AND y < 1000) OR (y > 2000 AND y < 3000) RETURNING z
@@ -375,6 +378,40 @@ vectorized: true
               spans: /1-/1000 /2001-/3000
               locking strength: for update
 
+# We also avoid the full scan using the session setting
+# avoid_full_table_scans_in_mutations.
+statement ok
+SET avoid_full_table_scans_in_mutations = true
+
+query T
+EXPLAIN (VERBOSE) DELETE FROM xyz WHERE (y > 0 AND y < 1000) OR (y > 2000 AND y < 3000) RETURNING z
+----
+distribution: local
+vectorized: true
+·
+• project
+│ columns: (z)
+│
+└── • delete
+    │ columns: (x, z)
+    │ estimated row count: 990 (missing stats)
+    │ from: xyz
+    │ auto commit
+    │
+    └── • index join
+        │ columns: (x, y, z)
+        │ estimated row count: 990 (missing stats)
+        │ table: xyz@xyz_pkey
+        │ key columns: x
+        │ locking strength: for update
+        │
+        └── • scan
+              columns: (x, y)
+              estimated row count: 990 (missing stats)
+              table: xyz@xyz_y_idx
+              spans: /1-/1000 /2001-/3000
+              locking strength: for update
+
 # Testcase for issue 105803.
 
 statement ok