On Wed, Jan 28, 2009 at 01:32:09PM -0500, dariuszs wrote:
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.
The point is that in your query, you have a predicate on the group-by attribute; hence, there are two way to evaluate this, as reflected by the two variant of your query: (1) first perform the restriction according to the predicate; the perform the grouping (plus aggregation, here: count). (2) first perform the grouping (plus aggregation, here: count) on the whole table, and the restriction according to the predicate. It appears that the evaluating the predicate on the whole table seems to be (significatly) more expensive than the grouping + aggregation --- the reason is most probably that your predicate selects 93838869 out of 96240405 tuples, i.e., MonetDB needs to materialize a rather larger intermediate result of ~97.5% of the original column, while the grouping results in only 3 tuples --- hence, doing the grouping first is (much) faster. In priciple, our optimizer should be able to detect that case and rewrite the query accordingly --- we need to analyse why it doesn't --- might not be that trivial in teh general case... Hence, for now, you might want rewrite queries that fall into this specific pattern as you did. Stefan
: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
------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- | 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 |