What is "Elapsed" measuring in ColumnStore query plan
I have a query that reports an execution time of 6.826 sec. I was expecting the sum of the elapsed to be something less that the total execution time. The query plan shows BPS step of 6.095 and TAS 6.059.
Here's the query and results...
-- version=10.2.10-MariaDB-log -- version_comment=Columnstore 1.1.2-1 RESET QUERY CACHE; SELECT calFlushCache(); SELECT calSetTrace(1); SELECT DAY(timestamp_),SUM(numeric_val), AVG(numeric_val) FROM cs1 WHERE timestamp_ >= '2017-09-01 00:00:00' AND timestamp_ < '2017-10-01 00:00:00' AND id IN (228091,228231,228233,227867,228216,228241,226331,227950,228254,227953,228156,228160,228164,211566,227827,227875,227886,227892,211111,211576,224213,227908,224293,211117,227919,224232,227850,227925,226290,227853,227261,210432,210443,210446,210477,211314,227391,211634,227396,226377,227485,211630,227385,211532,226814,211542,226825,210063,210081,210092,210287,210289,210252,210303,210326,210263,210339,226979,226984,210362,227019,227045,227055,227062,210376,227132,227133,227142,227169,210403,227240,209894,209895,209907,209896,209931,209946,209948,207054,209975,210009,210021,210023,210020,207181,207243,206201,206332,209821,209824,209839,209772,209773,209777,209851,209855,206559,209862,209870,206575,209878,185470,185475,185027,185044,185045,185052,185059,185041,185072,184223,185085,184339,185128,184369,184341,185108,184355,184372,185185,184860,184139,184145,184880,184146,184162,184934,184165,184941,184943,184894,184174,184960,184169,184930,184975,184210,184915,184987,184998,185013,381854,376280,413343,342336,412480,327684,412489,414657,414658,416331,318587,414660,414603,319965,319973,319975,412457,412464,342329,342330,413163,381783,412652,413696,412096,412661,413707,413710,413048,342333,412477,381805,381821,381825,381826,320421,342318,413007,413009,412411,412412,376236,412418,376240,376242,413338,414025,412443,412627,413018,412445,376266,412447,412449,413024,323373,325478,325163,326013,325752,323387,319958,321642,321643,320948,414942,328239,327041,412296,335091,412303,411961,335096,411965,334263,317730,321466,320413,321295,320940,319779,322531,325472,325473,325741,325742,334396,327907,334565,317724,321293,411940,411942,333900,327567,334594,412284,334418,333918,334432,333935,334444,229724,229727,230088,230470,230784,230785,230119,229657,230507,229853,229735,230624,230625,230633,230637,229647,230078,230457,230658,230794,230797,229716,230108,230501,230680,230681,229479,229530,229558,230035,230401,230595,229677,230050,230424,230425,229693,230602,230604,230610,229620,230618,229301,229295,228569,229313,229315,229359,229365,229386,229338,229393,229395,229428,229442,229473,228899,228883,229227,228488,229069,228913,228920,228516,228926,229285,229097,229106,229082,228508,229166,229013,228853,229033,228753,228430,228864,229219,229220,229229,228455,228358,228676,228631,228637,228807,229128,228827,228982,228708,228987,228997,228845,228171,228289,227963,228315,228769,228777,228181,228786,228184,228185,228616,228621,228190,228328,228813,228656,228350,228959,224241,228101,228104,426454,425833,425836,413952,414530,424627,414534,424633,413866,413971,413972,413973,413979,327692,381675,376161,412051,334521,381690,381695,414229,412225,412745,413313,412231,412233,414501,414863,414512,412748,413942,412755,413325,414518,413949,415149,415453,323391,426539,412910,413749,334724,412821,425661,416180,425785,415892,415894,412915,412916,412918,414494,413677,413838,413478,335201,414195,412203,325623,414201,413492,414207,414208,414209,414215,414483,414481,342351,414075,325963,326022,324753,319794,424584,415146,318590,317417,318722,318604,318605,317939,317742,413661,413907,413914,413465,414835,413921,413477,412593,320294,320638,376452,376456,320152,319995,320648,415450,415787,426952,415140,206051,207347,206095,207411,206119,206129,206135,206138,206224,206230,206234,206239,206283,206262,206361,206268,206287,205903,204852,204863,204884,204886,204891,204893,204895,204905,204922,204923,205919,205932,205067,205987,205991,205992,206007,206032,205729,205735,205747,204759,205765,205764,204766,205772,205774,205782,204793,205797,204803,205867,204826,204264,204298,204342,204469,205653,204333,204334,204539,204682,204728,205688,205696,205715,186694,203811,204024,203933,203935,204055,203955,204132,204139,204163,204175,204183,187187,186497,186500,186505,187532,186529,187547,186619,187665,186662,187684,186689,185872,185875,186882,186888,186901,186947,185967,185973,186956,186280,186282,186981,186986,186990,187040,187045,185756,185758,185760,185689,185691,185766,185697,185769,185776,184641,184643,186771,186786,184677,186796,186828,186833,186834,186861,184547,184558,184541,185238,185249,185254,185269,185284,185318,185319,185721,185727,185603,185204,184401,184405,184419,184431,184436,184448,184472,184473,184455,184450,184457,184492,184494,184534,185355,185359,185452,185454,369745,386083,369047,376014,408619,392018,409158,392020,371823,410732,371827,387721,386370,369154,390888,387707,380161,378386,382047,382048,384200,386588,386593,384936,379230,389609,368007,371783,367934,371785,371790,410710,371793,387862,378058,391020,410876,371799,371803,371805,386068,385516,385518,386339,367927,385610,375919,384736,385617,378379,384193,372456,384194,375993,379359,409657,385510,415122,424790,415126,424579,415133,424911,424799,415129,424914,328605,415852,426520,424896,414790,424564,415249,416146,427011,415439,415257,415867,426521,415262,415263,415264,415871,415873,426939,426523,424573,424575,415890,415845,414738,424880,424885,415209,414752,426920,415103,416388,414763,415226,426513,415231,415108,414777,414778,414779,415084,415389,425615,426887,415175,415391,414717,426894,415409,415411,426903,424553,415181,415190,416111,424877,415289,415166,426968,426971,426546,424618,424394,425667,424621,424395,426554,425795,424410,415314,425798,412953,412954,415737,416378,415746,424343,414098,381882,381903,413388,326880,376349,413210,412681,414430,326201,320442,320444,425342,415912,415292,416192,414088,412842,342360,413528,413534,413525,414093,343060,412849,376313,413383,376334,412137,412139,413196,412537,413539,376316,413765,413076,425598,425744,425604,326198,415080,427548,324778,326038,416447,424932,427047,427048,416372,414082,414990,414549,415906,425344,334729,334730,381863,413178,413180,320433,415282,320320,320321,320437,424635,424303,425789,326739,325757,326744,326368,324935,424610,427042,426643,413349,416366,425391,424532,424864,424536,424698,424699,415807,424709,415367,415805,415072,424856,320170,424600,428564,427120,428583,320652,320308,425382,425722,414607,415060,416345,381705,381700,381717,376175,342992,342293,412372,376188,334536,335216,327717,334692,341030,380383,380387,391224,393412,393415,378189,380323,408010,411552,410845,393315,393318,388598,411435,388980,388594,385447,379487,371580,371587,383788,391953,388983,383750,385136,385139,383763,391959,322976,370657,393312,322972,367863,377385,409309,411711,411713,385669,340995,390659,411226,411734,341011,341015,383560,380212,376563,381209,380215,380219,381212,410765,391092,341165,371570,377379,391710,391942,391083,393305,391094,391097,391098,368906,382540,367173,386240,386241,322885,391734,370515,387510,370798,368715,372518,376550,383534,322970,372515,381198,380203,391926,371653,383270,393138,370793,383516,371890,409188,391066,407948,371704,386133,376524,381167,380194,385850,367415,367417,380189,385561,385563,367853,367855,379445,391699,384618,391703,372120,372129,386417,391193,387409,386403,376078,376487,376490,376493,376090,376108,376516,408644,370789,390342,369178,387757,387764,391209,387493,367950) GROUP BY DAY(timestamp_) ORDER BY DAY(timestamp_) ; SELECT calGetTrace(); /* Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows BPS PM cs1 3161 (numeric_val,id,timestamp_) 321404 119576 1347584 6.095 6652 TAS UM - - - - - - 6.059 30 */ SELECT calGetStats(); /* Query Stats: MaxMemPct-0; NumTempFiles-0; TempFileSpace-0B; ApproxPhyI/O-321404; CacheI/O-119576; BlocksTouched-119960; PartitionBlocksEliminated-1347584; MsgBytesIn-3MB; MsgBytesOut-41KB; Mode-Distributed Execution Time : 6.826 sec */
Answer Answered by Andrew Hutchings in this comment.
Hi,
The BPS step is executed mostly on the PM server and feeds its results directly into the TAS step on the UM server. So these two are executed in parallel. There are some parts of the execution which cannot be tracked easily hence the 0.7sec time difference.
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.