Hi,
Could you please take a look at the second trace. I rewrote the query
and it's performing very well. Is that the way I should do all the
queries since most queries require filters and can I do complex queries
like that? Thanks again. Dariusz.
:0[0]:W
Strace select f25,count(*) from trw100 where f25 in ('F','M') group by f25;
:13422[0]:R
[ 0 usec mdb.setTimer(true); ]
[ 15000 usec _3 := sql.bind("sys","trw100","f25",0); ]
[ 16000 usec _8 := sql.bind("sys","trw100","f25",1); ]
[ 0 usec _10 := algebra.kunion(:bat[:oid,:str][96240405],:bat[:oid,:str][0]); ]
[ 16000 usec _3 := nil; ]
[ 15000 usec _8 := nil; ]
[ 16000 usec _11 := sql.bind("sys","trw100","f25",2); ]
[ 0 usec _13 := algebra.kdifference([96240405],:bat[:oid,:str][0]); ]
[ 16000 usec _10 := nil; ]
[ 15000 usec _14 := algebra.kunion([96240405],:bat[:oid,:str][0]); ]
[ 16000 usec _13 := nil; ]
[ 15000 usec _11 := nil; ]
[ 0 usec _15 := sql.bind_dbat("sys","trw100",1); ]
[ 16000 usec _16 := bat.reverse(:bat[:oid,:oid][0]); ]
[ 16000 usec _15 := nil; ]
[ 15000 usec _17 := algebra.kdifference([96240405],<~tmp_17017>[0]); ]
[ 0 usec _16 := nil; ]
[ 16000 usec _18 := bat.reverse([96240405]); ]
[ 16000 usec _17 := nil; ]
[ 15000 usec _19 := bat.new(nil,nil); ]
[ 0 usec bat.append(:bat[:oid,:str][1],"F",true); ]
[ 16000 usec bat.append(:bat[:oid,:str][2],"M",true); ]
[ 15000 usec _25 := bat.reverse(:bat[:oid,:str][2]); ]
[ 16000 usec _19 := nil; ]
[ 10953000 usec _26 := algebra.semijoin(<~tmp_17325>[96240405],<~tmp_17320>[2]); ]
[ 16000 usec _18 := nil; ]
[ 15000 usec _25 := nil; ]
[ 0 usec _27 := bat.reverse([93838869]); ]
[ 16000 usec _26 := nil; ]
[ 16000 usec _29 := algebra.markT(<~tmp_17323>[93838869],0@0); ]
[ 15000 usec _27 := nil; ]
[ 16000 usec _30 := bat.reverse([93838869]); ]
[ 0 usec _29 := nil; ]
[ 437000 usec _31 := algebra.join(<~tmp_17325>[93838869],[96240405]); ]
[ 79000 usec _30 := nil; ]
[ 15000 usec _14 := nil; ]
[ 953000 usec (ext37,grp35) := group.new([93838869]); ]
[ 16000 usec _34 := bat.mirror([2]); ]
[ 16000 usec ext37 := nil; ]
[ 15000 usec _35 := algebra.join([2],[93838869]); ]
[ 31000 usec _31 := nil; ]
[ 360000 usec _36 := aggr.count([93838869],[93838869],[2]); ]
[ 47000 usec grp35 := nil; ]
[ 15000 usec _34 := nil; ]
[ 0 usec _37 := sql.resultSet(2,1,[2]); ]
[ 16000 usec sql.rsColumn(14,"sys.trw100","f25","varchar",1,0,[2]); ]
[ 16000 usec _35 := nil; ]
[ 15000 usec sql.rsColumn(14,"sys.trw100","count_f25","wrd",64,0,:bat[:oid,:wrd][2]); ]
[ 16000 usec _36 := nil; ]
[ 15000 usec _45 := io.stdout(); ]
&1 14 2 2 2
% sys.trw100, sys.trw100 # table_name
% f25, count_f25 # name
% varchar, wrd # type
% 1, 8 # length
[ "F", 29577383 ]
[ "M", 64261486 ]
[ 16000 usec sql.exportResult(137931296,14,""); ]
[ 13422000 usec user.s1_1("F","M"); ]
:13437[0]:R
:86969[0]:W
Strace select * from (select f25,count(*) from trw100 group by f25) as t1 where t1.f25 in ('F','M');
:89953[0]:R
[ 0 usec mdb.setTimer(true); ]
[ 15000 usec _3 := sql.bind("sys","trw100","f1",0); ]
[ 16000 usec _8 := sql.bind("sys","trw100","f1",1); ]
[ 0 usec _10 := algebra.kunion(:bat[:oid,:str][96240405],:bat[:oid,:str][0]); ]
[ 15000 usec _3 := nil; ]
[ 16000 usec _8 := nil; ]
[ 16000 usec _11 := sql.bind("sys","trw100","f1",2); ]
[ 0 usec _13 := algebra.kdifference([96240405],:bat[:oid,:str][0]); ]
[ 15000 usec _10 := nil; ]
[ 16000 usec _14 := algebra.kunion([96240405],:bat[:oid,:str][0]); ]
[ 16000 usec _13 := nil; ]
[ 15000 usec _11 := nil; ]
[ 0 usec _15 := sql.bind_dbat("sys","trw100",1); ]
[ 16000 usec _16 := bat.reverse(:bat[:oid,:oid][0]); ]
[ 15000 usec _15 := nil; ]
[ 16000 usec _17 := algebra.kdifference([96240405],<~tmp_17017>[0]); ]
[ 0 usec _14 := nil; ]
[ 16000 usec _16 := nil; ]
[ 15000 usec _19 := algebra.markT([96240405],0@0); ]
[ 16000 usec _17 := nil; ]
[ 0 usec _20 := bat.reverse([96240405]); ]
[ 16000 usec _19 := nil; ]
[ 15000 usec _21 := sql.bind("sys","trw100","f25",0); ]
[ 16000 usec _23 := sql.bind("sys","trw100","f25",1); ]
[ 0 usec _24 := algebra.kunion(:bat[:oid,:str][96240405],:bat[:oid,:str][0]); ]
[ 15000 usec _21 := nil; ]
[ 16000 usec _23 := nil; ]
[ 16000 usec _25 := sql.bind("sys","trw100","f25",2); ]
[ 15000 usec _26 := algebra.kdifference([96240405],:bat[:oid,:str][0]); ]
[ 0 usec _24 := nil; ]
[ 16000 usec _27 := algebra.kunion([96240405],:bat[:oid,:str][0]); ]
[ 16000 usec _26 := nil; ]
[ 15000 usec _25 := nil; ]
[ 0 usec _28 := algebra.join(<~tmp_17323>[96240405],[96240405]); ]
[ 16000 usec _20 := nil; ]
[ 15000 usec _27 := nil; ]
[ 938000 usec (ext32,grp30) := group.new([96240405]); ]
[ 16000 usec _31 := bat.mirror([4]); ]
[ 15000 usec ext32 := nil; ]
[ 0 usec _32 := algebra.join([4],[96240405]); ]
[ 16000 usec _28 := nil; ]
[ 15000 usec _33 := bat.reverse([4]); ]
[ 16000 usec _34 := bat.new(nil,nil); ]
[ 0 usec bat.append(:bat[:oid,:str][1],"F",true); ]
[ 16000 usec bat.append(:bat[:oid,:str][2],"M",true); ]
[ 15000 usec _40 := bat.reverse(:bat[:oid,:str][2]); ]
[ 16000 usec _34 := nil; ]
[ 16000 usec _41 := algebra.semijoin(<~tmp_17330>[4],<~tmp_17320>[2]); ]
[ 0 usec _33 := nil; ]
[ 15000 usec _40 := nil; ]
[ 16000 usec _42 := bat.reverse([2]); ]
[ 15000 usec _41 := nil; ]
[ 0 usec _43 := algebra.markT(<~tmp_17325>[2],0@0); ]
[ 16000 usec _42 := nil; ]
[ 16000 usec _44 := bat.reverse([2]); ]
[ 15000 usec _43 := nil; ]
[ 0 usec _45 := algebra.join(<~tmp_17300>[2],[4]); ]
[ 16000 usec _32 := nil; ]
[ 1172000 usec _46 := aggr.count([96240405],[96240405],[4]); ]
[ 47000 usec grp30 := nil; ]
[ 15000 usec _31 := nil; ]
[ 16000 usec _47 := algebra.join(<~tmp_17300>[2],:bat[:oid,:wrd][4]); ]
[ 16000 usec _44 := nil; ]
[ 15000 usec _46 := nil; ]
[ 0 usec _48 := sql.resultSet(2,1,<~tmp_17332>[2]); ]
[ 16000 usec sql.rsColumn(15,"sys.t1","f25","varchar",1,0,<~tmp_17332>[2]); ]
[ 15000 usec _45 := nil; ]
[ 16000 usec sql.rsColumn(15,"sys.t1","count_f25","wrd",64,0,<~tmp_17254>[2]); ]
[ 16000 usec _47 := nil; ]
[ 15000 usec _56 := io.stdout(); ]
&1 15 2 2 2
% sys.t1, sys.t1 # table_name
% f25, count_f25 # name
% varchar, wrd # type
% 1, 8 # length
[ "F", 29577383 ]
[ "M", 64261486 ]
[ 0 usec sql.exportResult(137931296,15,""); ]
[ 2984000 usec user.s4_1("F","M"); ]
:90000[0]:R
:111640[0]:W
Connection closed
