tidb
tidb copied to clipboard
Published
20 hours ago •
pingcap
pingcap
planner: tidb_shard does not work with mpp
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
CREATE TABLE `lineorder` (
`LO_ORDERKEY` int(11) NOT NULL,
`LO_LINENUMBER` int(11) NOT NULL,
`LO_CUSTKEY` int(11) DEFAULT NULL,
`LO_PARTKEY` int(11) DEFAULT NULL,
`LO_SUPPKEY` int(11) DEFAULT NULL,
`LO_ORDERDATE` int(11) DEFAULT NULL,
`LO_ORDPRIORITY` char(15) DEFAULT NULL,
`LO_SHIPPRIORITY` char(1) DEFAULT NULL,
`LO_QUANTITY` int(11) DEFAULT NULL,
`LO_EXTENDEDPRICE` decimal(10,0) DEFAULT NULL,
`LO_DISCOUNT` int(11) DEFAULT NULL,
`LO_REVENUE` decimal(10,0) DEFAULT NULL,
`LO_SUPPLYCOST` decimal(10,0) DEFAULT NULL,
`LO_TAX` int(11) DEFAULT NULL,
`LO_COMMITDATE` int(11) DEFAULT NULL,
`LO_SHIPMODE` char(10) DEFAULT NULL,
KEY `idx_lo_custkey` (`LO_CUSTKEY`),
UNIQUE KEY `UK_ORDR_LINE` ((tidb_shard(`LO_ORDERKEY`)),`LO_ORDERKEY`,`LO_LINENUMBER`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin /*T! SHARD_ROW_ID_BITS=6 */;
CREATE TABLE `date` (
`D_DATEKEY` int(11) NOT NULL,
`D_DATE` varchar(18) DEFAULT NULL,
`D_DATEOFWEEK` varchar(9) DEFAULT NULL,
`D_MONTH` varchar(9) DEFAULT NULL,
`D_YEAR` int(11) DEFAULT NULL,
`D_YEARMONTHNUM` int(11) DEFAULT NULL,
`D_YEARMONTH` char(7) DEFAULT NULL,
`D_DAYNUMINWEEK` int(11) DEFAULT NULL,
`D_DAYNUMINMONTH` int(11) DEFAULT NULL,
`D_DAYNUMINYEAR` int(11) DEFAULT NULL,
`D_MONTHNUMINYEAR` int(11) DEFAULT NULL,
`D_WEEKNUMINYEAR` int(11) DEFAULT NULL,
`D_SELLINGSEASON` char(15) DEFAULT NULL,
`D_LASTDAYINWEEKFL` tinyint(1) DEFAULT NULL,
`D_LASTDAYINMONTHFL` tinyint(1) DEFAULT NULL,
`D_HOLIDAYFL` tinyint(1) DEFAULT NULL,
`D_WEEKDAYFL` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`D_DATEKEY`) /*T![clustered_index] CLUSTERED */,
KEY `idx_d_date` (`D_DATE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
2. What did you expect to see? (Required)
explain SELECT SUM(LO_EXTENDEDPRICE*LO_DISCOUNT) AS REVENUE FROM HAT.LINEORDER, HAT.DATE WHERE LO_ORDERDATE = D_DATEKEY AND D_YEAR = 1993 AND LO_DISCOUNT BETWEEN 1 AND 3 AND LO_QUANTITY<25;
+------------------------------------------+--------------+--------------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------------------+--------------+--------------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------+
| HashAgg_36 | 1.00 | root | | funcs:sum(Column#37)->Column#36 |
| ââTableReader_38 | 1.00 | root | | data:ExchangeSender_37 |
| ââExchangeSender_37 | 1.00 | mpp[tiflash] | | ExchangeType: PassThrough |
| ââHashAgg_13 | 1.00 | mpp[tiflash] | | funcs:sum(Column#38)->Column#37 |
| ââProjection_45 | 766733.34 | mpp[tiflash] | | mul(hat.lineorder.lo_extendedprice, cast(hat.lineorder.lo_discount, decimal(20,0) BINARY))->Column#38 |
| ââProjection_34 | 766733.34 | mpp[tiflash] | | hat.lineorder.lo_extendedprice, hat.lineorder.lo_discount |
| ââHashJoin_35 | 766733.34 | mpp[tiflash] | | inner join, equal:[eq(hat.date.d_datekey, hat.lineorder.lo_orderdate)] |
| ââExchangeReceiver_23(Build) | 2.56 | mpp[tiflash] | | |
| â ââExchangeSender_22 | 2.56 | mpp[tiflash] | | ExchangeType: Broadcast |
| â ââSelection_21 | 2.56 | mpp[tiflash] | | eq(hat.date.d_year, 1993) |
| â ââTableFullScan_20 | 2557.00 | mpp[tiflash] | table:date | keep order:false |
| ââSelection_25(Probe) | 4992370.53 | mpp[tiflash] | | ge(hat.lineorder.lo_discount, 1), le(hat.lineorder.lo_discount, 3), lt(hat.lineorder.lo_quantity, 25), not(isnull(hat.lineorder.lo_orderdate)) |
| ââTableFullScan_24 | 601488614.00 | mpp[tiflash] | table:lineorder | keep order:false, stats:pseudo |
+------------------------------------------+--------------+--------------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------+
3. What did you see instead (Required)
tidb don't choose the mpp plan, causing AP QPS drops
explain SELECT SUM(LO_EXTENDEDPRICE*LO_DISCOUNT) AS REVENUE FROM HAT.LINEORDER, HAT.DATE WHERE LO_ORDERDATE = D_DATEKEY AND D_YEAR = 1993 AND LO_DISCOUNT BETWEEN 1 AND 3 AND LO_QUANTITY<25;
+------------------------------+--------------+--------------+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------+--------------+--------------+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| HashAgg_12 | 1.00 | root | | funcs:sum(Column#37)->Column#36 |
| ââProjection_67 | 78524728.51 | root | | mul(hat.lineorder.lo_extendedprice, cast(hat.lineorder.lo_discount, decimal(20,0) BINARY))->Column#37 |
| ââHashJoin_30 | 78524728.51 | root | | inner join, equal:[eq(hat.date.d_datekey, hat.lineorder.lo_orderdate)] |
| ââTableReader_49(Build) | 365.00 | root | | data:Selection_48 |
| â ââSelection_48 | 365.00 | cop[tiflash] | | eq(hat.date.d_year, 1993) |
| â ââTableFullScan_47 | 2557.00 | cop[tiflash] | table:date | keep order:false |
| ââProjection_43(Probe) | 600156011.00 | root | | hat.lineorder.lo_orderdate, hat.lineorder.lo_quantity, hat.lineorder.lo_extendedprice, hat.lineorder.lo_discount, tidb_shard(hat.lineorder.lo_orderkey) |
| ââTableReader_42 | 78524728.51 | root | | data:Selection_41 |
| ââSelection_41 | 78524728.51 | cop[tiflash] | | ge(hat.lineorder.lo_discount, 1), le(hat.lineorder.lo_discount, 3), lt(hat.lineorder.lo_quantity, 25), not(isnull(hat.lineorder.lo_orderdate)) |
| ââTableFullScan_40 | 600156011.00 | cop[tiflash] | table:lineorder | keep order:false |
+------------------------------+--------------+--------------+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
4. What is your TiDB version? (Required)
https://github.com/pingcap/tidb/commit/137041ac743c24e809e96ac5512dae429a02e7b1 @ release-6.2
mysql> set session tidb_enforce_mpp=ON;
Query OK, 0 rows affected (0.04 sec)
mysql> explain SELECT SUM(LO_EXTENDEDPRICE*LO_DISCOUNT) AS REVENUE FROM HAT.LINEORDER, HAT.DATE WHERE LO_ORDERDATE = D_DATEKEY AND D_YEAR = 1993 AND LO_DISCOUNT BETWEEN 1 AND 3 AND LO_QUANTITY<25;
+----------------------------------+--------------+--------------+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+----------------------------------+--------------+--------------+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| HashAgg_13 | 1.00 | root | | funcs:sum(Column#37)->Column#36 |
| └─Projection_72 | 4992370.53 | root | | mul(hat.lineorder.lo_extendedprice, cast(hat.lineorder.lo_discount, decimal(20,0) BINARY))->Column#37 |
| └─HashJoin_32 | 4992370.53 | root | | inner join, equal:[eq(hat.date.d_datekey, hat.lineorder.lo_orderdate)] |
| ├─TableReader_51(Build) | 365.00 | root | | data:Selection_50 |
| │ └─Selection_50 | 365.00 | cop[tiflash] | | eq(hat.date.d_year, 1993) |
| │ └─TableFullScan_49 | 2557.00 | cop[tiflash] | table:date | keep order:false |
| └─Projection_45(Probe) | 601488614.00 | root | | hat.lineorder.lo_orderdate, hat.lineorder.lo_quantity, hat.lineorder.lo_extendedprice, hat.lineorder.lo_discount, tidb_shard(hat.lineorder.lo_orderkey) |
| └─TableReader_44 | 4992370.53 | root | | data:Selection_43 |
| └─Selection_43 | 4992370.53 | cop[tiflash] | | ge(hat.lineorder.lo_discount, 1), le(hat.lineorder.lo_discount, 3), lt(hat.lineorder.lo_quantity, 25), not(isnull(hat.lineorder.lo_orderdate)) |
| └─TableFullScan_42 | 601488614.00 | cop[tiflash] | table:lineorder | keep order:false, stats:pseudo |
+----------------------------------+--------------+--------------+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
10 rows in set, 1 warning (0.04 sec)
mysql> show warnings;
+---------+------+---------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------------------------------------------------------------------------+
| Warning | 1105 | MPP mode may be blocked because column `hat.lineorder._v$_uk_ordr_line_0` is a virtual column which is not supported now. |
+---------+------+---------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)
related with https://github.com/pingcap/tidb/pull/30659 and https://github.com/pingcap/tidb/pull/36771. The special virtual column tidb_shard('xxx'):
- should not be considered as "used columns" in rule_partition_pruning
- should not affect the access paths selection
- should not be kept in projection if the related shard_index is not selected, which makes projection and following operators cannot be pushdown to tiflash
- should only be kept when shard_index is selected
Workaround by setting the tidb_isolation_read_engines variables in tidb
hat> select @@tidb_isolation_read_engines;
+-------------------------------+
| @@tidb_isolation_read_engines |
+-------------------------------+
| tikv,tiflash,tidb |
+-------------------------------+
hat> explain SELECT SUM(LO_EXTENDEDPRICE*LO_DISCOUNT) AS REVENUE FROM HAT.LINEORDER, HAT.DATE WHERE LO_ORDERDATE = D_DATEKEY AND D_YEAR = 1993 AND LO_DISCOUNT BETWEEN 1 AND 3 AND LO_QUANTITY<25;
+------------------------------+--------------+--------------+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------+--------------+--------------+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| HashAgg_12 | 1.00 | root | | funcs:sum(Column#37)->Column#36 |
| └─Projection_67 | 78562639.16 | root | | mul(hat.lineorder.lo_extendedprice, cast(hat.lineorder.lo_discount, decimal(20,0) BINARY))->Column#37 |
| └─HashJoin_30 | 78562639.16 | root | | inner join, equal:[eq(hat.date.d_datekey, hat.lineorder.lo_orderdate)] |
| ├─TableReader_49(Build) | 365.00 | root | | data:Selection_48 |
| │ └─Selection_48 | 365.00 | cop[tiflash] | | eq(hat.date.d_year, 1993) |
| │ └─TableFullScan_47 | 2557.00 | cop[tiflash] | table:date | keep order:false |
| └─Projection_43(Probe) | 600445758.00 | root | | hat.lineorder.lo_orderdate, hat.lineorder.lo_quantity, hat.lineorder.lo_extendedprice, hat.lineorder.lo_discount, tidb_shard(hat.lineorder.lo_orderkey) |
| └─TableReader_42 | 78562639.16 | root | | data:Selection_41 |
| └─Selection_41 | 78562639.16 | cop[tiflash] | | ge(hat.lineorder.lo_discount, 1), le(hat.lineorder.lo_discount, 3), lt(hat.lineorder.lo_quantity, 25), not(isnull(hat.lineorder.lo_orderdate)) |
| └─TableFullScan_40 | 600445758.00 | cop[tiflash] | table:lineorder | keep order:false |
+------------------------------+--------------+--------------+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
hat> set tidb_isolation_read_engines = "tiflash,tidb";
hat> select @@tidb_isolation_read_engines;
+-------------------------------+
| @@tidb_isolation_read_engines |
+-------------------------------+
| tiflash,tidb |
+-------------------------------+
hat> explain SELECT SUM(LO_EXTENDEDPRICE*LO_DISCOUNT) AS REVENUE FROM HAT.LINEORDER, HAT.DATE WHERE LO_ORDERDATE = D_DATEKEY AND D_YEAR = 1993 AND LO_DISCOUNT BETWEEN 1 AND 3 AND LO_QUANTITY<25;
+------------------------------------------+--------------+--------------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------------------+--------------+--------------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------+
| HashAgg_36 | 1.00 | root | | funcs:sum(Column#37)->Column#36 |
| └─TableReader_38 | 1.00 | root | | data:ExchangeSender_37 |
| └─ExchangeSender_37 | 1.00 | mpp[tiflash] | | ExchangeType: PassThrough |
| └─HashAgg_13 | 1.00 | mpp[tiflash] | | funcs:sum(Column#38)->Column#37 |
| └─Projection_45 | 78597834.48 | mpp[tiflash] | | mul(hat.lineorder.lo_extendedprice, cast(hat.lineorder.lo_discount, decimal(20,0) BINARY))->Column#38 |
| └─Projection_34 | 78597834.48 | mpp[tiflash] | | hat.lineorder.lo_extendedprice, hat.lineorder.lo_discount |
| └─HashJoin_35 | 78597834.48 | mpp[tiflash] | | inner join, equal:[eq(hat.date.d_datekey, hat.lineorder.lo_orderdate)] |
| ├─ExchangeReceiver_23(Build) | 365.00 | mpp[tiflash] | | |
| │ └─ExchangeSender_22 | 365.00 | mpp[tiflash] | | ExchangeType: Broadcast |
| │ └─Selection_21 | 365.00 | mpp[tiflash] | | eq(hat.date.d_year, 1993) |
| │ └─TableFullScan_20 | 2557.00 | mpp[tiflash] | table:date | keep order:false |
| └─Selection_25(Probe) | 78597834.48 | mpp[tiflash] | | ge(hat.lineorder.lo_discount, 1), le(hat.lineorder.lo_discount, 3), lt(hat.lineorder.lo_quantity, 25), not(isnull(hat.lineorder.lo_orderdate)) |
| └─TableFullScan_24 | 600714752.00 | mpp[tiflash] | table:lineorder | keep order:false |
+------------------------------------------+--------------+--------------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------+
