Hi
I've been trying out monetdb on a 350M row table (modern linux workstation
with 24GB RAM + SSD). This table has a sorted TIMESTAMP column and a
second unsorted VARCHAR(6) column which is non-unique (perhaps 50M distinct
values fairly randomly distributed).
This query is very slow, spends huge amount of time writing, and has very
low CPU (intermediate results?):
select count(*) from t_order where orderId = 'XXXXXX'
This takes hours to complete.
However, this query is a lot faster, even though the timestamp clause is
redundant (all my data is > '2012-01-01'):
select count(*) from t_order where orderId = 'XXXXXX' and time >
'1970-01-01';
The first run is slowish (10s of seconds - I guess because we're reading
the orderId column fully), however subsequent runs are sub-second.
Obviously I could write an app which puts the spurious timestamp clause
into the where, but it's still confusing and not ideal. Is there something
I can do?
The EXPLAINs are:
sql>explain select count(*) from t_order where orderId = 'XXXXXX' and time
> '1970-01-01';
+-------------------------------------------------------------------------------------------------------------------------+
| mal
|
+=========================================================================================================================+
| function user.s9_3{autoCommit=true}(A0:str,A1:str):void;
|
| X_14 := nil:wrd;
|
| barrier X_263 := language.dataflow();
|
| X_4 := sql.mvc();
|
| X_68:bat[:oid,:str] :=
sql.bind(X_4,"sys","t_order","orderid",0,0,16);
|
| X_132 := X_68;
|
| X_101:bat[:oid,:timestamp] :=
sql.bind(X_4,"sys","t_order","time",0,0,16);
|
| X_164 := X_101;
|
| X_35:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",0,16);
|
| X_10 := calc.timestamp(A1,7);
|
| X_180 := algebra.thetasubselect(X_164,X_35,X_10,">");
|
| X_197 := algebra.subselect(X_132,X_180,A0,A0,true,true,false);
|
| X_228 := algebra.leftfetchjoin(X_197,X_164);
|
| X_246 := aggr.count(X_228);
|
| X_70:bat[:oid,:str] :=
sql.bind(X_4,"sys","t_order","orderid",0,1,16);
|
| X_133 := X_70;
|
| X_103:bat[:oid,:timestamp] :=
sql.bind(X_4,"sys","t_order","time",0,1,16);
|
| X_165 := X_103;
|
| X_37:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",1,16);
|
| X_181 := algebra.thetasubselect(X_165,X_37,X_10,">");
|
| X_199 := algebra.subselect(X_133,X_181,A0,A0,true,true,false);
|
| X_229 := algebra.leftfetchjoin(X_199,X_165);
|
| X_247 := aggr.count(X_229);
|
| X_72:bat[:oid,:str] :=
sql.bind(X_4,"sys","t_order","orderid",0,2,16);
|
| X_134 := X_72;
|
| X_105:bat[:oid,:timestamp] :=
sql.bind(X_4,"sys","t_order","time",0,2,16);
|
| X_166 := X_105;
|
| X_39:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",2,16);
|
| X_182 := algebra.thetasubselect(X_166,X_39,X_10,">");
|
| X_201 := algebra.subselect(X_134,X_182,A0,A0,true,true,false);
|
| X_230 := algebra.leftfetchjoin(X_201,X_166);
|
| X_248 := aggr.count(X_230);
|
| X_74:bat[:oid,:str] :=
sql.bind(X_4,"sys","t_order","orderid",0,3,16);
|
| X_135 := X_74;
|
| X_107:bat[:oid,:timestamp] :=
sql.bind(X_4,"sys","t_order","time",0,3,16);
|
| X_167 := X_107;
|
| X_41:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",3,16);
|
| X_183 := algebra.thetasubselect(X_167,X_41,X_10,">");
|
| X_203 := algebra.subselect(X_135,X_183,A0,A0,true,true,false);
|
| X_231 := algebra.leftfetchjoin(X_203,X_167);
|
| X_249 := aggr.count(X_231);
|
| X_76:bat[:oid,:str] :=
sql.bind(X_4,"sys","t_order","orderid",0,4,16);
|
| X_136 := X_76;
|
| X_109:bat[:oid,:timestamp] :=
sql.bind(X_4,"sys","t_order","time",0,4,16);
|
| X_168 := X_109;
|
| X_43:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",4,16);
|
| X_184 := algebra.thetasubselect(X_168,X_43,X_10,">");
|
| X_205 := algebra.subselect(X_136,X_184,A0,A0,true,true,false);
|
| X_232 := algebra.leftfetchjoin(X_205,X_168);
|
| X_250 := aggr.count(X_232);
|
| X_78:bat[:oid,:str] :=
sql.bind(X_4,"sys","t_order","orderid",0,5,16);
|
| X_137 := X_78;
|
| X_111:bat[:oid,:timestamp] :=
sql.bind(X_4,"sys","t_order","time",0,5,16);
|
| X_169 := X_111;
|
| X_45:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",5,16);
|
| X_185 := algebra.thetasubselect(X_169,X_45,X_10,">");
|
| X_207 := algebra.subselect(X_137,X_185,A0,A0,true,true,false);
|
| X_233 := algebra.leftfetchjoin(X_207,X_169);
|
| X_251 := aggr.count(X_233);
|
| X_80:bat[:oid,:str] :=
sql.bind(X_4,"sys","t_order","orderid",0,6,16);
|
| X_138 := X_80;
|
| X_113:bat[:oid,:timestamp] :=
sql.bind(X_4,"sys","t_order","time",0,6,16);
|
| X_170 := X_113;
|
| X_47:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",6,16);
|
| X_186 := algebra.thetasubselect(X_170,X_47,X_10,">");
|
| X_209 := algebra.subselect(X_138,X_186,A0,A0,true,true,false);
|
| X_234 := algebra.leftfetchjoin(X_209,X_170);
|
| X_252 := aggr.count(X_234);
|
| X_82:bat[:oid,:str] :=
sql.bind(X_4,"sys","t_order","orderid",0,7,16);
|
| X_139 := X_82;
|
| X_115:bat[:oid,:timestamp] :=
sql.bind(X_4,"sys","t_order","time",0,7,16);
|
| X_171 := X_115;
|
| X_49:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",7,16);
|
| X_187 := algebra.thetasubselect(X_171,X_49,X_10,">");
|
| X_211 := algebra.subselect(X_139,X_187,A0,A0,true,true,false);
|
| X_235 := algebra.leftfetchjoin(X_211,X_171);
|
| X_253 := aggr.count(X_235);
|
| X_84:bat[:oid,:str] :=
sql.bind(X_4,"sys","t_order","orderid",0,8,16);
|
| X_140 := X_84;
|
| X_117:bat[:oid,:timestamp] :=
sql.bind(X_4,"sys","t_order","time",0,8,16);
|
| X_172 := X_117;
|
| X_51:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",8,16);
|
| X_188 := algebra.thetasubselect(X_172,X_51,X_10,">");
|
| X_213 := algebra.subselect(X_140,X_188,A0,A0,true,true,false);
|
| X_236 := algebra.leftfetchjoin(X_213,X_172);
|
| X_254 := aggr.count(X_236);
|
| X_86:bat[:oid,:str] :=
sql.bind(X_4,"sys","t_order","orderid",0,9,16);
|
| X_141 := X_86;
|
| X_119:bat[:oid,:timestamp] :=
sql.bind(X_4,"sys","t_order","time",0,9,16);
|
| X_173 := X_119;
|
| X_53:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",9,16);
|
| X_189 := algebra.thetasubselect(X_173,X_53,X_10,">");
|
| X_215 := algebra.subselect(X_141,X_189,A0,A0,true,true,false);
|
| X_237 := algebra.leftfetchjoin(X_215,X_173);
|
| X_255 := aggr.count(X_237);
|
| X_88:bat[:oid,:str] :=
sql.bind(X_4,"sys","t_order","orderid",0,10,16);
|
| X_142 := X_88;
|
| X_121:bat[:oid,:timestamp] :=
sql.bind(X_4,"sys","t_order","time",0,10,16);
|
| X_174 := X_121;
|
| X_55:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",10,16);
|
| X_190 := algebra.thetasubselect(X_174,X_55,X_10,">");
|
| X_217 := algebra.subselect(X_142,X_190,A0,A0,true,true,false);
|
| X_238 := algebra.leftfetchjoin(X_217,X_174);
|
| X_256 := aggr.count(X_238);
|
| X_90:bat[:oid,:str] :=
sql.bind(X_4,"sys","t_order","orderid",0,11,16);
|
| X_143 := X_90;
|
| X_123:bat[:oid,:timestamp] :=
sql.bind(X_4,"sys","t_order","time",0,11,16);
|
| X_175 := X_123;
|
| X_57:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",11,16);
|
| X_191 := algebra.thetasubselect(X_175,X_57,X_10,">");
|
| X_219 := algebra.subselect(X_143,X_191,A0,A0,true,true,false);
|
| X_239 := algebra.leftfetchjoin(X_219,X_175);
|
| X_257 := aggr.count(X_239);
|
| X_92:bat[:oid,:str] :=
sql.bind(X_4,"sys","t_order","orderid",0,12,16);
|
| X_144 := X_92;
|
| X_125:bat[:oid,:timestamp] :=
sql.bind(X_4,"sys","t_order","time",0,12,16);
|
| X_176 := X_125;
|
| X_59:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",12,16);
|
| X_192 := algebra.thetasubselect(X_176,X_59,X_10,">");
|
| X_221 := algebra.subselect(X_144,X_192,A0,A0,true,true,false);
|
| X_240 := algebra.leftfetchjoin(X_221,X_176);
|
| X_258 := aggr.count(X_240);
|
| X_94:bat[:oid,:str] :=
sql.bind(X_4,"sys","t_order","orderid",0,13,16);
|
| X_145 := X_94;
|
| X_127:bat[:oid,:timestamp] :=
sql.bind(X_4,"sys","t_order","time",0,13,16);
|
| X_177 := X_127;
|
| X_61:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",13,16);
|
| X_193 := algebra.thetasubselect(X_177,X_61,X_10,">");
|
| X_223 := algebra.subselect(X_145,X_193,A0,A0,true,true,false);
|
| X_241 := algebra.leftfetchjoin(X_223,X_177);
|
| X_259 := aggr.count(X_241);
|
| X_96:bat[:oid,:str] :=
sql.bind(X_4,"sys","t_order","orderid",0,14,16);
|
| X_146 := X_96;
|
| X_129:bat[:oid,:timestamp] :=
sql.bind(X_4,"sys","t_order","time",0,14,16);
|
| X_178 := X_129;
|
| X_63:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",14,16);
|
| X_194 := algebra.thetasubselect(X_178,X_63,X_10,">");
|
| X_225 := algebra.subselect(X_146,X_194,A0,A0,true,true,false);
|
| X_242 := algebra.leftfetchjoin(X_225,X_178);
|
| X_260 := aggr.count(X_242);
|
| X_98:bat[:oid,:str] :=
sql.bind(X_4,"sys","t_order","orderid",0,15,16);
|
| X_147 := X_98;
|
| X_131:bat[:oid,:timestamp] :=
sql.bind(X_4,"sys","t_order","time",0,15,16);
|
| X_179 := X_131;
|
| X_65:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",15,16);
|
| X_195 := algebra.thetasubselect(X_179,X_65,X_10,">");
|
| X_227 := algebra.subselect(X_147,X_195,A0,A0,true,true,false);
|
| X_243 := algebra.leftfetchjoin(X_227,X_179);
|
| X_261 := aggr.count(X_243);
|
| X_245 :=
mat.pack(X_246,X_247,X_248,X_249,X_250,X_251,X_252,X_253,X_254,X_255,X_256,X_257,X_258,X_259,X_260,X_261);
|
| X_262 := algebra.selectNotNil(X_245);
|
| X_14 := aggr.sum(X_262);
|
| language.pass(X_164);
|
| language.pass(X_165);
|
| language.pass(X_166);
|
| language.pass(X_167);
|
| language.pass(X_168);
|
| language.pass(X_169);
|
| language.pass(X_170);
|
| language.pass(X_171);
|
| language.pass(X_172);
|
| language.pass(X_173);
|
| language.pass(X_174);
|
| language.pass(X_175);
|
| language.pass(X_176);
|
| language.pass(X_177);
|
| language.pass(X_178);
|
| language.pass(X_179);
|
| exit X_263;
|
| sql.exportValue(1,"sys.L1","L1","wrd",64,0,6,X_14,"");
|
| end s9_3;
|
+-------------------------------------------------------------------------------------------------------------------------+
171 tuples (63.183ms)
sql>explain select count(*) from t_order where orderId = 'XXXXXX';
+----------------------------------------------------------------+
| mal |
+================================================================+
| function user.s10_3{autoCommit=true}(A0:str):void; |
| X_3 := sql.mvc(); |
| X_7 := sql.bind(X_3,"sys","t_order","orderid",0); |
| X_10 := X_7; |
| X_4:bat[:oid,:oid] := sql.tid(X_3,"sys","t_order"); |
| X_11 := algebra.subselect(X_10,X_4,A0,A0,true,true,false); |
| X_13 := algebra.leftfetchjoin(X_11,X_10); |
| X_14 := aggr.count(X_13); |
| sql.exportValue(1,"sys.L1","L1","wrd",64,0,6,X_14,""); |
| end s10_3; |
| # optimizer.mitosis() |
| # optimizer.dataflow() |
+----------------------------------------------------------------+
12 tuples (0.838ms)
sql>select * from storage() where "table" = 't_order';
+--------+---------+-------------------+-----------+----------+-----------+-----------+------------+------------+---------+--------+
| schema | table | column | type | location | count |
typewidth | columnsize | heapsize | indices | sorted |
+========+=========+===================+===========+==========+===========+===========+============+============+=========+========+
| sys | t_order | time | timestamp | 11/1146 | 349989136 |
8 | 2799913088 | 0 | 0 | true |
...
| sys | t_order | orderid | varchar | 10/1043 | 349989136 |
6 | 2799913088 | 8240889856 | 0 | false |
...
| sys | t_order | idx_orderid | oid | 04/456 | 349989136 |
8 | 2799913088 | 0 | 0 | true |
+--------+---------+-------------------+-----------+----------+-----------+-----------+------------+------------+---------+--------+
(some fields edited out...)
So the fast one has a much more complicated MAL plan.
(Other than this, and a date parsing prob which I've reported, I'm really
excited about this software!)
-Will Muldrew
