Hi all,
Can someone explain me why this simple query takes so long to execute on
MonetDB 5 server v11.17.13 "Jan2014-SP1"?
sql>select * from fp_tree limit 1;
+-----------------------------+
| path |
+=============================+
| ;8;60;379;519;1241;174;692; |
+-----------------------------+
1 tuple (1.2s)
Table fp_tree has one column path with data type string.
Some useful statistics of table fp_tree:
sql>select count(*) from fp_tree;
+--------+
| L1 |
+========+
| 416499 |
+--------+
1 tuple (2.801ms)
sql>select max(length(path)) from fp_tree;
+------+
| L1 |
+======+
| 1275 |
+------+
1 tuple (254.742ms)
sql>select min(length(path)) from fp_tree;
+------+
| L1 |
+======+
| 3 |
+------+
1 tuple (272.186ms)
sql>select avg(length(path)) from fp_tree;
+--------------------------+
| L1 |
+==========================+
| 202.73579288305606 |
+--------------------------+
1 tuple (331.306ms)
Query trace:
+---------+-------------------------------------------------------------------+
| ticks | stmt
|
+=========+===================================================================+
| 3 | X_2 := sql.mvc();
|
| 29 | X_35:bat[:oid,:oid] =[104124] :=
sql.tid(X_2=0,"sys","fp |
: : _tree",0,4);
:
| 6 | X_8=[0] := sql.bind(X_2=0,"sys","fp_tree","path",1);
|
| 40 | (X_52:bat[:oid,:oid] =[0],X_53:bat[:oid,:str]
=[0]) := sql.bind(X_2=0,"sys","fp_tree","path",2,3,4);
:
| 8 | X_45:bat[:oid,:str] =[104127] :=
sql.bind(X_2=0,"sys"," |
: : fp_tree","path",0,3,4);
:
| 3 | X_57=[104127] :=
sql.delta(X_45=:bat[:oid,:st |
: :
r][104127],X_52=:bat[:oid,:oid][0],X_53=:bat[ :
: : :oid,:str][0],X_8=[0]);
:
| 4 | X_41:bat[:oid,:oid] =[104127] :=
sql.tid(X_2=0,"sys","f |
: : p_tree",3,4);
:
| 8 | X_61=[104127] :=
algebra.leftfetchjoin(X_41=:b |
: : at[:oid,:oid][104127],X_57=[104127]);
:
| 10 | X_65=[1] :=
algebra.subslice(X_61=[104127],0: |
: : wrd,0:wrd);
:
| 6 | X_71=[1] :=
algebra.leftfetchjoin(X_65=[1],X |
: : _61=[104127]);
:
| 1 | language.pass(X_61=[104127]);
|
| 24 | (X_50:bat[:oid,:oid] =[0],X_51:bat[:oid,:str]
=[0]) := sql.bind(X_2=0,"sys","fp_tree","path",2,2,4);
:
| 5 | X_44:bat[:oid,:str] =[104124] :=
sql.bind(X_2=0,"sys"," |
: : fp_tree","path",0,2,4);
:
| 3 | X_56=[104124] :=
sql.delta(X_44=:bat[:oid,:st |
: :
r][104124],X_50=:bat[:oid,:oid][0],X_51=:bat[ :
: : :oid,:str][0]);
:
| 3 | X_39:bat[:oid,:oid] =[104124] :=
sql.tid(X_2=0,"sys","f |
: : p_tree",2,4);
:
| 5 | X_60=[104124] :=
algebra.leftfetchjoin(X_39=:b |
: : at[:oid,:oid][104124],X_56=[104124]);
:
| 8 | X_64=[1] :=
algebra.subslice(X_60=[104124],0: |
: : wrd,0:wrd);
:
| 4 | X_70=[1] :=
algebra.leftfetchjoin(X_64=[1],X |
: : _60=[104124]);
:
| 1 | language.pass(X_60=[104124]);
|
| 21 | (X_48:bat[:oid,:oid] =[0],X_49:bat[:oid,:str]
=[0]) := sql.bind(X_2=0,"sys","fp_tree","path",2,1,4);
:
| 5 | X_43:bat[:oid,:str] =[104124] :=
sql.bind(X_2=0,"sys"," |
: : fp_tree","path",0,1,4);
:
| 1 | X_55=[104124] :=
sql.delta(X_43=:bat[:oid,:st |
: :
r][104124],X_48=:bat[:oid,:oid][0],X_49=:bat[ :
: : :oid,:str][0]);
:
| 3 | X_37:bat[:oid,:oid] =[104124] :=
sql.tid(X_2=0,"sys","f |
: : p_tree",1,4);
:
| 5 | X_59=[104124] :=
algebra.leftfetchjoin(X_37=:b |
: : at[:oid,:oid][104124],X_55=[104124]);
:
| 8 | X_63=[1] :=
algebra.subslice(X_59=[104124],0: |
: : wrd,0:wrd);
:
| 5 | X_69=[1] :=
algebra.leftfetchjoin(X_63=[1],X |
: : _59=[104124]);
:
| 1 | language.pass(X_59=[104124]);
|
| 23 | (X_46:bat[:oid,:oid] =[0],X_47:bat[:oid,:str]
=[0]) := sql.bind(X_2=0,"sys","fp_tree","path",2,0,4);
:
| 9 | X_42:bat[:oid,:str] =[104124] :=
sql.bind(X_2=0,"sys","f |
: : p_tree","path",0,0,4);
:
| 2 | X_54=[104124] :=
sql.delta(X_42=:bat[:oid,:str] |
: :
[104124],X_46=:bat[:oid,:oid][0],X_47=:bat[:o :
: : id,:str][0]);
:
| 6 | X_58=[104124] :=
algebra.leftfetchjoin(X_35=:ba |
: : t[:oid,:oid][104124],X_54=[104124]);
:
| 8 | X_62=[1] :=
algebra.subslice(X_58=[104124],0:w |
: : rd,0:wrd);
:
| 10 | X_72=[1] := mat.packIncrement(X_62=[1],4);
|
| 3 | X_74=[2] :=
mat.packIncrement(X_72=[2],X_63=[1]);
:
| 1 | language.pass(X_63=[1]);
|
| 2 | X_75=[3] :=
mat.packIncrement(X_74=[3],X_64=[1]);
:
| 1 | language.pass(X_64=[1]);
|
| 3 | X_66=[4] :=
mat.packIncrement(X_75=[4],X_65=[1]);
:
| 1 | language.pass(X_65=[1]);
|
| 9 | X_11=[1] :=
algebra.subslice(X_66=[4],0:wrd,0 |
: : :wrd);
:
| 5 | X_68=[1] :=
algebra.leftfetchjoin(X_62=[1],X_ |
: : 58=[104124]);
:
| 1 | language.pass(X_62=[1]);
|
| 6 | language.pass(X_58=[104124]);
|
| 1019824 | X_77=[1] := mat.packIncrement(X_68=[1],4);
|
| 8 | X_78=[2] :=
mat.packIncrement(X_77=[2],X_69=< |
: : tmp_1660>[1]);
:
| 7 | X_79=[3] :=
mat.packIncrement(X_78=[3],X_70=< |
: : tmp_1560>[1]);
:
| 18 | X_67=[4] :=
mat.packIncrement(X_79=[4],X_71=< |
: : tmp_1357>[1]);
:
| 18 | X_12=[1] :=
algebra.leftfetchjoin(X_11=[1],X |
: : _67=[4]);
:
| 1095479 | barrier X_89 := language.dataflow();
|
| 13 | X_13 := sql.resultSet(1,1,X_12=[1]);
|
| 7 |
sql.rsColumn(X_13=7,"sys.fp_tree","path","clob",0,0,X_12=[1]);
:
| 2 | X_17 := io.stdout();
|
| 33 | sql.exportResult(X_17=="104d2":streams,X_13=7);
|
| 0 | end s2_2;
|
| 1095725 | function user.s2_2();
|
| 1095772 | X_5:void := user.s2_2();
|
+---------+-------------------------------------------------------------------+
56 tuples (1.1s)
Why does mat.packIncrement takes so much time?
Thanks in advance,
Nik
N.H. Schuiling