65 lines
1.8 KiB
Text
65 lines
1.8 KiB
Text
--source include/have_hypergraph.inc
|
|
--source include/hash_join.inc
|
|
--source include/elide_costs.inc
|
|
|
|
--echo #
|
|
--echo # Bug#34940000 Hash join execution may be ineficcient if probe input is empty
|
|
--echo #
|
|
|
|
CREATE TABLE t1(
|
|
a INT NOT NULL PRIMARY KEY,
|
|
b INT NOT NULL,
|
|
c INT NOT NULL
|
|
);
|
|
|
|
INSERT INTO t1
|
|
WITH RECURSIVE qn(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM qn WHERE n<20)
|
|
SELECT n, n%7, n%5 FROM qn;
|
|
|
|
ANALYZE TABLE t1 UPDATE HISTOGRAM ON b,c;
|
|
ANALYZE TABLE t1;
|
|
|
|
# We should check if the probe (outer) input is empty first if it is an innerjoin \ or semijoin
|
|
# where reading the first row from the probe is cheaper than from the build (inner) input.
|
|
# If the probe is empty, we can skip reading the build input.
|
|
|
|
# Inner join.
|
|
--replace_regex $elide_time
|
|
EXPLAIN ANALYZE SELECT 1 FROM t1 JOIN
|
|
(SELECT SUM(x1.b) s FROM t1 x1, t1 x2 WHERE x1.b<x2.b GROUP BY x1.c) d1
|
|
ON c=d1.s AND c*2<0;
|
|
|
|
# Semijoin.
|
|
--replace_regex $elide_time
|
|
EXPLAIN ANALYZE SELECT 1 FROM t1 x1 WHERE b IN
|
|
(SELECT c FROM t1 x2 WHERE b>0) AND x1.c*2<0;
|
|
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Bug#34155137 STRAIGHT_JOIN GIVES DIFFERENT ORDER FOR HASH JOIN WITH HYPERGRAPH
|
|
--echo #
|
|
|
|
CREATE TABLE small_tab(x INT);
|
|
INSERT INTO small_tab VALUES (1), (2), (3), (4);
|
|
|
|
CREATE TABLE large_tab(x INT);
|
|
INSERT INTO large_tab SELECT x FROM small_tab;
|
|
INSERT INTO large_tab SELECT x FROM large_tab;
|
|
INSERT INTO large_tab SELECT x FROM large_tab;
|
|
INSERT INTO large_tab SELECT x FROM large_tab;
|
|
INSERT INTO large_tab SELECT x FROM large_tab;
|
|
|
|
SELECT COUNT(*) FROM small_tab;
|
|
SELECT COUNT(*) FROM large_tab;
|
|
|
|
ANALYZE TABLE large_tab;
|
|
ANALYZE TABLE small_tab;
|
|
|
|
--replace_regex $elide_costs
|
|
EXPLAIN FORMAT=TREE SELECT x FROM large_tab STRAIGHT_JOIN small_tab USING (x);
|
|
--replace_regex $elide_costs
|
|
EXPLAIN FORMAT=TREE SELECT x FROM small_tab STRAIGHT_JOIN large_tab USING (x);
|
|
|
|
DROP TABLE large_tab;
|
|
DROP TABLE small_tab;
|