You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
[For now, I file this as a feature request; it might be considered a (performance) bug, though ...]
There appear to be no bulk implementations of binary MIN(a,b) / MAX(a,b) (e.g., in module batcalc).
Consequently, the translation of SQL queries using "between SYMMETRIC" is sub-optimal, using BAT iterators in MAL, rather than an efficient bulk implementation of binary MIN(a,b) / MAX(a,b).
Cf.:
$ mclient -s 'create table T (a int, b int, c int);'
operation successful
$ mclient -s 'PLAN select * from T where a between b and c;'
+------------------------------------------------------+
| rel |
+======================================================+
| project ( |
| | select ( |
| | | table(sys.t) [ t.a, t.b, t.c, t.%TID% NOT NULL ] |
| | ) [ t.b <= t.a <= t.c ] |
| ) [ t.a, t.b, t.c ] |
+------------------------------------------------------+
5 tuples
$ mclient -s 'EXPLAIN select * from T where a between b and c;' | grep -9 -i iterator
$ mclient -s 'PLAN select * from T where a between SYMMETRIC b and c;'
+---------------------------------------------------------------+
| rel |
+===============================================================+
| project ( |
| | select ( |
| | | table(sys.t) [ t.a, t.b, t.c, t.%TID% NOT NULL ] |
| | ) [ sys.sql_min(t.b, t.c) <= t.a <= sys.sql_max(t.b, t.c) ] |
| ) [ t.a, t.b, t.c ] |
+---------------------------------------------------------------+
5 tuples
Date: 2011-12-08 18:34:48 +0100
From: @drstmane
To: MonetDB5 devs <>
Version: -- development
CC: @njnes, @yzchang
Last updated: 2014-10-31 14:14:17 +0100
Comment 16640
Date: 2011-12-08 18:34:48 +0100
From: @drstmane
[For now, I file this as a feature request; it might be considered a (performance) bug, though ...]
There appear to be no bulk implementations of binary MIN(a,b) / MAX(a,b) (e.g., in module batcalc).
Consequently, the translation of SQL queries using "between SYMMETRIC" is sub-optimal, using BAT iterators in MAL, rather than an efficient bulk implementation of binary MIN(a,b) / MAX(a,b).
Cf.:
$ mclient -s 'create table T (a int, b int, c int);'
operation successful
$ mclient -s 'PLAN select * from T where a between b and c;'
+------------------------------------------------------+
| rel |
+======================================================+
| project ( |
| | select ( |
| | | table(sys.t) [ t.a, t.b, t.c, t.%TID% NOT NULL ] |
| | ) [ t.b <= t.a <= t.c ] |
| ) [ t.a, t.b, t.c ] |
+------------------------------------------------------+
5 tuples
$ mclient -s 'EXPLAIN select * from T where a between b and c;' | grep -9 -i iterator
$ mclient -s 'PLAN select * from T where a between SYMMETRIC b and c;'
+---------------------------------------------------------------+
| rel |
+===============================================================+
| project ( |
| | select ( |
| | | table(sys.t) [ t.a, t.b, t.c, t.%TID% NOT NULL ] |
| | ) [ sys.sql_min(t.b, t.c) <= t.a <= sys.sql_max(t.b, t.c) ] |
| ) [ t.a, t.b, t.c ] |
+---------------------------------------------------------------+
5 tuples
$ mclient -s 'EXPLAIN select * from T where a between SYMMETRIC b and c;' | grep -9 -i iterator
| X_36 := algebra.kunion(X_35,X_27); |
| X_37 := algebra.kdifference(X_36,X_16); |
| X_10:bat[:oid,:int] := sql.bind(X_2,"sys","t","a",0); |
| X_8:bat[:oid,:int] := sql.bind(X_2,"sys","t","a",2); |
| X_12 := algebra.kdifference(X_10,X_8); |
| X_13 := algebra.kunion(X_12,X_8); |
| X_5:bat[:oid,:int] := sql.bind(X_2,"sys","t","a",1); |
| X_14 := algebra.kunion(X_13,X_5); |
| X_156 := bat.new(nil:oid,nil:int); |
| barrier (X_159,X_160,X_161) := bat.newIterator(X_24); |
| X_163 := algebra.find(X_37,X_160); |
| X_165 := calc.max(X_161,X_163); |
| bat.insert(X_156,X_160,X_165); |
| redo (X_159,X_160,X_161) := bat.hasMoreElements(X_24); |
| exit (X_159,X_160,X_161); |
| X_38:bat[:oid,:int] := X_156; |
| X_68 := bat.mirror(X_10); |
| X_42 := bat.mirror(X_38); |
| X_41 := bat.mirror(X_5); |
| X_49 := algebra.join(X_44,X_38); |
| X_50:bat[:oid,:bit] := batcalc.<=(X_48,X_49); |
| X_51 := algebra.uselect(X_50,true); |
| X_53 := bat.reverse(X_51); |
| X_54 := algebra.join(X_53,X_44); |
| X_55 := bat.reverse(X_54); |
| X_83 := algebra.kunion(X_82,X_55); |
| X_84 := algebra.kdifference(X_83,X_16); |
| X_170 := bat.new(nil:oid,nil:int); |
| barrier (X_172,X_173,X_174) := bat.newIterator(X_24); |
| X_176 := algebra.find(X_37,X_173); |
| X_178 := calc.min(X_174,X_176); |
| bat.insert(X_170,X_173,X_178); |
| redo (X_172,X_173,X_174) := bat.hasMoreElements(X_24); |
| exit (X_172,X_173,X_174); |
| X_24 := nil:BAT; |
| X_37 := nil:BAT; |
| X_85:bat[:oid,:int] := X_170; |
| X_87 := bat.mirror(X_85); |
Comment 18080
Date: 2012-11-27 14:25:10 +0100
From: @yzchang
No test for performance issues
Comment 20363
Date: 2014-10-31 14:14:17 +0100
From: @sjoerdmullender
Oct2014 has been released.
The text was updated successfully, but these errors were encountered: