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