alex-plekhanov commented on code in PR #13389:
URL: https://github.com/apache/ignite/pull/13389#discussion_r4423466815
##########
modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/metadata/IgniteMdRowCount.java:
##########
@@ -17,38 +17,60 @@
package org.apache.ignite.internal.processors.query.calcite.metadata;
+import java.util.ArrayList;
+import java.util.BitSet;
+import java.util.HashMap;
+import java.util.IdentityHashMap;
+import java.util.Iterator;
+import java.util.List;
+import java.util.Map;
+import org.apache.calcite.plan.RelOptTable;
+import org.apache.calcite.rel.RelDistribution;
+import org.apache.calcite.rel.RelNode;
import org.apache.calcite.rel.core.Intersect;
import org.apache.calcite.rel.core.Join;
import org.apache.calcite.rel.core.JoinInfo;
import org.apache.calcite.rel.core.JoinRelType;
import org.apache.calcite.rel.core.Minus;
import org.apache.calcite.rel.core.Sort;
+import org.apache.calcite.rel.metadata.BuiltInMetadata;
import org.apache.calcite.rel.metadata.ReflectiveRelMetadataProvider;
+import org.apache.calcite.rel.metadata.RelColumnOrigin;
import org.apache.calcite.rel.metadata.RelMdRowCount;
import org.apache.calcite.rel.metadata.RelMdUtil;
import org.apache.calcite.rel.metadata.RelMetadataProvider;
import org.apache.calcite.rel.metadata.RelMetadataQuery;
import org.apache.calcite.rex.RexNode;
-import org.apache.calcite.util.BuiltInMethod;
import org.apache.calcite.util.ImmutableBitSet;
import org.apache.calcite.util.ImmutableIntList;
import org.apache.calcite.util.Util;
+import org.apache.calcite.util.mapping.IntPair;
import org.apache.ignite.internal.processors.query.calcite.rel.IgniteAggregate;
import org.apache.ignite.internal.processors.query.calcite.rel.IgniteLimit;
import
org.apache.ignite.internal.processors.query.calcite.rel.IgniteSortedIndexSpool;
import
org.apache.ignite.internal.processors.query.calcite.rel.IgniteTableModify;
+import
org.apache.ignite.internal.processors.query.calcite.schema.CacheColumnDescriptor;
+import
org.apache.ignite.internal.processors.query.calcite.schema.ColumnDescriptor;
+import org.apache.ignite.internal.processors.query.calcite.schema.IgniteTable;
+import org.apache.ignite.internal.util.GridLeanMap;
import org.apache.ignite.internal.util.typedef.F;
import org.jetbrains.annotations.Nullable;
import static org.apache.calcite.util.NumberUtil.multiply;
+import static
org.apache.ignite.internal.processors.query.QueryUtils.KEY_FIELD_NAME;
/** */
@SuppressWarnings("unused") // actually all methods are used by runtime
generated classes
public class IgniteMdRowCount extends RelMdRowCount {
+ /** */
+ private static final double NON_EQUI_COEFF = 0.7;
+
+ /** */
+ public static final double INTERSECT_ESTIMATE_COEFF = 0.8;
Review Comment:
Can be private
##########
modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/JoinRowCountEstimationTest.java:
##########
@@ -0,0 +1,348 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.ignite.internal.processors.query.calcite.planner;
+
+import java.math.BigDecimal;
+import java.util.function.Predicate;
+import java.util.regex.Pattern;
+import org.apache.calcite.plan.RelOptUtil;
+import org.apache.calcite.rel.RelNode;
+import org.apache.calcite.sql.SqlExplainFormat;
+import org.apache.calcite.sql.SqlExplainLevel;
+import org.apache.calcite.util.ImmutableIntList;
+import
org.apache.ignite.internal.processors.query.calcite.rel.IgniteCorrelatedNestedLoopJoin;
+import org.apache.ignite.internal.processors.query.calcite.schema.IgniteSchema;
+import
org.apache.ignite.internal.processors.query.calcite.trait.IgniteDistributions;
+import org.apache.ignite.internal.util.typedef.internal.CU;
+import org.hamcrest.BaseMatcher;
+import org.hamcrest.CoreMatchers;
+import org.hamcrest.Description;
+import org.hamcrest.Matcher;
+import org.junit.jupiter.api.BeforeEach;
+import org.junit.jupiter.api.Test;
+
+import static
org.apache.ignite.internal.processors.query.calcite.metadata.IgniteMdSelectivity.COMPARISON_SELECTIVITY;
+import static
org.apache.ignite.internal.processors.query.calcite.metadata.IgniteMdSelectivity.EQUALS_SELECTIVITY;
+import static
org.apache.ignite.internal.processors.query.calcite.metadata.IgniteMdSelectivity.IS_NOT_NULL_SELECTIVITY;
+
+/**
+ * Tests to check row count estimation for join relation.
+ */
+public class JoinRowCountEstimationTest extends AbstractPlannerTest {
+ /** */
+ private static final int CATALOG_SALES_SIZE = 1_441_548;
+
+ /** */
+ private static final int CATALOG_RETURNS_SIZE = 144_067;
+
+ /** */
+ private static final int DATE_DIM_SIZE = 73_049;
+
+ /** */
+ private static final String SELECT = "SELECT /*+ DISABLE_RULE(" +
+ "'JoinCommuteRule', " +
+ "'MergeJoinConverter', " +
+ "'NestedLoopJoinConverter', " +
+ "'CorrelateToNestedLoopRule'" +
+ ") */ * ";
+
+ /** */
+ private IgniteSchema publicSchema;
+
+ /** {@inheritDoc} */
+ @BeforeEach
+ @Override public void setup() {
+ super.setup();
+
+ TestTable tbl1 = createTable(
+ "CATALOG_SALES",
+ CATALOG_SALES_SIZE,
+ IgniteDistributions.affinity(ImmutableIntList.of(1, 2),
CU.cacheId("default"), 0),
+ "ID", Integer.class,
+ "CS_ITEM_SK", Integer.class,
+ "CS_ORDER_NUMBER", Integer.class,
+ "CS_PROMO_SK", Integer.class
+ );
+
+ TestTable tbl2 = createTable(
+ "CATALOG_RETURNS",
+ CATALOG_RETURNS_SIZE,
+ IgniteDistributions.affinity(ImmutableIntList.of(1, 2),
CU.cacheId("default"), 0),
+ "ID", Integer.class,
+ "CR_ITEM_SK", Integer.class,
+ "CR_ORDER_NUMBER", Integer.class,
+ "CR_RETURNED_DATE_SK", Integer.class,
+ "CR_RETURN_QUANTITY", Integer.class
+ );
+
+ TestTable tbl3 = createTable(
+ "DATE_DIM",
+ DATE_DIM_SIZE,
+ IgniteDistributions.affinity(ImmutableIntList.of(0),
CU.cacheId("default"), 0),
+ "D_DATE_SK", Integer.class,
+ "D_MOY", Integer.class
+ );
+
+ publicSchema = createSchema(tbl1, tbl2, tbl3);
+ }
+
+ /** */
+ @Test
+ public void joinSameTableSimpleAffMergeJoin() throws Exception {
+ assertPlan(SELECT
+ + " FROM catalog_sales"
+ + " ,catalog_returns"
+ + " WHERE cs_item_sk = cr_item_sk"
+ + " AND cs_order_number = cr_order_number",
+ publicSchema,
+ nodeRowCount("IgniteHashJoin",
approximatelyEqual(CATALOG_RETURNS_SIZE)));
+
+ assertPlan(SELECT
+ + " FROM catalog_sales"
+ + " ,catalog_returns"
+ + " WHERE cs_item_sk = cr_item_sk"
+ + " AND cs_order_number = cr_order_number"
+ + " AND cs_promo_sk IS NOT NULL",
+ publicSchema,
+ nodeRowCount("IgniteHashJoin",
approximatelyEqual(CATALOG_RETURNS_SIZE * IS_NOT_NULL_SELECTIVITY)));
+ }
+
+ /** */
+ @Test
+ public void joinByPrimaryKeysLeft() throws Exception {
+ assertPlan(SELECT
+ + " FROM catalog_sales LEFT JOIN catalog_returns ON"
+ + " cs_item_sk = cr_item_sk AND cs_order_number =
cr_order_number",
+ publicSchema,
+ nodeRowCount("IgniteHashJoin",
CoreMatchers.is(CATALOG_SALES_SIZE)));
+
+ assertPlan(SELECT
+ + " FROM catalog_sales LEFT JOIN catalog_returns"
+ + " ON cs_item_sk = cr_item_sk"
+ + " AND cs_order_number = cr_order_number"
+ + " AND cs_promo_sk IS NOT NULL",
+ publicSchema,
+ nodeRowCount("IgniteHashJoin",
CoreMatchers.is(CATALOG_SALES_SIZE)));
+
+ assertPlan(SELECT
+ + " FROM catalog_sales LEFT JOIN catalog_returns"
+ + " ON cs_item_sk = cr_item_sk"
+ + " AND cs_order_number = cr_order_number"
+ + " AND cr_order_number > 1",
+ publicSchema,
+ nodeRowCount("IgniteHashJoin",
CoreMatchers.is(CATALOG_SALES_SIZE)));
+ }
+
+ /** */
+ @Test
+ public void joinByPrimaryKeysRight() throws Exception {
+ assertPlan(SELECT
+ + " FROM catalog_returns RIGHT JOIN catalog_sales ON"
+ + " cs_item_sk = cr_item_sk AND cs_order_number =
cr_order_number",
+ publicSchema,
+ nodeRowCount("IgniteHashJoin",
CoreMatchers.is(CATALOG_SALES_SIZE)));
+
+ assertPlan(SELECT
+ + " FROM catalog_returns RIGHT JOIN catalog_sales"
+ + " ON cs_item_sk = cr_item_sk"
+ + " AND cs_order_number = cr_order_number"
+ + " AND cs_promo_sk IS NOT NULL",
+ publicSchema,
+ nodeRowCount("IgniteHashJoin",
CoreMatchers.is(CATALOG_SALES_SIZE)));
+
+ assertPlan(SELECT
+ + " FROM catalog_returns RIGHT JOIN catalog_sales"
+ + " ON cs_item_sk = cr_item_sk"
+ + " AND cs_order_number = cr_order_number"
+ + " AND cr_order_number > 1",
+ publicSchema,
+ nodeRowCount("IgniteHashJoin",
CoreMatchers.is(CATALOG_SALES_SIZE)));
+ }
+
+ /** */
+ @Test
+ public void joinByPrimaryKeysFull() throws Exception {
+ assertPlan(SELECT
+ + " FROM catalog_returns FULL OUTER JOIN catalog_sales ON"
+ + " cs_item_sk = cr_item_sk AND cs_order_number =
cr_order_number",
+ publicSchema,
+ nodeRowCount("IgniteHashJoin",
approximatelyEqual((int)((CATALOG_SALES_SIZE + CATALOG_RETURNS_SIZE)
+ * (1.0 - (EQUALS_SELECTIVITY * EQUALS_SELECTIVITY))))));
+
+ assertPlan(SELECT
+ + " FROM catalog_returns FULL OUTER JOIN catalog_sales"
+ + " ON cs_item_sk = cr_item_sk"
+ + " AND cs_order_number = cr_order_number"
+ + " AND cs_promo_sk IS NOT NULL",
+ publicSchema,
+ nodeRowCount("IgniteHashJoin",
approximatelyEqual((int)((CATALOG_SALES_SIZE + CATALOG_RETURNS_SIZE)
+ * (1.0 - (EQUALS_SELECTIVITY * EQUALS_SELECTIVITY *
IS_NOT_NULL_SELECTIVITY))))));
+
+ assertPlan(SELECT
+ + " FROM catalog_returns FULL OUTER JOIN catalog_sales"
+ + " ON cs_item_sk = cr_item_sk"
+ + " AND cs_order_number = cr_order_number"
+ + " AND cr_order_number > 1",
+ publicSchema,
+ nodeRowCount("IgniteHashJoin",
approximatelyEqual((int)((CATALOG_SALES_SIZE + CATALOG_RETURNS_SIZE)
+ * (1.0 - (EQUALS_SELECTIVITY * EQUALS_SELECTIVITY *
COMPARISON_SELECTIVITY))))));
+ }
+
+ /** */
+ @Test
+ public void joinByForeignKey() throws Exception {
+ assertPlan(SELECT
+ + " FROM catalog_returns"
+ + " ,date_dim"
+ + " WHERE cr_returned_date_sk = d_date_sk",
+ publicSchema,
+ nodeRowCount("IgniteHashJoin",
approximatelyEqual(CATALOG_RETURNS_SIZE * EQUALS_SELECTIVITY)));
+
+ assertPlan(SELECT
+ + " FROM date_dim"
+ + " ,catalog_returns"
+ + " WHERE cr_returned_date_sk = d_date_sk"
+ + " AND d_moy > 6",
+ publicSchema,
+ nodeRowCount("IgniteHashJoin",
+ approximatelyEqual(CATALOG_RETURNS_SIZE * EQUALS_SELECTIVITY *
COMPARISON_SELECTIVITY)));
+ }
+
+ /** */
+ @Test
+ public void joinByForeignKeyLeft() throws Exception {
+ assertPlan(SELECT
+ + " FROM catalog_returns LEFT JOIN date_dim ON"
+ + " cr_returned_date_sk = d_date_sk",
+ publicSchema,
+ nodeRowCount("IgniteHashJoin",
CoreMatchers.is(CATALOG_RETURNS_SIZE)));
+
+ assertPlan(SELECT
+ + " FROM date_dim LEFT JOIN catalog_returns ON"
+ + " cr_returned_date_sk = d_date_sk",
+ publicSchema,
+ nodeRowCount("IgniteHashJoin",
CoreMatchers.is(CATALOG_RETURNS_SIZE)));
+
+ assertPlan(SELECT
+ + " FROM catalog_returns LEFT JOIN date_dim ON"
+ + " cr_returned_date_sk = d_date_sk"
+ + " AND cr_return_quantity > 6",
+ publicSchema,
+ nodeRowCount("IgniteHashJoin",
CoreMatchers.is(CATALOG_RETURNS_SIZE)));
+ }
+
+ /** */
+ @Test
+ public void joinByForeignKeyRight() throws Exception {
+ assertPlan(SELECT
+ + " FROM catalog_returns RIGHT JOIN date_dim ON"
+ + " cr_returned_date_sk = d_date_sk",
+ publicSchema,
+ nodeRowCount("IgniteHashJoin",
CoreMatchers.is(CATALOG_RETURNS_SIZE)));
+
+ assertPlan(SELECT
+ + " FROM date_dim RIGHT JOIN catalog_returns ON"
+ + " cr_returned_date_sk = d_date_sk",
+ publicSchema,
+ nodeRowCount("IgniteHashJoin",
CoreMatchers.is(CATALOG_RETURNS_SIZE)));
+
+ assertPlan(SELECT
+ + " FROM date_dim RIGHT JOIN catalog_returns ON"
+ + " cr_returned_date_sk = d_date_sk"
+ + " AND cr_return_quantity > 6",
+ publicSchema,
+ nodeRowCount("IgniteHashJoin",
CoreMatchers.is(CATALOG_RETURNS_SIZE)));
+ }
+
+ /** */
+ @Test
+ public void joinByForeignKeyFull() throws Exception {
+ assertPlan(SELECT
+ + " FROM date_dim FULL OUTER JOIN catalog_returns ON"
+ + " cr_returned_date_sk = d_date_sk",
+ publicSchema,
+ nodeRowCount("IgniteHashJoin",
approximatelyEqual((int)((DATE_DIM_SIZE + CATALOG_RETURNS_SIZE)
+ * (1.0 - (EQUALS_SELECTIVITY))))));
+
+ assertPlan(SELECT
+ + " FROM date_dim FULL OUTER JOIN catalog_returns ON"
+ + " cr_returned_date_sk = d_date_sk"
+ + " AND cr_return_quantity > 6",
+ publicSchema,
+ nodeRowCount("IgniteHashJoin",
approximatelyEqual((int)((DATE_DIM_SIZE + CATALOG_RETURNS_SIZE)
+ * (1.0 - (EQUALS_SELECTIVITY * COMPARISON_SELECTIVITY))))));
+ }
+
+ /** */
+ @Test
+ public void correlatedNestedLoopJoinRowCount() throws Exception {
+ assertPlan(
+ "SELECT /*+ CNL_JOIN */ * FROM catalog_returns"
+ + " ,date_dim"
+ + " WHERE cr_returned_date_sk = d_date_sk",
+ publicSchema,
+ nodeOrAnyChild(isInstanceOf(IgniteCorrelatedNestedLoopJoin.class)
+ .and(nodeRowCount("IgniteCorrelatedNestedLoopJoin",
+ approximatelyEqual(CATALOG_RETURNS_SIZE *
EQUALS_SELECTIVITY)))));
+ }
+
+ /**
+ * Matcher which ensures that node matching the pattern has row count
matching provided matcher.
+ *
+ * @param nodePattern A pattern describing a node of interest.
+ * @param rowCountMatcher Matcher for the estimated row count.
+ * @return Matcher.
+ */
+ static <T extends RelNode> Predicate<RelNode> nodeRowCount(String
nodePattern, Matcher<Integer> rowCountMatcher) {
+ Pattern pattern = Pattern.compile(".*" + nodePattern
+ + "\\(.*?rowcount = (?<rowcount>\\d+).*");
+
+ return node -> {
+ String plan = RelOptUtil.dumpPlan("", node, SqlExplainFormat.TEXT,
SqlExplainLevel.ALL_ATTRIBUTES);
+
+ String sanitized = plan.replaceAll("\\R", "");
+ java.util.regex.Matcher matcher = pattern.matcher(sanitized);
+
+ if (!matcher.matches())
+ return false;
+
+ String rowCntStr = matcher.group("rowcount");
+
+ return rowCountMatcher.matches(new
BigDecimal(rowCntStr).intValue());
+ };
+ }
+
+ /** Approximate equality check. */
+ static Matcher<Integer> approximatelyEqual(double expected) {
+ /** */
+ return new BaseMatcher<>() {
+ @Override public boolean matches(Object o) {
+ if (!(o instanceof Integer))
+ return false;
+
+ double val = ((Integer)o).doubleValue();
+ // IgniteMdRowCount.EQUI_COEFF with a bit overhead
Review Comment:
No more EQUI_COEFF in the code
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
