database - MonetDB- Why doubling self-joins can cause query execution time to go from milliseconds to hours on the same data? -
i'm running sql queries on pretty simple table. tables model rdf graph using 'vertical partition' introduced here. thus, have following model s 'subject' , o object.
create table a_table (bigint s, bigint o) given have 449669 tuples store in table prop_eventprecedeintask. following query (with 4 self-join on table prop_eventprecedeintask) ran in 353 ms
select t1.s event1, t2.o event2, t3.o event3, t4.o event4, t5.o event5, t6.o event6 "prop_systemcallisexecutedduringtask" t1, "prop_eventprecedeintask" t2, "prop_eventprecedeintask" t3, "prop_eventprecedeintask" t4, "prop_eventprecedeintask" t5, "prop_eventprecedeintask" t6 t1.o = 15667 , t1.s = t2.s , t2.o = t3.s , t3.o = t4.s , t4.o = t5.s , t5.o = t6.s; but when double number of self-joins on same table following query (with 9 self joins), query did not finish after hour.
select t1.s event1, t2.o event2, t3.o event3, t4.o event4, t5.o event5, t6.o event6, t7.o event7, t8.o event8, t9.o event9, t10.o event10, t11.o event11 "prop_systemcallisexecutedduringtask" t1, "prop_eventprecedeintask" t2, "prop_eventprecedeintask" t3, "prop_eventprecedeintask" t4, "prop_eventprecedeintask" t5, "prop_eventprecedeintask" t6, "prop_eventprecedeintask" t7, "prop_eventprecedeintask" t8, "prop_eventprecedeintask" t9, "prop_eventprecedeintask" t10, "prop_eventprecedeintask" t11 t1.o = 15667 , t1.s = t2.s , t2.o = t3.s , t3.o = t4.s , t4.o = t5.s , t5.o = t6.s , t6.o = t7.s , t7.o = t8.s , t8.o = t9.s , t9.o = t10.s , t10.o = t11.s; what explanation of drawbak in performances ? can improve query ?
configuration
i'm working on ubuntu 12.0.4, monetdb database server v1.7 (jan2014) on 64g of ram machine.
adding query execution trace (trace select ...) of query on small dataset
+-------+------------------------------------------------------------------------------------------------------------+ | ticks | stmt | +=======+================================================================= ===========================================+ | 16 | x_3 := sql.mvc(); | | 28 | x_7=<tmp_1326>[3939] := sql.bind(x_3=0,"sys","prop_systemcallisexecutedduringtask","o",0); | | 36 | (x_59=<tmp_21>[0],r1_129=<tmp_33>[0]) := sql.bind(x_3=0,"sys","prop_eventprecedeintask","s",2); | | 20 | x_72=<tmp_33>[0] := sql.bind(x_3=0,"sys","prop_eventprecedeintask","o",1); | | 13 | x_62=<tmp_33>[0] := sql.bind(x_3=0,"sys","prop_eventprecedeintask","s",1); | | 30 | x_55:bat[:oid,:oid] =<tmp_17710>[78450] := sql.tid(x_3=0,"sys","prop_eventprecedeintask"); | | 16 | x_37=<tmp_33>[0] := sql.bind(x_3=0,"sys","prop_eventprecedeintask","o",1); | | 16 | (x_34=<tmp_21>[0],r1_40=<tmp_33>[0]) := sql.bind(x_3=0,"sys","prop_eventprecedeintask","o",2); | | 14 | x_32=<tmp_2063>[78450] := sql.bind(x_3=0,"sys","prop_eventprecedeintask","o",0); | | 15 | x_28=<tmp_33>[0] := sql.bind(x_3=0,"sys","prop_eventprecedeintask","s",1); | | 13 | (x_26=<tmp_21>[0],r1_31=<tmp_33>[0]) := sql.bind(x_3=0,"sys","prop_eventprecedeintask","s",2); | | 13 | x_25=<tmp_2055>[78450] := sql.bind(x_3=0,"sys","prop_eventprecedeintask","s",0); | | 21 | x_23:bat[:oid,:oid] =<tmp_25746>[78450] := sql.tid(x_3=0,"sys","prop_eventprecedeintask"); | | 30 | x_29=<tmp_33332>[78450] := sql.projectdelta(x_23=<tmp_25746>:bat[:oid,:oid][78450],x_25=<tmp_2055>[78450], | : : x_26=<tmp_21>[0],r1_31=<tmp_33>[0],x_28=<tmp_33>[0]); : | 21 | (x_69=<tmp_21>[0],r1_140=<tmp_33>[0]) := sql.bind(x_3=0,"sys","prop_eventprecedeintask","o",2); | | 21 | x_21=<tmp_33>[0] := sql.bind(x_3=0,"sys","prop_systemcallisexecutedduringtask","s",1); | | 14 | (x_19=<tmp_21>[0],r1_22=<tmp_33>[0]) := sql.bind(x_3=0,"sys","prop_systemcallisexecutedduringtask","s",2); | | 12 | x_17=<tmp_1320>[3939] := sql.bind(x_3=0,"sys","prop_systemcallisexecutedduringtask","s",0); | | 11 | x_13=<tmp_33>[0] := sql.bind(x_3=0,"sys","prop_systemcallisexecutedduringtask","o",1); | | 20 | (x_10=<tmp_21>[0],r1_10=<tmp_33>[0]) := sql.bind(x_3=0,"sys","prop_systemcallisexecutedduringtask","o",2); | | 24 | x_149=<tmp_34471>[0] := algebra.subselect(r1_10=<tmp_33> [0],a0=15667:lng,a0=15667:lng,true,true,false); | | 18 | x_4:bat[:oid,:oid] =<tmp_4731>[3939] := sql.tid(x_3=0,"sys","prop_systemcallisexecutedduringtask"); | | 113 | x_148=<tmp_21746>[0] := algebra.subselect(x_7=<tmp_1326>[3939],x_4=<tmp_4731>:bat[:oid,:oid][3939],a0=1566 | : : 7:lng,a0=15667:lng,true,true,false); : | 24 | x_150=<tmp_6675>[0] := algebra.subselect(x_13=<tmp_33>[0],x_4=<tmp_4731>:bat[:oid,:oid][3939],a0=15667:lng | : : ,a0=15667:lng,true,true,false); : | 27 | x_38=<tmp_32620>[78450] := sql.projectdelta(x_23=<tmp_25746>:bat[:oid,:oid][78450],x_32=<tmp_2063>[78450], | : : x_34=<tmp_21>[0],r1_40=<tmp_33>[0],x_37=<tmp_33>[0]); : | 8 | language.pass(x_23=<tmp_25746>:bat[:oid,:oid][78450]); | | 15 | x_56=<tmp_2055>[78450] := sql.bind(x_3=0,"sys","prop_eventprecedeintask","s",0); | | 31 | x_65=<tmp_33344>[78450] := sql.projectdelta(x_55=<tmp_17710>:bat[:oid,:oid][78450],x_56=<tmp_2055>[78450], | : : x_59=<tmp_21>[0],r1_129=<tmp_33>[0],x_62=<tmp_33>[0]); : | 17 | x_68=<tmp_2063>[78450] := sql.bind(x_3=0,"sys","prop_eventprecedeintask","o",0); | | 11 | x_15=<tmp_21746>[0] := sql.subdelta(x_148=<tmp_21746>[0],x_4=<tmp_4731>:bat[:oid,:oid][3939],x_10=<tmp_21> | : : [0],x_149=<tmp_34471>[0],x_150=<tmp_6675>[0]); : | 13 | language.pass(x_4=<tmp_4731>:bat[:oid,:oid][3939]); | | 43 | x_22=<tmp_25746>[0] := sql.projectdelta(x_15=<tmp_21746>[0],x_17=<tmp_1320>[3939],x_19=<tmp_21>[0],r1_22=< | : : tmp_33>[0],x_21=<tmp_33>[0]); : | 33 | (x_30=<tmp_21746>[0],r1_36=<tmp_12754>[0]) := algebra.join(x_22=<tmp_25746>[0],x_29=<tmp_33332>[78450]); | | 38 | x_39=<tmp_23566>[0] := algebra.leftfetchjoin(r1_36=<tmp_12754>[0],x_38=<tmp_32620>[78450]); | | 28 | (x_40=<tmp_12754>[0],r1_58=<tmp_4661>[0]) := algebra.join(x_39=<tmp_23566>[0],x_29=<tmp_33332>[78450]); | | 29 | x_42=<tmp_10412>[0] := algebra.leftfetchjoin(r1_58=<tmp_4661>[0],x_38=<tmp_32620>[78450]); | | 18 | (x_43=<tmp_4661>[0],r1_76=<tmp_4056>[0]) := algebra.join(x_42=<tmp_10412>[0],x_29=<tmp_33332>[78450]); | | 18 | x_45=<tmp_11601>[0] := algebra.leftfetchjoin(r1_76=<tmp_4056>[0],x_38=<tmp_32620>[78450]); | | 14 | (x_46=<tmp_4056>[0],r1_97=<tmp_14043>[0]) := algebra.join(x_45=<tmp_11601>[0],x_29=<tmp_33332>[78450]); | | 26 | x_73=<tmp_33524>[78450] := sql.projectdelta(x_55=<tmp_17710>:bat[:oid,:oid][78450],x_68=<tmp_2063>[78450], | : : x_69=<tmp_21>[0],r1_140=<tmp_33>[0],x_72=<tmp_33>[0]); : | 10 | language.pass(x_55=<tmp_17710>:bat[:oid,:oid][78450]); | | 9434 | x_49=<tmp_13521>[0] := algebra.leftfetchjoin(r1_97=<tmp_14043>[0],x_38=<tmp_32620>[78450]); | | 47 | (x_51=<tmp_14043>[0],r1_115=<tmp_36607>[0]) := algebra.join(x_49=<tmp_13521>[0],x_29=<tmp_33332>[78450]); | | 6 | language.pass(x_29=<tmp_33332>[78450]); | | 23 | x_54=<tmp_33332>[0] := algebra.leftfetchjoin(r1_115=<tmp_36607>[0],x_38=<tmp_32620>[78450]); | | 7 | language.pass(x_38=<tmp_32620>[78450]); | | 30 | (x_66=<tmp_4731>[0],r1_137=<tmp_6675>[0]) := algebra.join(x_54=<tmp_33332>[0],x_65=<tmp_33344>[78450]); | | 19 | x_74=<tmp_34471>[0] := algebra.leftfetchjoin(r1_137=<tmp_6675>[0],x_73=<tmp_33524>[78450]); | | 17 | (x_75=<tmp_6675>[0],r1_154=<tmp_4677>[0]) := algebra.join(x_74=<tmp_34471>[0],x_65=<tmp_33344>[78450]); | | 14 | x_77=<tmp_11503>[0] := algebra.leftfetchjoin(r1_154=<tmp_4677>[0],x_73=<tmp_33524>[78450]); | | 13 | (x_78=<tmp_4677>[0],r1_176=<tmp_6501>[0]) := algebra.join(x_77=<tmp_11503>[0],x_65=<tmp_33344>[78450]); | | 15 | x_80=<tmp_24055>[0] := algebra.leftfetchjoin(r1_176=<tmp_6501>[0],x_73=<tmp_33524>[78450]); | | 14 | (x_81=<tmp_6501>[0],r1_193=<tmp_7616>[0]) := algebra.join(x_80=<tmp_24055>[0],x_65=<tmp_33344>[78450]); | | 16 | x_83=<tmp_25373>[0] := algebra.leftfetchjoin(r1_193=<tmp_7616>[0],x_73=<tmp_33524>[78450]); | | 29 | (x_84=<tmp_7616>[0],r1_216=<tmp_7215>[0]) := algebra.join(x_83=<tmp_25373>[0],x_65=<tmp_33344>[78450]); | | 7 | language.pass(x_65=<tmp_33344>[78450]); | | 67 | x_98=<tmp_17710>[0] := algebra.leftfetchjoin(r1_216=<tmp_7215>[0],x_73=<tmp_33524>[78450]); | | 2 | language.pass(x_73=<tmp_33524>[78450]); | | 1129 | x_151=<tmp_26246>[0] := algebra.leftfetchjoin(x_84=<tmp_7616>[0],x_81=<tmp_6501>[0]); | | 85 | x_86:bat[:oid,:lng] =<tmp_6501>[0] := algebra.leftfetchjoinpath(x_151=<tmp_26246>[0],x_78=<tmp_4677>[0],x_ | : : 75=<tmp_6675>[0],x_66=<tmp_4731>[0],x_51=<tmp_14043>[0],x_46=<tmp_4056>[0],x_43=<tmp_4661>[0],x_40=<tmp_12 : : : 754>[0],x_30=<tmp_21746>[0],x_22=<tmp_25746>[0]); : | 67 | x_95=<tmp_32620>[0] := algebra.leftfetchjoin(x_84=<tmp_7616>[0],x_83=<tmp_25373>[0]); | | 3 | language.pass(x_84=<tmp_7616>[0]); | | 2 | language.pass(x_83=<tmp_25373>[0]); | | 14 | x_89:bat[:oid,:lng] =<tmp_7616>[0] := algebra.leftfetchjoinpath(x_151=<tmp_26246>[0],x_78=<tmp_4677>[0],x_ | : : 75=<tmp_6675>[0],x_66=<tmp_4731>[0],x_51=<tmp_14043>[0],x_46=<tmp_4056>[0],x_45=<tmp_11601>[0]); : | 2 | language.pass(x_45=<tmp_11601>[0]); | | 79 | x_87:bat[:oid,:lng] =<tmp_10432>[0] := algebra.leftfetchjoinpath(x_151=<tmp_26246>[0],x_78=<tmp_4677>[0],x | : : _75=<tmp_6675>[0],x_66=<tmp_4731>[0],x_51=<tmp_14043>[0],x_46=<tmp_4056>[0],x_43=<tmp_4661>[0],x_40=<tmp_1 : : : 2754>[0],x_39=<tmp_23566>[0]); : | 2 | language.pass(x_40=<tmp_12754>[0]); | | 7 | language.pass(x_22=<tmp_25746>[0]); | | 6902 | x_88:bat[:oid,:lng] =<tmp_33524>[0] := algebra.leftfetchjoinpath(x_151=<tmp_26246>[0],x_78=<tmp_4677>[0],x | : : _75=<tmp_6675>[0],x_66=<tmp_4731>[0],x_51=<tmp_14043>[0],x_46=<tmp_4056>[0],x_43=<tmp_4661>[0],x_42=<tmp_1 : : : 0412>[0]); : | 2 | language.pass(x_46=<tmp_4056>[0]); | | 27091 | x_90:bat[:oid,:lng] =<tmp_11601>[0] := algebra.leftfetchjoinpath(x_151=<tmp_26246>[0],x_78=<tmp_4677>[0],x | : : _75=<tmp_6675>[0],x_66=<tmp_4731>[0],x_51=<tmp_14043>[0],x_49=<tmp_13521>[0]); : | 5 | language.pass(x_43=<tmp_4661>[0]); | | 3 | language.pass(x_51=<tmp_14043>[0]); | | 2 | language.pass(x_49=<tmp_13521>[0]); | | 26 | x_91:bat[:oid,:lng] =<tmp_25746>[0] := algebra.leftfetchjoinpath(x_151=<tmp_26246>[0],x_78=<tmp_4677>[0],x | : : _75=<tmp_6675>[0],x_66=<tmp_4731>[0],x_54=<tmp_33332>[0]); : | 24 | x_92:bat[:oid,:lng] =<tmp_13521>[0] := algebra.leftfetchjoinpath(x_151=<tmp_26246>[0],x_78=<tmp_4677>[0],x | : : _75=<tmp_6675>[0],x_74=<tmp_34471>[0]); : | 2 | language.pass(x_75=<tmp_6675>[0]); | | 1 | language.pass(x_54=<tmp_33332>[0]); | | 10 | x_93:bat[:oid,:lng] =<tmp_6675>[0] := algebra.leftfetchjoinpath(x_151=<tmp_26246>[0],x_78=<tmp_4677>[0],x_ | : : 77=<tmp_11503>[0]); : | 1 | language.pass(x_78=<tmp_4677>[0]); | | 1 | language.pass(x_74=<tmp_34471>[0]); | | 7 | x_94:bat[:oid,:lng] =<tmp_34471>[0] := algebra.leftfetchjoin(x_151=<tmp_26246>[0],x_80=<tmp_24055>[0]); | | 1 | language.pass(x_151=<tmp_26246>[0]); | | 2 | language.pass(x_77=<tmp_11503>[0]); | | 2 | language.pass(x_80=<tmp_24055>[0]); | | 9 | language.pass(x_42=<tmp_10412>[0]); | | 3 | language.pass(x_66=<tmp_4731>[0]); | | 4 | language.pass(x_39=<tmp_23566>[0]); | | 65664 | barrier x_178 := language.dataflow(); | | 9 | x_99 := sql.resultset(11,1,x_86=<tmp_6501>:bat[:oid,:lng][0]); | | 6 | sql.rscolumn(x_99=4,"sys.l","event1","bigint",64,0,x_86=<tmp_6501>:bat[:oid,:lng][0]); | | 4 | sql.rscolumn(x_99=4,"sys.l","event2","bigint",64,0,x_87=<tmp_10432>:bat[:oid,:lng][0]); | | 4 | sql.rscolumn(x_99=4,"sys.l","event3","bigint",64,0,x_88=<tmp_33524>:bat[:oid,:lng][0]); | | 3 | sql.rscolumn(x_99=4,"sys.l","event4","bigint",64,0,x_89=<tmp_7616>:bat[:oid,:lng][0]); | | 3 | sql.rscolumn(x_99=4,"sys.l","event5","bigint",64,0,x_90=<tmp_11601>:bat[:oid,:lng][0]); | | 3 | sql.rscolumn(x_99=4,"sys.l","event6","bigint",64,0,x_91=<tmp_25746>:bat[:oid,:lng][0]); | | 2 | sql.rscolumn(x_99=4,"sys.l","event7","bigint",64,0,x_92=<tmp_13521>:bat[:oid,:lng][0]); | | 3 | sql.rscolumn(x_99=4,"sys.l","event8","bigint",64,0,x_93=<tmp_6675>:bat[:oid,:lng][0]); | | 5 | sql.rscolumn(x_99=4,"sys.l","event9","bigint",64,0,x_94=<tmp_34471>:bat[:oid,:lng][0]); | | 5 | sql.rscolumn(x_99=4,"sys.l","event10","bigint",64,0,x_95=<tmp_32620>[0]); | | 5 | sql.rscolumn(x_99=4,"sys.l","event11","bigint",64,0,x_98=<tmp_17710>[0]); | | 2 | x_127 := io.stdout(); | | 30 | sql.exportresult(x_127=="104d2":streams,x_99=4); | | 1 | end s3_3; | | 66047 | function user.s3_3(a0=15667:lng); | | 66145 | x_5:void := user.s3_3(15667:lng); | +-------+------------------------------------------------------------------------------------------------------------+ edit 2 modification of tuple type int bigint
Comments
Post a Comment