This is an automated email from the ASF dual-hosted git repository.
amashenkov pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/ignite-3.git
The following commit(s) were added to refs/heads/main by this push:
new 36bd44ce78f IGNITE-28296 SQL. Prefer table scan for unbounded scan
with no sort required. (#7818)
36bd44ce78f is described below
commit 36bd44ce78f2a82534ed8957af4b8c81e31a74b7
Author: Andrew V. Mashenkov <[email protected]>
AuthorDate: Tue Mar 24 14:40:33 2026 +0300
IGNITE-28296 SQL. Prefer table scan for unbounded scan with no sort
required. (#7818)
---
.../internal/sql/engine/rel/AbstractIndexScan.java | 5 +--
.../engine/rel/ProjectableFilterableTableScan.java | 2 +-
.../internal/sql/engine/planner/PlannerTest.java | 42 ++++++++++++++++++++--
3 files changed, 44 insertions(+), 5 deletions(-)
diff --git
a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/rel/AbstractIndexScan.java
b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/rel/AbstractIndexScan.java
index efb0d83b327..34b626a86b5 100644
---
a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/rel/AbstractIndexScan.java
+++
b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/rel/AbstractIndexScan.java
@@ -157,6 +157,7 @@ public abstract class AbstractIndexScan extends
ProjectableFilterableTableScan {
double indexRowPassThroughCost = IgniteCost.ROW_PASS_THROUGH_COST *
IgniteCost.INDEX_ROW_SCAN_MULTIPLIER;
if (condition == null) {
+ rows = Math.max(1.0d, rows);
cost = rows * indexRowPassThroughCost;
} else {
double selectivity = 1;
@@ -170,9 +171,9 @@ public abstract class AbstractIndexScan extends
ProjectableFilterableTableScan {
cost = Math.max(Math.log(rows), 1) *
IgniteCost.ROW_COMPARISON_COST;
}
- rows *= selectivity;
+ rows = Math.max(rows * selectivity, 1);
- cost += Math.max(rows, 1) * (IgniteCost.ROW_COMPARISON_COST +
indexRowPassThroughCost);
+ cost += rows * (IgniteCost.ROW_COMPARISON_COST +
indexRowPassThroughCost);
}
return planner.getCostFactory().makeCost(rows, cost, 0);
diff --git
a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/rel/ProjectableFilterableTableScan.java
b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/rel/ProjectableFilterableTableScan.java
index 3a3f61b02e4..15696ab99da 100644
---
a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/rel/ProjectableFilterableTableScan.java
+++
b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/rel/ProjectableFilterableTableScan.java
@@ -173,7 +173,7 @@ public abstract class ProjectableFilterableTableScan
extends TableScan {
cost += rows * IgniteCost.ROW_COMPARISON_COST;
}
- return planner.getCostFactory().makeCost(rows, cost, 0);
+ return planner.getCostFactory().makeCost(rows, Math.max(cost, 1.0d),
0);
}
/** {@inheritDoc} */
diff --git
a/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/planner/PlannerTest.java
b/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/planner/PlannerTest.java
index 2c730abb358..3d02ea5df9d 100644
---
a/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/planner/PlannerTest.java
+++
b/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/planner/PlannerTest.java
@@ -17,6 +17,7 @@
package org.apache.ignite.internal.sql.engine.planner;
+import static java.util.function.Predicate.not;
import static org.apache.calcite.tools.Frameworks.newConfigBuilder;
import static
org.apache.ignite.internal.sql.engine.planner.CorrelatedSubqueryPlannerTest.createTestTable;
import static
org.apache.ignite.internal.sql.engine.util.Commons.FRAMEWORK_CONFIG;
@@ -55,10 +56,12 @@ import
org.apache.ignite.internal.sql.engine.prepare.PlanningContext;
import org.apache.ignite.internal.sql.engine.rel.IgniteConvention;
import org.apache.ignite.internal.sql.engine.rel.IgniteFilter;
import org.apache.ignite.internal.sql.engine.rel.IgniteHashJoin;
+import org.apache.ignite.internal.sql.engine.rel.IgniteIndexScan;
import org.apache.ignite.internal.sql.engine.rel.IgniteMergeJoin;
import org.apache.ignite.internal.sql.engine.rel.IgniteRel;
import org.apache.ignite.internal.sql.engine.rel.IgniteSort;
import org.apache.ignite.internal.sql.engine.rel.IgniteTableScan;
+import org.apache.ignite.internal.sql.engine.schema.IgniteIndex.Collation;
import org.apache.ignite.internal.sql.engine.schema.IgniteSchema;
import org.apache.ignite.internal.sql.engine.schema.IgniteTable;
import org.apache.ignite.internal.sql.engine.trait.IgniteDistribution;
@@ -114,7 +117,7 @@ public class PlannerTest extends AbstractPlannerTest {
+ " WHERE VAL = 10::VARCHAR) \n"
+ "WHERE VAL = 10::VARCHAR";
- assertPlan(sql, publicSchema,
Predicate.not(nodeOrAnyChild(isInstanceOf(IgniteFilter.class)))
+ assertPlan(sql, publicSchema,
not(nodeOrAnyChild(isInstanceOf(IgniteFilter.class)))
.and(nodeOrAnyChild(isInstanceOf(IgniteTableScan.class)
.and(scan -> scan.condition() != null)
)));
@@ -216,7 +219,7 @@ public class PlannerTest extends AbstractPlannerTest {
.and(hasChildThat(isTableScan("EMP")))
.and(hasChildThat(isTableScan("DEPT")))
))
-
).and(Predicate.not(nodeOrAnyChild(isInstanceOf(IgniteMergeJoin.class)))),
+
).and(not(nodeOrAnyChild(isInstanceOf(IgniteMergeJoin.class)))),
"CorrelatedNestedLoopJoin");
}
@@ -427,4 +430,39 @@ public class PlannerTest extends AbstractPlannerTest {
.size(100)
.distribution(distribution);
}
+
+ /** Ensure we we prefer table scan in cases where index scan is useless. */
+ @Test
+ public void preferTableScanForEmptyTable() throws Exception {
+ IgniteSchema publicSchema = createSchema(
+ TestBuilders.table()
+ .name("T1")
+ .distribution(TestBuilders.affinity(0, nextTableId(),
Integer.MIN_VALUE))
+ .distribution(IgniteDistributions.single())
+ .addKeyColumn("PK", NativeTypes.INT32)
+ .addColumn("VAL1", NativeTypes.INT32)
+ .addColumn("VAL2", NativeTypes.INT32)
+ .size(0)
+ .sortedIndex().name("IDX1").addColumn("VAL2",
Collation.ASC_NULLS_LAST).end()
+ .build(),
+ TestBuilders.table()
+ .name("T2")
+ .distribution(TestBuilders.affinity(0, nextTableId(),
Integer.MIN_VALUE))
+ .addColumn("PK", NativeTypes.INT32)
+ .addColumn("VAL1", NativeTypes.INT32)
+ .addColumn("VAL2", NativeTypes.INT32)
+ .size(0)
+ .sortedIndex().name("IDX2").addColumn("VAL2",
Collation.ASC_NULLS_LAST).end()
+ .build()
+ );
+
+ // Join on non-indexed columns.
+ assertPlan("SELECT * FROM T1 LEFT JOIN T2 ON t1.val1 = t2.val1",
publicSchema, isInstanceOf(IgniteHashJoin.class)
+
.and(not(nodeOrAnyChild(isInstanceOf(IgniteIndexScan.class)))));
+ // Order by non-indexed column
+ assertPlan("SELECT * FROM T1 ORDER BY val1", publicSchema,
isInstanceOf(IgniteSort.class)
+ .and(nodeOrAnyChild(isTableScan("T1"))));
+ // Condition on non-indexed column.
+ assertPlan("SELECT * FROM T1", publicSchema,
nodeOrAnyChild(isTableScan("T1")));
+ }
}
