On Wed, Jan 28, 2009 at 12:49:13PM -0500, dariuszs wrote:
Hi, Thanks for your help. Please change the extenstion to .txt for the attached trace. Gender is f25. Thanks again. Dariusz.
thanks. here you see where the time goes:
Strace select f25,count(*) from trw100 group by f25; :2734[0]:R [ 0 usec mdb.setTimer(true); ] [ 16000 usec _1 := sql.bind("sys","trw100","f1",0); ] [ 0 usec _6 := sql.bind("sys","trw100","f1",1); ] [ 15000 usec _8 := algebra.kunion(
:bat[:oid,:str][96240405], :bat[:oid,:str][0]); ] [ 16000 usec _1 := nil; ] [ 15000 usec _6 := nil; ] [ 0 usec _9 := sql.bind("sys","trw100","f1",2); ] [ 16000 usec _11 := algebra.kdifference( [96240405], :bat[:oid,:str][0]); ] [ 16000 usec _8 := nil; ] [ 15000 usec _12 := algebra.kunion( [96240405], :bat[:oid,:str][0]); ] [ 0 usec _11 := nil; ] [ 16000 usec _9 := nil; ] [ 16000 usec _13 := sql.bind_dbat("sys","trw100",1); ] [ 15000 usec _14 := bat.reverse( :bat[:oid,:oid][0]); ] [ 0 usec _13 := nil; ] [ 16000 usec _15 := algebra.kdifference( [96240405],<~tmp_17017>[0]); ] [ 15000 usec _12 := nil; ] [ 0 usec _14 := nil; ] [ 16000 usec _17 := algebra.markT( [96240405],0@0); ] [ 16000 usec _15 := nil; ] [ 15000 usec _18 := bat.reverse( [96240405]); ] [ 0 usec _17 := nil; ] [ 16000 usec _19 := sql.bind("sys","trw100","f25",0); ] [ 16000 usec _21 := sql.bind("sys","trw100","f25",1); ] [ 15000 usec _22 := algebra.kunion( :bat[:oid,:str][96240405], :bat[:oid,:str][0]); ] [ 0 usec _19 := nil; ] [ 16000 usec _21 := nil; ] [ 15000 usec _23 := sql.bind("sys","trw100","f25",2); ] [ 16000 usec _24 := algebra.kdifference( [96240405], :bat[:oid,:str][0]); ] [ 0 usec _22 := nil; ] [ 16000 usec _25 := algebra.kunion( [96240405], :bat[:oid,:str][0]); ] [ 15000 usec _24 := nil; ] [ 0 usec _23 := nil; ] [ 16000 usec _26 := algebra.join(<~tmp_17251>[96240405], [96240405]); ] [ 16000 usec _18 := nil; ] [ 15000 usec _25 := nil; ] [ 1016000 usec (ext30,grp28) := group.new( [96240405]); ] [ 15000 usec _29 := bat.mirror( [4]); ] [ 16000 usec ext30 := nil; ] [ 0 usec _30 := algebra.join( [4], [96240405]); ] [ 16000 usec _26 := nil; ] [ 1109000 usec _31 := aggr.count( [96240405], [96240405], [4]); ] [ 31000 usec grp28 := nil; ] [ 16000 usec _29 := nil; ] [ 16000 usec _32 := sql.resultSet(2,1, [4]); ] [ 15000 usec sql.rsColumn(0,"sys.trw100","f25","varchar",1,0, [4]); ] [ 16000 usec _30 := nil; ] [ 15000 usec sql.rsColumn(0,"sys.trw100","count_f25","wrd",64,0, :bat[:oid,:wrd][4]); ] [ 0 usec _31 := nil; ] [ 16000 usec _40 := io.stdout(); ] &1 0 4 2 4 % sys.trw100, sys.trw100 # table_name % f25, count_f25 # name % varchar, wrd # type % 1, 8 # length [ "F", 29577383 ] [ "M", 64261486 ] [ "U", 2401517 ] [ "", 19 ] [ 16000 usec sql.exportResult(137943968,0,""); ] [ 2734000 usec user.s9_1(); ] ^^^^^^^^^^^^ "base-line" 2.7 secs
Strace select f25,count(*) from trw100 where f25 in ('F','M') group by f25; ^^^^^^^^^^^^^^^^^^^^^^ :31562[0]:R [ 0 usec mdb.setTimer(true); ] [ 0 usec _3 := sql.bind("sys","trw100","f25",0); ] [ 16000 usec _8 := sql.bind("sys","trw100","f25",1); ] [ 16000 usec _10 := algebra.kunion(
:bat[:oid,:str][96240405], :bat[:oid,:str][0]); ] [ 15000 usec _3 := nil; ] [ 0 usec _8 := nil; ] [ 16000 usec _11 := sql.bind("sys","trw100","f25",2); ] [ 15000 usec _13 := algebra.kdifference( [96240405], :bat[:oid,:str][0]); ] [ 16000 usec _10 := nil; ] [ 0 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]); ] [ 15000 usec _17 := nil; ] [ 16000 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; ] [ 11172000 usec _26 := algebra.semijoin(<~tmp_17247>[96240405],<~tmp_17174>[2]); ] ^^^^^^^^^^^^^ ~ 11 secs for the semijoin that implements "where f25 in ('F','M')"
[ 15000 usec _18 := nil; ] [ 16000 usec _25 := nil; ] [ 16000 usec _27 := bat.reverse(
[93838869]); ] [ 0 usec _26 := nil; ] [ 15000 usec _29 := algebra.markT(<~tmp_17260>[93838869],0@0); ] [ 16000 usec _27 := nil; ] [ 16000 usec _30 := bat.reverse( [93838869]); ] [ 0 usec _29 := nil; ] [ 437000 usec _31 := algebra.join(<~tmp_17247>[93838869], [96240405]); ] [ 63000 usec _30 := nil; ] [ 15000 usec _14 := nil; ] [ 953000 usec (ext37,grp35) := group.new( [93838869]); ] [ 16000 usec _34 := bat.mirror( [2]); ] [ 16000 usec ext37 := nil; ] [ 0 usec _35 := algebra.join( [2], [93838869]); ] [ 46000 usec _31 := nil; ] [ 344000 usec _36 := aggr.count( [93838869], [93838869], [2]); ] [ 47000 usec grp35 := nil; ] [ 16000 usec _34 := nil; ] [ 0 usec _37 := sql.resultSet(2,1, [2]); ] [ 15000 usec sql.rsColumn(1,"sys.trw100","f25","varchar",1,0, [2]); ] [ 16000 usec _35 := nil; ] [ 16000 usec sql.rsColumn(1,"sys.trw100","count_f25","wrd",64,0, :bat[:oid,:wrd][2]); ] [ 15000 usec _36 := nil; ] [ 16000 usec _45 := io.stdout(); ] &1 1 2 2 2 % sys.trw100, sys.trw100 # table_name % f25, count_f25 # name % varchar, wrd # type % 1, 8 # length [ "F", 29577383 ] [ "M", 64261486 ] [ 15000 usec sql.exportResult(137943968,1,""); ] [ 13578000 usec user.s1_1("F","M"); ] ^^^^^^^^^^^^^ 13.6 secs in total, i.e., ~= 2.7 secs "base-line" + 11 secs for the extra "where f25 in ('F','M')" predicate (i.e., the extra work that your query requests)
Strace select f25,count(*) from trw100 where f25 not in ('F','M') group by f25; ^^^^^^^^^^^^^^^^^^^^^^^^^^ :48234[0]:R [ 0 usec mdb.setTimer(true); ] [ 0 usec _3 := sql.bind("sys","trw100","f25",0); ] [ 453000 usec _9 := algebra.selectNotNil(
:bat[:oid,:str][96240405]); ] [ 16000 usec _10 := sql.bind("sys","trw100","f25",1); ] [ 16000 usec _12 := algebra.selectNotNil( :bat[:oid,:str][0]); ] [ 0 usec _13 := algebra.kunion( [96240405], [0]); ] [ 15000 usec _9 := nil; ] [ 16000 usec _12 := nil; ] [ 16000 usec _14 := sql.bind("sys","trw100","f25",2); ] [ 0 usec _16 := algebra.kdifference( [96240405], :bat[:oid,:str][0]); ] [ 15000 usec _13 := nil; ] [ 16000 usec _17 := algebra.selectNotNil( :bat[:oid,:str][0]); ] [ 15000 usec _18 := algebra.kunion( [96240405], [0]); ] [ 0 usec _16 := nil; ] [ 16000 usec _17 := nil; ] [ 16000 usec _19 := sql.bind_dbat("sys","trw100",1); ] [ 0 usec _20 := bat.reverse( :bat[:oid,:oid][0]); ] [ 15000 usec _19 := nil; ] [ 16000 usec _21 := algebra.kdifference( [96240405],<~tmp_17017>[0]); ] [ 16000 usec _18 := nil; ] [ 0 usec _20 := nil; ] [ 15000 usec _22 := bat.new(nil,nil); ] [ 16000 usec bat.append( :bat[:oid,:str][1],"F",true); ] [ 15000 usec bat.append( :bat[:oid,:str][2],"M",true); ] [ 0 usec _27 := bat.reverse( :bat[:oid,:str][2]); ] [ 16000 usec _22 := nil; ] [ 7922000 usec _28 := algebra.join( [96240405],<~tmp_17174>[2]); ] ^^^^^^^^^^^^ [ 16000 usec _27 := nil; ] [ 1859000 usec _29 := algebra.kdifference( [96240405], [93838869]); ] ^^^^^^^^^^^^ ~ 7.9 + 1.9 = 9.8 secs for the "where f25 not in ('F','M')"
[ 0 usec _21 := nil; ] [ 141000 usec _28 := nil; ] [ 0 usec _31 := algebra.markT(
[2401536],0@0); ] [ 15000 usec _29 := nil; ] [ 16000 usec _32 := bat.reverse( [2401536]); ] [ 15000 usec _31 := nil; ] [ 16000 usec _33 := algebra.kunion( :bat[:oid,:str][96240405], :bat[:oid,:str][0]); ] [ 16000 usec _3 := nil; ] [ 15000 usec _10 := nil; ] [ 0 usec _34 := algebra.kdifference( [96240405], :bat[:oid,:str][0]); ] [ 16000 usec _33 := nil; ] [ 16000 usec _35 := algebra.kunion( [96240405], :bat[:oid,:str][0]); ] [ 15000 usec _34 := nil; ] [ 16000 usec _14 := nil; ] [ 297000 usec _36 := algebra.join(<~tmp_17170>[2401536], [96240405]); ] [ 15000 usec _32 := nil; ] [ 16000 usec _35 := nil; ] [ 31000 usec (ext50,grp48) := group.new( [2401536]); ] [ 16000 usec _39 := bat.mirror( [2]); ] [ 15000 usec ext50 := nil; ] [ 16000 usec _40 := algebra.join( [2], [2401536]); ] [ 16000 usec _36 := nil; ] [ 31000 usec _41 := aggr.count( [2401536], [2401536], [2]); ] [ 0 usec grp48 := nil; ] [ 16000 usec _39 := nil; ] [ 15000 usec _42 := sql.resultSet(2,1, [2]); ] [ 16000 usec sql.rsColumn(2,"sys.trw100","f25","varchar",1,0, [2]); ] [ 0 usec _40 := nil; ] [ 15000 usec sql.rsColumn(2,"sys.trw100","count_f25","wrd",64,0, :bat[:oid,:wrd][2]); ] [ 16000 usec _41 := nil; ] [ 16000 usec _50 := io.stdout(); ] &1 2 2 2 2 % sys.trw100, sys.trw100 # table_name % f25, count_f25 # name % varchar, wrd # type % 1, 7 # length [ "U", 2401517 ] [ "", 19 ] [ 0 usec sql.exportResult(137943968,2,""); ] [ 11375000 usec user.s10_1("F","M"); ] ^^^^^^^^^^^^^ ~ 11.4 secs ~= 2.7 secs "base-line" + 9.8 secs for extra predicate as above
:48266[0]:R :56656[0]:W Strace select f25,count(*) from trw100 where f25 <> '' group by f25; ^^^^^^^^^^^^^^^ should obviously have been where f25 <> 'U' ^ :62609[0]:R [ 0 usec mdb.setTimer(true); ] [ 0 usec _2 := sql.bind("sys","trw100","f25",0); ] [ 16000 usec _7 := sql.bind("sys","trw100","f25",1); ] [ 15000 usec _9 := algebra.kunion(
:bat[:oid,:str][96240405], :bat[:oid,:str][0]); ] [ 16000 usec _2 := nil; ] [ 0 usec _7 := nil; ] [ 16000 usec _10 := sql.bind("sys","trw100","f25",2); ] [ 15000 usec _12 := algebra.kdifference( [96240405], :bat[:oid,:str][0]); ] [ 0 usec _9 := nil; ] [ 16000 usec _13 := algebra.kunion( [96240405], :bat[:oid,:str][0]); ] [ 16000 usec _12 := nil; ] [ 15000 usec _10 := nil; ] [ 0 usec _14 := sql.bind_dbat("sys","trw100",1); ] [ 16000 usec _15 := bat.reverse( :bat[:oid,:oid][0]); ] [ 15000 usec _14 := nil; ] [ 16000 usec _16 := algebra.kdifference( [96240405],<~tmp_17017>[0]); ] [ 0 usec _15 := nil; ] [ 3656000 usec _17 := algebra.antiuselect( [96240405],""); ] ^^^^^^^^^^^^ ~ 3.6 secs for the "where f25 <> 'U'"
[ 16000 usec _16 := nil; ] [ 0 usec _19 := algebra.markT(
[96240386],0@0); ] [ 16000 usec _17 := nil; ] [ 15000 usec _20 := bat.reverse( [96240386]); ] [ 16000 usec _19 := nil; ] [ 453000 usec _21 := algebra.join(<~tmp_17251>[96240386], [96240405]); ] [ 31000 usec _20 := nil; ] [ 0 usec _13 := nil; ] [ 969000 usec (ext26,grp24) := group.new( [96240386]); ] [ 0 usec _24 := bat.mirror( [3]); ] [ 15000 usec ext26 := nil; ] [ 16000 usec _25 := algebra.join( [3], [96240386]); ] [ 31000 usec _21 := nil; ] [ 359000 usec _26 := aggr.count( [96240386], [96240386], [3]); ] [ 47000 usec grp24 := nil; ] [ 16000 usec _24 := nil; ] [ 16000 usec _27 := sql.resultSet(2,1, [3]); ] [ 15000 usec sql.rsColumn(3,"sys.trw100","f25","varchar",1,0, [3]); ] [ 0 usec _25 := nil; ] [ 16000 usec sql.rsColumn(3,"sys.trw100","count_f25","wrd",64,0, :bat[:oid,:wrd][3]); ] [ 15000 usec _26 := nil; ] [ 16000 usec _35 := io.stdout(); ] &1 3 3 2 3 % sys.trw100, sys.trw100 # table_name % f25, count_f25 # name % varchar, wrd # type % 1, 8 # length [ "F", 29577383 ] [ "M", 64261486 ] [ "U", 2401517 ] [ 16000 usec sql.exportResult(137943968,3,""); ] [ 5953000 usec user.s11_1(""); ] ^^^^^^^^^^^^ ~ 6 secs ~= 2.7 secs "base=line" + 3.6 secs for "where f25 <> 'U'"
essence: the domination cost in your query is the extra predicate; the simple the extra predicate the cheaper the extra cost for it. Stefan -- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |