Changeset: 8df24af4526d for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=8df24af4526d
Added Files:
sql/test/miscellaneous/Tests/simple_plans.stable.out.single
Modified Files:
sql/server/rel_unnest.c
sql/test/BugTracker-2018/Tests/sqlitelogictest-cast-null-add.Bug-6630.stable.out
sql/test/BugTracker-2018/Tests/sqlitelogictest-groupby-having-in.Bug-6560.stable.out
sql/test/BugTracker-2020/Tests/select-and.Bug-6878.stable.out
Branch: Jun2020
Log Message:
Performance fix for tpcds queries. Reset cardinalities for expressions at
join/select and other relations as well. There's a query failing at subquery4,
but it only happens with forcemito, so I guess the problem is somewhere else
diffs (truncated from 508 to 300 lines):
diff --git a/sql/server/rel_unnest.c b/sql/server/rel_unnest.c
--- a/sql/server/rel_unnest.c
+++ b/sql/server/rel_unnest.c
@@ -1609,40 +1609,83 @@ exp_reset_card_and_freevar(mvc *sql, sql
reset_freevar(e); /* unnesting is done, we can remove the freevar flag
*/
if (!rel->l)
return e;
- if (is_groupby(rel->op)) {
+
+ switch(rel->op){
+ case op_basetable:
+ case op_truncate:
+ case op_topn:
+ case op_sample:
+ case op_insert:
+ case op_update:
+ case op_delete:
+ case op_ddl:
+ case op_table:
+ break;
+ case op_select:
+ case op_join:
+ case op_left:
+ case op_right:
+ case op_full:
+ case op_semi:
+ case op_anti:
+ case op_project:
+ case op_union:
+ case op_inter:
+ case op_except: {
+ switch(e->type) {
+ case e_aggr:
+ case e_func: {
+ e->card = exps_card(e->l);
+ } break;
+ case e_column: {
+ sql_rel *l = rel->l, *r = rel->r, *ll = NULL, *rr =
NULL;
+ sql_exp *le = NULL, *re = NULL;
+
+ if (is_set(rel->op)) {
+ le = rel_find_exp_and_corresponding_rel(l, e,
&ll);
+ re = rel_find_exp_and_corresponding_rel(r, e,
&rr);
+ if (ll && rr) /* TODO fix this? */
+ e->card = MAX(ll->card, rr->card);
+ } else {
+ if (l && (le =
rel_find_exp_and_corresponding_rel(l, e, &ll)) && ll) {
+ e->card = ll->card;
+ } else if (!is_simple_project(rel->op) && r &&
(re = rel_find_exp_and_corresponding_rel(r, e, &rr)) && rr) {
+ e->card = rr->card;
+ }
+ }
+ } break;
+ case e_convert: {
+ e->card = exp_card(e->l);
+ } break;
+ case e_cmp: {
+ if (e->flag == cmp_or || e->flag == cmp_filter) {
+ e->card = MAX(exps_card(e->l), exps_card(e->r));
+ } else if (e->flag == cmp_in || e->flag == cmp_notin) {
+ e->card = MAX(exp_card(e->l), exps_card(e->r));
+ } else {
+ e->card = MAX(exp_card(e->l), exp_card(e->r));
+ if (e->f)
+ e->card = MAX(e->card, exp_card(e->f));
+ }
+ } break;
+ case e_atom:
+ case e_psm:
+ break;
+ }
+ } break;
+ case op_groupby: {
switch(e->type) {
case e_aggr:
case e_column:
e->card = rel->card;
break;
- case e_func:
- case e_convert:
- case e_cmp:
- case e_atom:
- case e_psm:
+ default:
break;
}
+ } break;
}
- if (!is_simple_project(rel->op)) /* only need to fix projections */
- return e;
- sql_rel *l;
- /* need card of lower relation */
- switch(e->type) {
- case e_func:
- case e_column:
- case e_convert:
- l = rel->l;
- if (e->card < l->card)
- e->card = l->card;
- if (need_distinct(rel)) /* Need distinct, all expressions
should have CARD_AGGR at max */
- e->card = MIN(e->card, CARD_AGGR);
- break;
- case e_aggr: /* should have been corrected by rewrites already */
- case e_cmp:
- case e_atom:
- case e_psm:
- break;
- }
+ if (is_simple_project(rel->op) && need_distinct(rel)) /* Need distinct,
all expressions should have CARD_AGGR at max */
+ e->card = MIN(e->card, CARD_AGGR);
return e;
}
diff --git
a/sql/test/BugTracker-2018/Tests/sqlitelogictest-cast-null-add.Bug-6630.stable.out
b/sql/test/BugTracker-2018/Tests/sqlitelogictest-cast-null-add.Bug-6630.stable.out
---
a/sql/test/BugTracker-2018/Tests/sqlitelogictest-cast-null-add.Bug-6630.stable.out
+++
b/sql/test/BugTracker-2018/Tests/sqlitelogictest-cast-null-add.Bug-6630.stable.out
@@ -50,14 +50,14 @@ stdout of test 'sqlitelogictest-cast-nul
% 1 # length
[ NULL ]
#SELECT DISTINCT CAST ( + 2 + + CAST ( NULL AS INTEGER ) - - ( + 69 ) AS
BIGINT ) FROM tab2 AS cor0; -- NULL
-% .%1 # table_name
-% %1 # name
+% .%2 # table_name
+% %2 # name
% bigint # type
% 1 # length
[ NULL ]
#SELECT ALL CAST ( - 8 + CAST ( NULL AS INTEGER ) + 43 AS BIGINT ) FROM tab2
cor0; -- A single column with 3 NULL values
-% .%1 # table_name
-% %1 # name
+% .%2 # table_name
+% %2 # name
% bigint # type
% 1 # length
[ NULL ]
@@ -73,7 +73,7 @@ stdout of test 'sqlitelogictest-cast-nul
[ NULL ]
#SELECT ALL CAST ( col1 / - - ( + CAST ( NULL AS INTEGER ) ) AS BIGINT ) col0,
# CAST ( 39 + + + CAST ( NULL AS INTEGER ) + + - 10 + col1 / - col0
AS BIGINT ) AS col1 FROM tab2;
-% sys., . # table_name
+% sys., sys. # table_name
% col0, col1 # name
% bigint, bigint # type
% 1, 1 # length
diff --git
a/sql/test/BugTracker-2018/Tests/sqlitelogictest-groupby-having-in.Bug-6560.stable.out
b/sql/test/BugTracker-2018/Tests/sqlitelogictest-groupby-having-in.Bug-6560.stable.out
---
a/sql/test/BugTracker-2018/Tests/sqlitelogictest-groupby-having-in.Bug-6560.stable.out
+++
b/sql/test/BugTracker-2018/Tests/sqlitelogictest-groupby-having-in.Bug-6560.stable.out
@@ -32,7 +32,7 @@ stdout of test 'sqlitelogictest-groupby-
% int # type
% 1 # length
#SELECT DISTINCT - NULLIF ( - 85, 24 ) * tab1.col1 FROM tab1 GROUP BY
tab1.col1;
-% .%1 # table_name
+% sys.%1 # table_name
% %1 # name
% bigint # type
% 4 # length
diff --git a/sql/test/BugTracker-2020/Tests/select-and.Bug-6878.stable.out
b/sql/test/BugTracker-2020/Tests/select-and.Bug-6878.stable.out
--- a/sql/test/BugTracker-2020/Tests/select-and.Bug-6878.stable.out
+++ b/sql/test/BugTracker-2020/Tests/select-and.Bug-6878.stable.out
@@ -44,7 +44,7 @@ stdout of test 'select-and.Bug-6878` in
[ 31 ]
[ 31 ]
#select (0.72/2147483648.0)*(temp_table.mmax -
temp_table.mmin)+temp_table.mmin as col1 from temp_value, temp_table where
temp_value.vvalue<=temp_table.norm_max and
temp_value.vvalue>temp_table.norm_min order by col1;
-% . # table_name
+% sys. # table_name
% col1 # name
% double # type
% 24 # length
diff --git a/sql/test/miscellaneous/Tests/simple_plans.stable.out.single
b/sql/test/miscellaneous/Tests/simple_plans.stable.out.single
new file mode 100644
--- /dev/null
+++ b/sql/test/miscellaneous/Tests/simple_plans.stable.out.single
@@ -0,0 +1,335 @@
+stdout of test 'simple_plans` in directory 'sql/test/miscellaneous` itself:
+
+
+# 21:22:10 >
+# 21:22:10 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-857522" "--port=34649"
+# 21:22:10 >
+
+#start transaction;
+#create table myy (col1 int, col2 int);
+#insert into myy values (1, 1), (2, 0), (3,3), (4,2);
+[ 4 ]
+#select distinct col1 + col2 from myy order by col1 + col2;
+% sys.%2 # table_name
+% %2 # name
+% bigint # type
+% 1 # length
+[ 2 ]
+[ 6 ]
+#plan select distinct col1 + col2 from myy order by col1 + col2;
+% .plan # table_name
+% rel # name
+% clob # type
+% 110 # length
+project (
+| group by (
+| | project (
+| | | table(sys.myy) [ "myy"."col1", "myy"."col2" ] COUNT
+| | ) [ sys.sql_add(bigint["myy"."col1"] as "myy"."col1", bigint["myy"."col2"]
as "myy"."col2") as "%2"."%2" ]
+| ) [ "%2"."%2" ] [ "%2"."%2" ]
+) [ "%2"."%2" ] [ "%2"."%2" ASC ]
+#create table myx (x uuid, y uuid);
+#plan select * from myx where x in ('1aea00e5db6e0810b554fde31d961965') or y =
'1aea00e5db6e0810b554fde31d961965';
+% .plan # table_name
+% rel # name
+% clob # type
+% 138 # length
+project (
+| select (
+| | table(sys.myx) [ "myx"."x", "myx"."y" ] COUNT
+| ) [ ("myx"."x" = uuid[char(32) "1aea00e5db6e0810b554fde31d961965"]) or
("myx"."y" = uuid[char(32) "1aea00e5db6e0810b554fde31d961965"]) ]
+) [ "myx"."x", "myx"."y" ]
+#plan select * from myx where x in ('1aea00e5db6e0810b554fde31d961965') or y
is null;
+% .plan # table_name
+% rel # name
+% clob # type
+% 102 # length
+project (
+| select (
+| | table(sys.myx) [ "myx"."x", "myx"."y" ] COUNT
+| ) [ ("myx"."x" = uuid[char(32) "1aea00e5db6e0810b554fde31d961965"]) or
("myx"."y" * = uuid "NULL") ]
+) [ "myx"."x", "myx"."y" ]
+#plan select * from myx where x in ('1aea00e5db6e0810b554fde31d961965',
'1aea00e5db6e0810b554fde31d961966') or y = '1aea00e5db6e0810b554fde31d961967';
+% .plan # table_name
+% rel # name
+% clob # type
+% 192 # length
+project (
+| select (
+| | table(sys.myx) [ "myx"."x", "myx"."y" ] COUNT
+| ) [ ("myx"."x" in (uuid[char(32) "1aea00e5db6e0810b554fde31d961965"],
uuid[char(32) "1aea00e5db6e0810b554fde31d961966"])) or ("myx"."y" =
uuid[char(32) "1aea00e5db6e0810b554fde31d961967"]) ]
+) [ "myx"."x", "myx"."y" ]
+#insert into myx values ('1aea00e5db6e0810b554fde31d961965',
'1aea00e5db6e0810b554fde31d961967');
+[ 1 ]
+#select * from myx where x in ('1aea00e5db6e0810b554fde31d961966') or y is not
null;
+% sys.myx, sys.myx # table_name
+% x, y # name
+% uuid, uuid # type
+% 36, 36 # length
+[ 1aea00e5-db6e-0810-b554-fde31d961965,
1aea00e5-db6e-0810-b554-fde31d961967 ]
+#select * from myx where x in ('1aea00e5db6e0810b554fde31d961966') or y is
null;
+% sys.myx, sys.myx # table_name
+% x, y # name
+% uuid, uuid # type
+% 36, 36 # length
+#CREATE TABLE tbl_ProductSales (ColID int, Product_Category varchar(64),
Product_Name varchar(64), TotalSales int);
+#INSERT INTO tbl_ProductSales VALUES (1,'Game','Mobo Game',200),(2,'Game','PKO
Game',400),(3,'Fashion','Shirt',500),(4,'Fashion','Shorts',100);
+[ 4 ]
+#CREATE TABLE another_T (col1 INT, col2 INT, col3 INT, col4 INT, col5 INT,
col6 INT, col7 INT, col8 INT);
+#INSERT INTO another_T VALUES (1,2,3,4,5,6,7,8), (11,22,33,44,55,66,77,88),
(111,222,333,444,555,666,777,888), (1111,2222,3333,4444,5555,6666,7777,8888);
+[ 4 ]
+#EXPLAIN SELECT 1 FROM another_t t1 INNER JOIN another_t t2 ON t1.col1 BETWEEN
t2.col1 AND t2.col1;
+% .explain # table_name
+% mal # name
+% clob # type
+% 175 # length
+function user.s20_0():void;
+ X_2:void := querylog.define("explain select 1 from another_t t1 inner join
another_t t2 on t1.col1 between t2.col1 and t2.col1;":str, "default_pipe":str,
23:int);
+ X_32:bat[:str] := bat.pack(".%3":str);
+ X_33:bat[:str] := bat.pack("%3":str);
+ X_34:bat[:str] := bat.pack("tinyint":str);
+ X_35:bat[:int] := bat.pack(1:int);
+ X_36:bat[:int] := bat.pack(0:int);
+ X_5:int := sql.mvc();
+ C_6:bat[:oid] := sql.tid(X_5:int, "sys":str, "another_t":str);
+ X_12:bat[:int] := sql.bind(X_5:int, "sys":str, "another_t":str,
"col1":str, 0:int);
+ X_16:bat[:int] := algebra.projection(C_6:bat[:oid], X_12:bat[:int]);
+ (X_18:bat[:oid], X_19:bat[:oid]) := algebra.rangejoin(X_16:bat[:int],
X_16:bat[:int], X_16:bat[:int], nil:BAT, nil:BAT, true:bit, true:bit,
false:bit, false:bit, nil:lng);
+ X_27:bat[:int] := algebra.projection(X_18:bat[:oid], X_16:bat[:int]);
+ X_30:bat[:bte] := algebra.project(X_27:bat[:int], 1:bte);
+ sql.resultSet(X_32:bat[:str], X_33:bat[:str], X_34:bat[:str],
X_35:bat[:int], X_36:bat[:int], X_30:bat[:bte]);
+end user.s20_0;
+#inline actions= 0 time=0 usec
+#remap actions= 0 time=1 usec
+#costmodel actions= 1 time=1 usec
+#coercion actions= 0 time=1 usec
+#aliases actions= 1 time=6 usec
+#evaluate actions= 0 time=5 usec
+#emptybind actions= 0 time=0 usec
+#pushselect actions= 0 time=11 usec
+#aliases actions= 0 time=1 usec
+#mitosis actions=4 time=28 usec
+#mergetable actions= 5 time=65 usec
+#deadcode actions= 9 time=15 usec
+#aliases actions= 0 time=0 usec
+#constants actions= 5 time=7 usec
+#commonTerms actions= 0 time=12 usec
+#projectionpath actions= 0 time=6 usec
+#deadcode actions= 0 time=10 usec
+#reorder actions= 1 time=31 usec
+#matpack actions= 1 time=11 usec
+#dataflow actions= 1 time=28 usec
+#multiplex actions= 0 time=2 usec
+#profiler actions= 1 time=2 usec
+#candidates actions= 1 time=2 usec
+#deadcode actions= 0 time=12 usec
+#postfix actions= 0 time=9 usec
+#wlc actions= 0 time=0 usec
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
