Re: [MonetDB-users] sql filter slow

Hi, I just did that - no improvement, still grouping by first on fields with little distinct values seems to be the way to go, see attached. Thanks. Dariusz. Stefan Manegold wrote:

On Wed, Jan 28, 2009 at 02:21:37PM -0500, Chris Curvey wrote:

...

On Wed, Jan 28, 2009 at 11:47 AM, dariuszs wrote:

...

Hi, If I rewrite this query like this

select * from (select gender,count(*) from table1 group by gender) as t1 where t1.gender in ('F','M');

It takes about 2 seconds which is wonderful however how do I apply this to very complicated queries? Thanks. Dariusz.

I might suggest that it will be cleaner to do it this way.

select gender, count(*) from table1 group by gender having gender in ('F','M');

(In the past, I've found that the construct "select ... from (select ....)" ends up creating SQL that's really hard to debug. YMMV.)

indeed a good idea --- [cs]hould have been mine ... ;-)

dariuszs, feel free to test/trace this one on your data, too.

Stefan

...

------------------------------------------------------------------------------ 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

:250000[0]:W Strace select f25,count(*) from trw100 group by f25 having f25 in ('F','M'); :261735[0]:R [ 0 usec mdb.setTimer(true); ] [ 16000 usec _3 := sql.bind("sys","trw100","f1",0); ] [ 16000 usec _8 := sql.bind("sys","trw100","f1",1); ] [ 15000 usec _10 := algebra.kunion(:bat[:oid,:str][96240405],:bat[:oid,:str][0]); ] [ 16000 usec _3 := nil; ] [ 16000 usec _8 := nil; ] [ 15000 usec _11 := sql.bind("sys","trw100","f1",2); ] [ 16000 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; ] [ 16000 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]); ] [ 15000 usec _14 := nil; ] [ 16000 usec _16 := nil; ] [ 16000 usec _19 := algebra.markT([96240405],0@0); ] [ 15000 usec _17 := nil; ] [ 16000 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); ] [ 15000 usec _24 := algebra.kunion(:bat[:oid,:str][96240405],:bat[:oid,:str][0]); ] [ 16000 usec _21 := nil; ] [ 16000 usec _23 := nil; ] [ 15000 usec _25 := sql.bind("sys","trw100","f25",2); ] [ 16000 usec _26 := algebra.kdifference([96240405],:bat[:oid,:str][0]); ] [ 31000 usec _24 := nil; ] [ 16000 usec _27 := algebra.kunion([96240405],:bat[:oid,:str][0]); ] [ 15000 usec _26 := nil; ] [ 16000 usec _25 := nil; ] [ 16000 usec _28 := algebra.join(<~tmp_20163>[96240405],[96240405]); ] [ 15000 usec _20 := nil; ] [ 16000 usec _27 := nil; ] [ 937000 usec (ext32,grp30) := group.new([96240405]); ] [ 16000 usec _31 := bat.mirror([4]); ] [ 16000 usec ext32 := nil; ] [ 15000 usec _32 := algebra.join([4],[96240405]); ] [ 16000 usec _33 := bat.reverse([4]); ] [ 16000 usec _32 := nil; ] [ 15000 usec _34 := bat.new(nil,nil); ] [ 16000 usec bat.append(:bat[:oid,:str][1],"F",true); ] [ 15000 usec bat.append(:bat[:oid,:str][2],"M",true); ] [ 16000 usec _40 := bat.reverse(:bat[:oid,:str][2]); ] [ 16000 usec _34 := nil; ] [ 15000 usec _41 := algebra.semijoin(<~tmp_17330>[4],<~tmp_20176>[2]); ] [ 16000 usec _33 := nil; ] [ 16000 usec _40 := nil; ] [ 15000 usec _42 := bat.reverse([2]); ] [ 16000 usec _41 := nil; ] [ 15000 usec _43 := algebra.semijoin([4],<~tmp_20104>[2]); ] [ 16000 usec _31 := nil; ] [ 16000 usec _44 := algebra.join(<~tmp_20160>[2],[96240405]); ] [ 15000 usec _28 := nil; ] [ 16000 usec _45 := bat.reverse([96240405]); ] [ 16000 usec grp30 := nil; ] [ 6531000 usec _46 := algebra.semijoin(<~tmp_20173>[96240405],<~tmp_20104>[2]); ] [ 31000 usec _45 := nil; ] [ 16000 usec _42 := nil; ] [ 31000 usec _47 := bat.reverse([93838869]); ] [ 16000 usec _46 := nil; ] [ 3062000 usec _48 := aggr.count(<~tmp_20200>[93838869],<~tmp_20200>[93838869],<~tmp_20160>[2]); ] [ 63000 usec _47 := nil; ] [ 15000 usec _43 := nil; ] [ 16000 usec _49 := sql.resultSet(2,1,[2]); ] [ 31000 usec sql.rsColumn(4,"sys.trw100","f25","varchar",1,0,[2]); ] [ 16000 usec _44 := nil; ] [ 15000 usec sql.rsColumn(4,"sys.trw100","count_f25","wrd",64,0,:bat[:oid,:wrd][2]); ] [ 16000 usec _48 := nil; ] [ 16000 usec _57 := io.stdout(); ] &1 4 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(1781102128,4,""); ] [ 11735000 usec user.s27_1("F","M"); ] :261782[0]:R :274375[0]:W Strace select * from (select f25,count(*) from trw100 group by f25) as t1 where t1.f25 in ('F','M'); :277563[0]:R [ 0 usec mdb.setTimer(true); ] [ 16000 usec _3 := sql.bind("sys","trw100","f1",0); ] [ 16000 usec _8 := sql.bind("sys","trw100","f1",1); ] [ 15000 usec _10 := algebra.kunion(:bat[:oid,:str][96240405],:bat[:oid,:str][0]); ] [ 16000 usec _3 := nil; ] [ 16000 usec _8 := nil; ] [ 15000 usec _11 := sql.bind("sys","trw100","f1",2); ] [ 16000 usec _13 := algebra.kdifference([96240405],:bat[:oid,:str][0]); ] [ 31000 usec _10 := nil; ] [ 16000 usec _14 := algebra.kunion([96240405],:bat[:oid,:str][0]); ] [ 15000 usec _13 := nil; ] [ 16000 usec _11 := nil; ] [ 16000 usec _15 := sql.bind_dbat("sys","trw100",1); ] [ 15000 usec _16 := bat.reverse(:bat[:oid,:oid][0]); ] [ 16000 usec _15 := nil; ] [ 15000 usec _17 := algebra.kdifference([96240405],<~tmp_17017>[0]); ] [ 16000 usec _14 := nil; ] [ 16000 usec _16 := nil; ] [ 15000 usec _19 := algebra.markT([96240405],0@0); ] [ 16000 usec _17 := nil; ] [ 16000 usec _20 := bat.reverse([96240405]); ] [ 15000 usec _19 := nil; ] [ 16000 usec _21 := sql.bind("sys","trw100","f25",0); ] [ 15000 usec _23 := sql.bind("sys","trw100","f25",1); ] [ 16000 usec _24 := algebra.kunion(:bat[:oid,:str][96240405],:bat[:oid,:str][0]); ] [ 16000 usec _21 := nil; ] [ 15000 usec _23 := nil; ] [ 16000 usec _25 := sql.bind("sys","trw100","f25",2); ] [ 16000 usec _26 := algebra.kdifference([96240405],:bat[:oid,:str][0]); ] [ 15000 usec _24 := nil; ] [ 16000 usec _27 := algebra.kunion([96240405],:bat[:oid,:str][0]); ] [ 15000 usec _26 := nil; ] [ 16000 usec _25 := nil; ] [ 16000 usec _28 := algebra.join(<~tmp_20174>[96240405],[96240405]); ] [ 15000 usec _20 := nil; ] [ 16000 usec _27 := nil; ] [ 937000 usec (ext32,grp30) := group.new([96240405]); ] [ 16000 usec _31 := bat.mirror([4]); ] [ 16000 usec ext32 := nil; ] [ 15000 usec _32 := algebra.join([4],[96240405]); ] [ 16000 usec _28 := nil; ] [ 16000 usec _33 := bat.reverse([4]); ] [ 15000 usec _34 := bat.new(nil,nil); ] [ 16000 usec bat.append(:bat[:oid,:str][1],"F",true); ] [ 31000 usec bat.append(:bat[:oid,:str][2],"M",true); ] [ 16000 usec _40 := bat.reverse(:bat[:oid,:str][2]); ] [ 15000 usec _34 := nil; ] [ 16000 usec _41 := algebra.semijoin(<~tmp_17330>[4],<~tmp_20176>[2]); ] [ 16000 usec _33 := nil; ] [ 15000 usec _40 := nil; ] [ 16000 usec _42 := bat.reverse([2]); ] [ 15000 usec _41 := nil; ] [ 16000 usec _43 := algebra.markT(<~tmp_20104>[2],0@0); ] [ 16000 usec _42 := nil; ] [ 15000 usec _44 := bat.reverse([2]); ] [ 16000 usec _43 := nil; ] [ 16000 usec _45 := algebra.join(<~tmp_20163>[2],[4]); ] [ 15000 usec _32 := nil; ] [ 1110000 usec _46 := aggr.count([96240405],[96240405],[4]); ] [ 31000 usec grp30 := nil; ] [ 15000 usec _31 := nil; ] [ 16000 usec _47 := algebra.join(<~tmp_20163>[2],:bat[:oid,:wrd][4]); ] [ 16000 usec _44 := nil; ] [ 15000 usec _46 := nil; ] [ 32000 usec _48 := sql.resultSet(2,1,<~tmp_20177>[2]); ] [ 15000 usec sql.rsColumn(5,"sys.t1","f25","varchar",1,0,<~tmp_20177>[2]); ] [ 16000 usec _45 := nil; ] [ 15000 usec sql.rsColumn(5,"sys.t1","count_f25","wrd",64,0,<~tmp_17254>[2]); ] [ 16000 usec _47 := nil; ] [ 16000 usec _56 := io.stdout(); ] &1 5 2 2 2 % sys.t1, sys.t1 # table_name % f25, count_f25 # name % varchar, wrd # type % 1, 8 # length [ "F", 29577383 ] [ "M", 64261486 ] [ 15000 usec sql.exportResult(1781102128,5,""); ] [ 3188000 usec user.s4_1("F","M"); ] :277594[0]:R :280172[0]:W Connection closed