Oh... sorry about the attachment password thing. For reasons that make no sense to me, but are above my pay grade, it's required for all of our outgoing mail. For convenience, here's the same information inline. Tim sql>\d raw.ss CREATE TABLE "raw"."ss" ( "log" VARCHAR(32), "record" INTEGER, "size" SMALLINT, "sensor" VARCHAR(64), "ts" TIMESTAMP WITH TIME ZONE, "vector" VARCHAR(16), "action" VARCHAR(16), "med" VARCHAR(16), "offset" VARCHAR(16), "octets" INTEGER, "l3" SMALLINT, "l4" SMALLINT, "src" INET, "snet" INET, "siso2" CHAR(2), "sport" INTEGER, "s1" BOOLEAN, "s2" BOOLEAN, "s3" BOOLEAN, "dst" INET, "dnet" INET, "diso2" CHAR(2), "dport" INTEGER, "d1" BOOLEAN, "d2" BOOLEAN, "d3" BOOLEAN, "a1" BOOLEAN, "a2" BOOLEAN, "a3" BOOLEAN, "a4" BOOLEAN, "a5" BOOLEAN, "a6" BOOLEAN, "a7" BOOLEAN, "a8" BOOLEAN, "truncated" BOOLEAN, CONSTRAINT "uniquesslogentry" UNIQUE ("log", "record") ); sql>select count(*) from raw.ss; +------------+ | L1 | +============+ | 4525458159 | +------------+ 1 tuple (29.963ms) sql>select count(*) from raw.ss where "action"='T'; +-----------+ | L1 | +===========+ | 446532712 | +-----------+ 1 tuple (55.3s) sql>select count(*) from raw.ss where "action"='R'; +------------+ | L1 | +============+ | 3988792686 | +------------+ 1 tuple (7m 13s) sql>select count(*) from raw.ss where med='E'; +------------+ | L1 | +============+ | 2823214994 | +------------+ 1 tuple (6m 2s) sql>select count(distinct siso2) from raw.ss; +------+ | L1 | +======+ | 243 | +------+ 1 tuple (7m 54s) sql>select count(*) from raw.ss where ts>='2014-10-01'; +------------+ | L1 | +============+ | 3591320144 | +------------+ 1 tuple (1m 16s) sql>select count(*) from raw.ss where ts<'2015-01-01'; +------------+ | L1 | +============+ | 3917617844 | +------------+ 1 tuple (12.4s) sql>select count(distinct src) from raw.ss; (no answer in 8 hours) Answer should be 101,706,247 sql>EXPLAIN select siso2 as country,count(distinct src) as sources from raw.ss where ts>='2014-10-01' and ts<'2015-01-01' and ("action"='T' or "action"='R') and med='E' group by country order by sources desc; +----------------------------------------------------------------------------------------------------------------------------------+ | mal | +==================================================================================================================================+ | function user.s5_1{autoCommit=true}(A0:str,A1:str,A2:str,A3:str,A4:str):void; | | X_7 := sql.mvc(); | | X_11:bat[:oid,:str] := sql.bind(X_7,"raw","ss","action",0); | | X_19:bat[:oid,:ts] := sql.bind(X_7,"raw","ss","ts",0); | | X_27:bat[:oid,:str] := sql.bind(X_7,"raw","ss","med",0); | | X_8:bat[:oid,:oid] := sql.tid(X_7,"raw","ss"); | | X_109 := algebra.subselect(X_27,X_8,A4,A4,true,true,false); | | (X_29,r1_36) := sql.bind(X_7,"raw","ss","med",2); | | X_110 := algebra.subselect(r1_36,A4,A4,true,true,false); | | X_31:bat[:oid,:str] := sql.bind(X_7,"raw","ss","med",1); | | X_111 := algebra.subselect(X_31,X_8,A4,A4,true,true,false); | | X_32 := sql.subdelta(X_109,X_8,X_29,X_110,X_111); | | X_24 := calc.ts(A0,7); | | X_26 := calc.ts(A1,7); | | X_112 := algebra.subselect(X_19,X_32,X_24,X_26,true,false,false); | | (X_21,r1_24) := sql.bind(X_7,"raw","ss","ts",2); | | X_113 := algebra.subselect(r1_24,X_24,X_26,true,false,false); | | X_23:bat[:oid,:ts] := sql.bind(X_7,"raw","ss","ts",1); | | X_114 := algebra.subselect(X_23,X_32,X_24,X_26,true,false,false); | | X_35 := sql.subdelta(X_112,X_32,X_21,X_113,X_114); | | X_115 := algebra.subselect(X_11,X_35,A2,A2,true,true,false); | | (X_14,r1_14) := sql.bind(X_7,"raw","ss","action",2); | | X_116 := algebra.subselect(r1_14,A2,A2,true,true,false); | | X_17:bat[:oid,:str] := sql.bind(X_7,"raw","ss","action",1); | | X_117 := algebra.subselect(X_17,X_35,A2,A2,true,true,false); | | X_36 := sql.subdelta(X_115,X_35,X_14,X_116,X_117); | | X_118 := algebra.subselect(X_11,X_35,A3,A3,true,true,false); | | X_119 := algebra.subselect(r1_14,A3,A3,true,true,false); | | X_120 := algebra.subselect(X_17,X_35,A3,A3,true,true,false); | | X_37 := sql.subdelta(X_118,X_35,X_14,X_119,X_120); | | X_38 := bat.mergecand(X_36,X_37); | | X_39:bat[:oid,:str] := sql.bind(X_7,"raw","ss","siso2",0); | | (X_44,r1_55) := sql.bind(X_7,"raw","ss","siso2",2); | | X_47:bat[:oid,:str] := sql.bind(X_7,"raw","ss","siso2",1); | | X_49 := sql.projectdelta(X_38,X_39,X_44,r1_55,X_47); | | X_50:bat[:oid,:inet] := sql.bind(X_7,"raw","ss","src",0); | | (X_52,r1_65) := sql.bind(X_7,"raw","ss","src",2); | | X_54:bat[:oid,:inet] := sql.bind(X_7,"raw","ss","src",1); | | X_55 := sql.projectdelta(X_38,X_50,X_52,r1_65,X_54); | | (X_56,r1_71,r2_71) := group.subgroup(X_55); | | (X_59,r1_74,r2_74) := group.subgroupdone(X_49,X_56); | | X_62 := algebra.leftfetchjoin(r1_74,X_55); | | X_63 := algebra.leftfetchjoin(r1_74,X_49); | | (X_64,r1_79,r2_79) := group.subgroupdone(X_63); | | X_67:bat[:oid,:wrd] := aggr.subcount(X_62,X_64,r1_79,true); | | (X_69,r1_84,r2_84) := algebra.subsort(X_67,true,false); | | X_73:bat[:oid,:str] := algebra.leftfetchjoinPath(r1_84,r1_79,X_63); | | X_74 := algebra.leftfetchjoin(r1_84,X_67); | | X_75 := sql.resultSet(2,1,X_73); | | sql.rsColumn(X_75,"raw.ss","country","char",2,0,X_73); | | sql.rsColumn(X_75,"raw.L1","sources","wrd",64,0,X_74); | | X_88 := io.stdout(); | | sql.exportResult(X_88,X_75); | | end s5_1; | | # querylog.define("explain select siso2 as country,count(distinct src) as sources from raw.ss where ts>=\\'2014-10-01\\' and ts< | : \\'2015-01-01\\' and (\"action\"=\\'T\\' or \"action\"=\\'R\\') and med=\\'E\\' group by country order by sources des : : c;","default_pipe") : | # optimizer.mitosis() | | # optimizer.dataflow() | +----------------------------------------------------------------------------------------------------------------------------------+ 57 tuples (33.922ms) sql> sql>EXPLAIN select c.siso2 as country,count(c.src) as sources from (select siso2,src from raw.ss where ts>='2014-10-01' and ts<'2015-01-01' and ("action"='T' or "action"='R') and med='E' group by siso2,src) c group by country order by sources desc; +----------------------------------------------------------------------------------------------------------------------------------+ | mal | +==================================================================================================================================+ | function user.s6_1{autoCommit=true}(A0:str,A1:str,A2:str,A3:str,A4:str):void; | | X_7 := sql.mvc(); | | X_11:bat[:oid,:str] := sql.bind(X_7,"raw","ss","action",0); | | X_19:bat[:oid,:ts] := sql.bind(X_7,"raw","ss","ts",0); | | X_27:bat[:oid,:str] := sql.bind(X_7,"raw","ss","med",0); | | X_8:bat[:oid,:oid] := sql.tid(X_7,"raw","ss"); | | X_109 := algebra.subselect(X_27,X_8,A4,A4,true,true,false); | | (X_29,r1_36) := sql.bind(X_7,"raw","ss","med",2); | | X_110 := algebra.subselect(r1_36,A4,A4,true,true,false); | | X_31:bat[:oid,:str] := sql.bind(X_7,"raw","ss","med",1); | | X_111 := algebra.subselect(X_31,X_8,A4,A4,true,true,false); | | X_32 := sql.subdelta(X_109,X_8,X_29,X_110,X_111); | | X_24 := calc.ts(A0,7); | | X_26 := calc.ts(A1,7); | | X_112 := algebra.subselect(X_19,X_32,X_24,X_26,true,false,false); | | (X_21,r1_24) := sql.bind(X_7,"raw","ss","ts",2); | | X_113 := algebra.subselect(r1_24,X_24,X_26,true,false,false); | | X_23:bat[:oid,:ts] := sql.bind(X_7,"raw","ss","ts",1); | | X_114 := algebra.subselect(X_23,X_32,X_24,X_26,true,false,false); | | X_35 := sql.subdelta(X_112,X_32,X_21,X_113,X_114); | | X_115 := algebra.subselect(X_11,X_35,A2,A2,true,true,false); | | (X_14,r1_14) := sql.bind(X_7,"raw","ss","action",2); | | X_116 := algebra.subselect(r1_14,A2,A2,true,true,false); | | X_17:bat[:oid,:str] := sql.bind(X_7,"raw","ss","action",1); | | X_117 := algebra.subselect(X_17,X_35,A2,A2,true,true,false); | | X_36 := sql.subdelta(X_115,X_35,X_14,X_116,X_117); | | X_118 := algebra.subselect(X_11,X_35,A3,A3,true,true,false); | | X_119 := algebra.subselect(r1_14,A3,A3,true,true,false); | | X_120 := algebra.subselect(X_17,X_35,A3,A3,true,true,false); | | X_37 := sql.subdelta(X_118,X_35,X_14,X_119,X_120); | | X_38 := bat.mergecand(X_36,X_37); | | X_39:bat[:oid,:str] := sql.bind(X_7,"raw","ss","siso2",0); | | (X_44,r1_55) := sql.bind(X_7,"raw","ss","siso2",2); | | X_47:bat[:oid,:str] := sql.bind(X_7,"raw","ss","siso2",1); | | X_49 := sql.projectdelta(X_38,X_39,X_44,r1_55,X_47); | | X_50:bat[:oid,:inet] := sql.bind(X_7,"raw","ss","src",0); | | (X_52,r1_65) := sql.bind(X_7,"raw","ss","src",2); | | X_54:bat[:oid,:inet] := sql.bind(X_7,"raw","ss","src",1); | | X_55 := sql.projectdelta(X_38,X_50,X_52,r1_65,X_54); | | (X_56,r1_71,r2_71) := group.subgroup(X_55); | | (X_59,r1_74,r2_74) := group.subgroupdone(X_49,X_56); | | X_62 := algebra.leftfetchjoin(r1_74,X_55); | | X_63 := algebra.leftfetchjoin(r1_74,X_49); | | (X_64,r1_79,r2_79) := group.subgroupdone(X_63); | | X_67:bat[:oid,:wrd] := aggr.subcount(X_62,X_64,r1_79,true); | | (X_69,r1_84,r2_84) := algebra.subsort(X_67,true,false); | | X_73:bat[:oid,:str] := algebra.leftfetchjoinPath(r1_84,r1_79,X_63); | | X_74 := algebra.leftfetchjoin(r1_84,X_67); | | X_75 := sql.resultSet(2,1,X_73); | | sql.rsColumn(X_75,"raw.c","country","char",2,0,X_73); | | sql.rsColumn(X_75,"raw.L1","sources","wrd",64,0,X_74); | | X_88 := io.stdout(); | | sql.exportResult(X_88,X_75); | | end s6_1; | | # querylog.define("explain select c.siso2 as country,count(c.src) as sources from (select siso2,src from raw.ss where ts>=\\'201 | : 4-10-01\\' and ts<\\'2015-01-01\\' and (\"action\"=\\'T\\' or \"action\"=\\'R\\') and med=\\'E\\' group by siso2,src) : : c group by country order by sources desc;","default_pipe") : | # optimizer.mitosis() | | # optimizer.dataflow() | +----------------------------------------------------------------------------------------------------------------------------------+ 57 tuples (1.031ms)