This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-15. For the latest, interactive version please visit https://mariadb.com/kb/.

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.