users-list

Download

users-list@monetdb.org

August 2011

13 participants
22 discussions

[MonetDB-users] Long running query with count(distinct ...)
by Eugene Prokopiev 02 Aug '11

02 Aug '11

Hi, I have table with 81 887 010 rows. Select count from this table works fine, but I can't wait for the results of select count(distinct ...) from this table. Why can it be? sql>explain select count(src) from transfer_ip; +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | mal | +============================================… [View More]======================================================================================================================================+ | function user.s1_1{autoCommit=true}():void; | | barrier _55 := language.dataflow(); | | _2 := sql.mvc(); | | _39:bat[:oid,:inet] := sql.bind(_2,"sys","transfer_ip","src",0,0,2); | | _43 := algebra.selectNotNil(_39); | | _35:bat[:oid,:inet] := sql.bind(_2,"sys","transfer_ip","src",2,0,2); | | _45 := algebra.kdifference(_43,_35); | | _41 := algebra.selectNotNil(_35); | | _47 := algebra.kunion(_45,_41); | | _6:bat[:oid,:oid] := sql.bind_dbat(_2,"sys","transfer_ip",1); | | _9 := bat.reverse(_6); | | _49 := algebra.kdifference(_47,_9); | | _51 := aggr.count(_49); | | _40:bat[:oid,:inet] := sql.bind(_2,"sys","transfer_ip","src",0,1,2); | | _44 := algebra.selectNotNil(_40); | | _38:bat[:oid,:inet] := sql.bind(_2,"sys","transfer_ip","src",2,1,2); | | _46 := algebra.kdifference(_44,_38); | | _42 := algebra.selectNotNil(_38); | | _48 := algebra.kunion(_46,_42); | | _52 := aggr.count(_48); | | _10:bat[:oid,:inet] := sql.bind(_2,"sys","transfer_ip","src",1); | | _11 := algebra.selectNotNil(_10); | | _53 := aggr.count(_11); | | _50 := mat.pack(_51,_52,_53); | | _54 := algebra.selectNotNil(_50); | | _14 := aggr.sum(_54); | | exit _55; | | sql.exportValue(1,"sys.transfer_ip","L4","wrd",64,0,6,_14,""); | | end s1_1; | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 29 tuples (12.925ms) sql>explain select count(distinct src) from transfer_ip; +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | mal | +==================================================================================================================================================================================+ | function user.s2_1{autoCommit=true}():void; | | barrier _56 := language.dataflow(); | | _2 := sql.mvc(); | | _42:bat[:oid,:inet] := sql.bind(_2,"sys","transfer_ip","src",0,0,2); | | _46 := algebra.selectNotNil(_42); | | _38:bat[:oid,:inet] := sql.bind(_2,"sys","transfer_ip","src",2,0,2); | | _48 := algebra.kdifference(_46,_38); | | _44 := algebra.selectNotNil(_38); | | _50 := algebra.kunion(_48,_44); | | _6:bat[:oid,:oid] := sql.bind_dbat(_2,"sys","transfer_ip",1); | | _9 := bat.reverse(_6); | | _52 := algebra.kdifference(_50,_9); | | _53 := bat.reverse(_52); | | _43:bat[:oid,:inet] := sql.bind(_2,"sys","transfer_ip","src",0,1,2); | | _47 := algebra.selectNotNil(_43); | | _41:bat[:oid,:inet] := sql.bind(_2,"sys","transfer_ip","src",2,1,2); | | _49 := algebra.kdifference(_47,_41); | | _45 := algebra.selectNotNil(_41); | | _51 := algebra.kunion(_49,_45); | | _54 := bat.reverse(_51); | | _10:bat[:oid,:inet] := sql.bind(_2,"sys","transfer_ip","src",1); | | _11 := algebra.selectNotNil(_10); | | _55 := bat.reverse(_11); | | _14 := mat.pack(_53,_54,_55); | | _15 := algebra.kunique(_14); | | _16 := bat.reverse(_15); | | _17 := aggr.count(_16); | | exit _56; | | sql.exportValue(1,"sys.transfer_ip","L5","wrd",64,0,6,_17,""); | | end s2_1; | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 30 tuples (1.238ms) -- Thanks, Eugene Prokopiev [View Less]

3 4

[MonetDB-users] Vacuum command missing
by Eugene Prokopiev 01 Aug '11

01 Aug '11

Hi, I can't find VACUUM command in http://www.monetdb.org/Documentation, I see only vacuum cleaning algorithm mention in http://www.monetdb.org/Documentation/Manuals/SQLreference/Transactions. So, how can phisicaly delete rows after marking them as deleted? -- Thanks, Eugene Prokopiev

2 3