CHBenchmark Q10 Performance

[lmwnshn]

noisepage=# \pset pager off   
Pager usage is off.
noisepage=# explain (format tpl) SELECT c_id,
       c_last,
       sum(ol_amount) AS revenue,
       c_city,
       c_phone,
       n_name
FROM customer,
     oorder,
     order_line,
     nation
WHERE c_id = o_c_id
  AND c_w_id = o_w_id
  AND c_d_id = o_d_id
  AND ol_w_id = o_w_id
  AND ol_d_id = o_d_id
  AND ol_o_id = o_id
  AND o_entry_d >= '2007-01-02 00:00:00.000000'
  AND o_entry_d <= ol_delivery_d
  AND n_nationkey = ascii(substr(c_state, 1, 1))
GROUP BY c_id,
         c_last,
         c_city,
         c_phone,
         n_name
ORDER BY revenue DESC;
                                                                                                                                                           QUERY PLAN                                                                                                                                                            
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 struct AggPayload {                                                                                                                                                                                                                                                                                                            +
     gb_term_attr0: Integer                                                                                                                                                                                                                                                                                                     +
     gb_term_attr1: StringVal                                                                                                                                                                                                                                                                                                   +
     gb_term_attr2: StringVal                                                                                                                                                                                                                                                                                                   +
     gb_term_attr3: StringVal                                                                                                                                                                                                                                                                                                   +
     gb_term_attr4: StringVal                                                                                                                                                                                                                                                                                                   +
 }                                                                                                                                                                                                                                                                                                                              +
 struct AggValues {                                                                                                                                                                                                                                                                                                             +
     gb_term_attr0: Integer                                                                                                                                                                                                                                                                                                     +
     gb_term_attr1: StringVal                                                                                                                                                                                                                                                                                                   +
     gb_term_attr2: StringVal                                                                                                                                                                                                                                                                                                   +
     gb_term_attr3: StringVal                                                                                                                                                                                                                                                                                                   +
     gb_term_attr4: StringVal                                                                                                                                                                                                                                                                                                   +
 }                                                                                                                                                                                                                                                                                                                              +
 struct QueryState {                                                                                                                                                                                                                                                                                                            +
     execCtx     : *ExecutionContext                                                                                                                                                                                                                                                                                            +
     aggHashTable: AggregationHashTable                                                                                                                                                                                                                                                                                         +
 }                                                                                                                                                                                                                                                                                                                              +
 struct P2_State {                                                                                                                                                                                                                                                                                                              +
     tviBase      : TableVectorIterator                                                                                                                                                                                                                                                                                         +
     tviNeedsFree : bool                                                                                                                                                                                                                                                                                                        +
     tviBase1     : TableVectorIterator                                                                                                                                                                                                                                                                                         +
     tviNeedsFree1: bool                                                                                                                                                                                                                                                                                                        +
 }                                                                                                                                                                                                                                                                                                                              +
 struct P1_State {                                                                                                                                                                                                                                                                                                              +
                                                                                                                                                                                                                                                                                                                                +
 }                                                                                                                                                                                                                                                                                                                              +
 fun Query20_Pipeline1_KeyCheck(aggPayload: *AggPayload, aggValues: *AggValues) -> bool {                                                                                                                                                                                                                                       +
     if (SqlBoolToBool(aggPayload.gb_term_attr0 != aggValues.gb_term_attr0)) {                                                                                                                                                                                                                                                  +
         return false                                                                                                                                                                                                                                                                                                           +
     }                                                                                                                                                                                                                                                                                                                          +
     if (SqlBoolToBool(aggPayload.gb_term_attr1 != aggValues.gb_term_attr1)) {                                                                                                                                                                                                                                                  +
         return false                                                                                                                                                                                                                                                                                                           +
     }                                                                                                                                                                                                                                                                                                                          +
     if (SqlBoolToBool(aggPayload.gb_term_attr2 != aggValues.gb_term_attr2)) {                                                                                                                                                                                                                                                  +
         return false                                                                                                                                                                                                                                                                                                           +
     }                                                                                                                                                                                                                                                                                                                          +
     if (SqlBoolToBool(aggPayload.gb_term_attr3 != aggValues.gb_term_attr3)) {                                                                                                                                                                                                                                                  +
         return false                                                                                                                                                                                                                                                                                                           +
     }                                                                                                                                                                                                                                                                                                                          +
     if (SqlBoolToBool(aggPayload.gb_term_attr4 != aggValues.gb_term_attr4)) {                                                                                                                                                                                                                                                  +
         return false                                                                                                                                                                                                                                                                                                           +
     }                                                                                                                                                                                                                                                                                                                          +
     return true                                                                                                                                                                                                                                                                                                                +
 }                                                                                                                                                                                                                                                                                                                              +
                                                                                                                                                                                                                                                                                                                                +
 fun Query20_Init(queryState: *QueryState) -> nil {                                                                                                                                                                                                                                                                             +
     @aggHTInit(&queryState.aggHashTable, queryState.execCtx, @sizeOf(AggPayload))                                                                                                                                                                                                                                              +
     return                                                                                                                                                                                                                                                                                                                     +
 }                                                                                                                                                                                                                                                                                                                              +
                                                                                                                                                                                                                                                                                                                                +
 fun Query20_Pipeline2_InitPipelineState(queryState: *QueryState, pipelineState: *P2_State) -> nil {                                                                                                                                                                                                                            +
     return                                                                                                                                                                                                                                                                                                                     +
 }                                                                                                                                                                                                                                                                                                                              +
                                                                                                                                                                                                                                                                                                                                +
 fun Query20_Pipeline2_TearDownPipelineState(queryState: *QueryState, pipelineState: *P2_State) -> nil {                                                                                                                                                                                                                        +
     if (pipelineState.tviNeedsFree) {                                                                                                                                                                                                                                                                                          +
         @tableIterClose(&pipelineState.tviBase)                                                                                                                                                                                                                                                                                +
         pipelineState.tviNeedsFree = false                                                                                                                                                                                                                                                                                     +
     }                                                                                                                                                                                                                                                                                                                          +
     if (pipelineState.tviNeedsFree1) {                                                                                                                                                                                                                                                                                         +
         @tableIterClose(&pipelineState.tviBase1)                                                                                                                                                                                                                                                                               +
         pipelineState.tviNeedsFree1 = false                                                                                                                                                                                                                                                                                    +
     }                                                                                                                                                                                                                                                                                                                          +
     return                                                                                                                                                                                                                                                                                                                     +
 }                                                                                                                                                                                                                                                                                                                              +
                                                                                                                                                                                                                                                                                                                                +
 fun Query20_Pipeline2_SerialWork(queryState: *QueryState, pipelineState: *P2_State) -> nil {                                                                                                                                                                                                                                   +
     var tvi1 = &pipelineState.tviBase1                                                                                                                                                                                                                                                                                         +
     var col_oids3: [7]uint32                                                                                                                                                                                                                                                                                                   +
     col_oids3[0] = 18                                                                                                                                                                                                                                                                                                          +
     col_oids3[1] = 1                                                                                                                                                                                                                                                                                                           +
     col_oids3[2] = 15                                                                                                                                                                                                                                                                                                          +
     col_oids3[3] = 2                                                                                                                                                                                                                                                                                                           +
     col_oids3[4] = 6                                                                                                                                                                                                                                                                                                           +
     col_oids3[5] = 16                                                                                                                                                                                                                                                                                                          +
     col_oids3[6] = 3                                                                                                                                                                                                                                                                                                           +
     @tableIterInit(tvi1, queryState.execCtx, 1085, col_oids3)                                                                                                                                                                                                                                                                  +
     pipelineState.tviNeedsFree1 = true                                                                                                                                                                                                                                                                                         +
     var slot3: TupleSlot                                                                                                                                                                                                                                                                                                       +
     for (@tableIterAdvance(tvi1)) {                                                                                                                                                                                                                                                                                            +
         var vpi1 = @tableIterGetVPI(tvi1)                                                                                                                                                                                                                                                                                      +
         for (; @vpiHasNext(vpi1); @vpiAdvance(vpi1)) {                                                                                                                                                                                                                                                                         +
             slot3 = @vpiGetSlot(vpi1)                                                                                                                                                                                                                                                                                          +
             var col_oids2: [5]uint32                                                                                                                                                                                                                                                                                           +
             col_oids2[0] = 3                                                                                                                                                                                                                                                                                                   +
             col_oids2[1] = 2                                                                                                                                                                                                                                                                                                   +
             col_oids2[2] = 1                                                                                                                                                                                                                                                                                                   +
             col_oids2[3] = 4                                                                                                                                                                                                                                                                                                   +
             col_oids2[4] = 8                                                                                                                                                                                                                                                                                                   +
             var index_iter1: IndexIterator                                                                                                                                                                                                                                                                                     +
             @indexIteratorInit(&index_iter1, queryState.execCtx, 3, 1082, 1094, col_oids2)                                                                                                                                                                                                                                     +
             var lo_index_pr1 = @indexIteratorGetLoPR(&index_iter1)                                                                                                                                                                                                                                                             +
             var hi_index_pr1 = @indexIteratorGetHiPR(&index_iter1)                                                                                                                                                                                                                                                             +
             @prSetInt(lo_index_pr1, 0, @vpiGetInt(vpi1, 1))                                                                                                                                                                                                                                                                    +
             @prSetInt(lo_index_pr1, 1, @vpiGetInt(vpi1, 3))                                                                                                                                                                                                                                                                    +
             @prSetInt(lo_index_pr1, 2, @vpiGetInt(vpi1, 6))                                                                                                                                                                                                                                                                    +
             @prSetInt(hi_index_pr1, 0, @vpiGetInt(vpi1, 1))                                                                                                                                                                                                                                                                    +
             @prSetInt(hi_index_pr1, 1, @vpiGetInt(vpi1, 3))                                                                                                                                                                                                                                                                    +
             @prSetInt(hi_index_pr1, 2, @vpiGetInt(vpi1, 6))                                                                                                                                                                                                                                                                    +
             for (@indexIteratorScanAscending(&index_iter1, 0, 0); @indexIteratorAdvance(&index_iter1); ) {                                                                                                                                                                                                                     +
                 var table_pr1 = @indexIteratorGetTablePR(&index_iter1)                                                                                                                                                                                                                                                         +
                 var slot2 = @indexIteratorGetSlot(&index_iter1)                                                                                                                                                                                                                                                                +
                 if (SqlBoolToBool(@prGetTimestamp(table_pr1, 0) >= @timestampToSqlYMDHMSMU(2007, 1, 2, 0, 0, 0, 0, 0)) and SqlBoolToBool(@vpiGetInt(vpi1, 6) == @prGetInt(table_pr1, 4)) and SqlBoolToBool(@vpiGetInt(vpi1, 1) == @prGetInt(table_pr1, 1)) and SqlBoolToBool(@vpiGetInt(vpi1, 3) == @prGetInt(table_pr1, 2))) {+
                     var col_oids1: [4]uint32                                                                                                                                                                                                                                                                                   +
                     col_oids1[0] = 3                                                                                                                                                                                                                                                                                           +
                     col_oids1[1] = 2                                                                                                                                                                                                                                                                                           +
                     col_oids1[2] = 6                                                                                                                                                                                                                                                                                           +
                     col_oids1[3] = 1                                                                                                                                                                                                                                                                                           +
                     var index_iter: IndexIterator                                                                                                                                                                                                                                                                              +
                     @indexIteratorInit(&index_iter, queryState.execCtx, 3, 1076, 1077, col_oids1)                                                                                                                                                                                                                              +
                     var lo_index_pr = @indexIteratorGetLoPR(&index_iter)                                                                                                                                                                                                                                                       +
                     var hi_index_pr = @indexIteratorGetHiPR(&index_iter)                                                                                                                                                                                                                                                       +
                     @prSetInt(lo_index_pr, 0, @prGetInt(table_pr1, 1))                                                                                                                                                                                                                                                         +
                     @prSetInt(lo_index_pr, 1, @prGetInt(table_pr1, 2))                                                                                                                                                                                                                                                         +
                     @prSetInt(lo_index_pr, 2, @prGetInt(table_pr1, 3))                                                                                                                                                                                                                                                         +
                     @prSetInt(hi_index_pr, 0, @prGetInt(table_pr1, 1))                                                                                                                                                                                                                                                         +
                     @prSetInt(hi_index_pr, 1, @prGetInt(table_pr1, 2))                                                                                                                                                                                                                                                         +
                     @prSetInt(hi_index_pr, 2, @prGetInt(table_pr1, 3))                                                                                                                                                                                                                                                         +
                     for (@indexIteratorScanAscending(&index_iter, 0, 0); @indexIteratorAdvance(&index_iter); ) {                                                                                                                                                                                                               +
                         var table_pr = @indexIteratorGetTablePR(&index_iter)                                                                                                                                                                                                                                                   +
                         var slot1 = @indexIteratorGetSlot(&index_iter)                                                                                                                                                                                                                                                         +
                         if (SqlBoolToBool(@prGetInt(table_pr, 1) == @prGetInt(table_pr1, 1)) and SqlBoolToBool(@prGetInt(table_pr, 2) == @prGetInt(table_pr1, 2)) and SqlBoolToBool(@prGetInt(table_pr, 3) == @prGetInt(table_pr1, 3)) and SqlBoolToBool(@prGetTimestamp(table_pr1, 0) <= @prGetTimestampNull(table_pr, 0))) { +
                             var tvi = &pipelineState.tviBase                                                                                                                                                                                                                                                                   +
                             var col_oids: [2]uint32                                                                                                                                                                                                                                                                            +
                             col_oids[0] = 2                                                                                                                                                                                                                                                                                    +
                             col_oids[1] = 1                                                                                                                                                                                                                                                                                    +
                             @tableIterInit(tvi, queryState.execCtx, 1070, col_oids)                                                                                                                                                                                                                                            +
                             pipelineState.tviNeedsFree = true                                                                                                                                                                                                                                                                  +
                             var slot: TupleSlot                                                                                                                                                                                                                                                                                +
                             for (@tableIterAdvance(tvi)) {                                                                                                                                                                                                                                                                     +
                                 var vpi = @tableIterGetVPI(tvi)                                                                                                                                                                                                                                                                +
                                 for (; @vpiHasNext(vpi); @vpiAdvance(vpi)) {                                                                                                                                                                                                                                                   +
                                     slot = @vpiGetSlot(vpi)                                                                                                                                                                                                                                                                    +
                                     if (SqlBoolToBool(@vpiGetInt(vpi, 1) == @ascii(queryState.execCtx, @substring(queryState.execCtx, @vpiGetString(vpi1, 5), @intToSql(1), @intToSql(1))))) {                                                                                                                                 +
                                         var aggValues: AggValues                                                                                                                                                                                                                                                               +
                                         aggValues.gb_term_attr0 = @vpiGetInt(vpi1, 6)                                                                                                                                                                                                                                          +
                                         aggValues.gb_term_attr1 = @vpiGetString(vpi1, 4)                                                                                                                                                                                                                                       +
                                         aggValues.gb_term_attr2 = @vpiGetString(vpi1, 2)                                                                                                                                                                                                                                       +
                                         aggValues.gb_term_attr3 = @vpiGetString(vpi1, 0)                                                                                                                                                                                                                                       +
                                         aggValues.gb_term_attr4 = @vpiGetString(vpi, 0)                                                                                                                                                                                                                                        +
                                         var hashVal = @hash(aggValues.gb_term_attr0, aggValues.gb_term_attr1, aggValues.gb_term_attr2, aggValues.gb_term_attr3, aggValues.gb_term_attr4)                                                                                                                                       +
                                         var aggPayload = @ptrCast(*AggPayload, @aggHTLookup(&queryState.aggHashTable, hashVal, Query20_Pipeline1_KeyCheck, &aggValues))                                                                                                                                                        +
                                         if (aggPayload == nil) {                                                                                                                                                                                                                                                               +
                                             aggPayload = @ptrCast(*AggPayload, @aggHTInsert(&queryState.aggHashTable, hashVal, false))                                                                                                                                                                                         +
                                             aggPayload.gb_term_attr0 = aggValues.gb_term_attr0                                                                                                                                                                                                                                 +
                                             aggPayload.gb_term_attr1 = aggValues.gb_term_attr1                                                                                                                                                                                                                                 +
                                             aggPayload.gb_term_attr2 = aggValues.gb_term_attr2                                                                                                                                                                                                                                 +
                                             aggPayload.gb_term_attr3 = aggValues.gb_term_attr3                                                                                                                                                                                                                                 +
                                             aggPayload.gb_term_attr4 = aggValues.gb_term_attr4                                                                                                                                                                                                                                 +
                                         }                                                                                                                                                                                                                                                                                      +
                                     }                                                                                                                                                                                                                                                                                          +
                                 }                                                                                                                                                                                                                                                                                              +
                                 var vpi_num_tuples = @tableIterGetVPINumTuples(tvi)                                                                                                                                                                                                                                            +
                             }                                                                                                                                                                                                                                                                                                  +
                             @tableIterClose(&pipelineState.tviBase)                                                                                                                                                                                                                                                            +
                             pipelineState.tviNeedsFree = false                                                                                                                                                                                                                                                                 +
                         }                                                                                                                                                                                                                                                                                                      +
                     }                                                                                                                                                                                                                                                                                                          +
                     @indexIteratorFree(&index_iter)                                                                                                                                                                                                                                                                            +
                 }                                                                                                                                                                                                                                                                                                              +
             }                                                                                                                                                                                                                                                                                                                  +
             @indexIteratorFree(&index_iter1)                                                                                                                                                                                                                                                                                   +
         }                                                                                                                                                                                                                                                                                                                      +
         var vpi_num_tuples1 = @tableIterGetVPINumTuples(tvi1)                                                                                                                                                                                                                                                                  +
     }                                                                                                                                                                                                                                                                                                                          +
     @tableIterClose(&pipelineState.tviBase1)                                                                                                                                                                                                                                                                                   +
     pipelineState.tviNeedsFree1 = false                                                                                                                                                                                                                                                                                        +
     return                                                                                                                                                                                                                                                                                                                     +
 }                                                                                                                                                                                                                                                                                                                              +
                                                                                                                                                                                                                                                                                                                                +
 fun Query20_Pipeline2_Init(queryState: *QueryState) -> nil {                                                                                                                                                                                                                                                                   +
     var threadStateContainer = @execCtxGetTLS(queryState.execCtx)                                                                                                                                                                                                                                                              +
     @tlsReset(threadStateContainer, @sizeOf(P2_State), Query20_Pipeline2_InitPipelineState, Query20_Pipeline2_TearDownPipelineState, queryState)                                                                                                                                                                               +
     return                                                                                                                                                                                                                                                                                                                     +
 }                                                                                                                                                                                                                                                                                                                              +
                                                                                                                                                                                                                                                                                                                                +
 fun Query20_Pipeline2_Run(queryState: *QueryState) -> nil {                                                                                                                                                                                                                                                                    +
     var pipelineState = @ptrCast(*P2_State, @tlsGetCurrentThreadState(@execCtxGetTLS(queryState.execCtx)))                                                                                                                                                                                                                     +
     Query20_Pipeline2_SerialWork(queryState, pipelineState)                                                                                                                                                                                                                                                                    +
     return                                                                                                                                                                                                                                                                                                                     +
 }                                                                                                                                                                                                                                                                                                                              +
                                                                                                                                                                                                                                                                                                                                +
 fun Query20_Pipeline2_TearDown(queryState: *QueryState) -> nil {                                                                                                                                                                                                                                                               +
     @tlsClear(@execCtxGetTLS(queryState.execCtx))                                                                                                                                                                                                                                                                              +
     @ensureTrackersStopped(queryState.execCtx)                                                                                                                                                                                                                                                                                 +
     return                                                                                                                                                                                                                                                                                                                     +
 }                                                                                                                                                                                                                                                                                                                              +
                                                                                                                                                                                                                                                                                                                                +
 fun Query20_Pipeline1_InitPipelineState(queryState: *QueryState, pipelineState: *P1_State) -> nil {                                                                                                                                                                                                                            +
     return                                                                                                                                                                                                                                                                                                                     +
 }                                                                                                                                                                                                                                                                                                                              +
                                                                                                                                                                                                                                                                                                                                +
 fun Query20_Pipeline1_TearDownPipelineState(queryState: *QueryState, pipelineState: *P1_State) -> nil {                                                                                                                                                                                                                        +
     return                                                                                                                                                                                                                                                                                                                     +
 }                                                                                                                                                                                                                                                                                                                              +
                                                                                                                                                                                                                                                                                                                                +
 fun Query20_Pipeline1_SerialWork(queryState: *QueryState, pipelineState: *P1_State) -> nil {                                                                                                                                                                                                                                   +
     var iterBase: AHTIterator                                                                                                                                                                                                                                                                                                  +
     var iter = &iterBase                                                                                                                                                                                                                                                                                                       +
     for (@aggHTIterInit(iter, &queryState.aggHashTable); @aggHTIterHasNext(iter); @aggHTIterNext(iter)) {                                                                                                                                                                                                                      +
         var aggRow = @ptrCast(*AggPayload, @aggHTIterGetRow(iter))                                                                                                                                                                                                                                                             +
     }                                                                                                                                                                                                                                                                                                                          +
     @aggHTIterClose(iter)                                                                                                                                                                                                                                                                                                      +
     return                                                                                                                                                                                                                                                                                                                     +
 }                                                                                                                                                                                                                                                                                                                              +
                                                                                                                                                                                                                                                                                                                                +
 fun Query20_Pipeline1_Init(queryState: *QueryState) -> nil {                                                                                                                                                                                                                                                                   +
     var threadStateContainer = @execCtxGetTLS(queryState.execCtx)                                                                                                                                                                                                                                                              +
     @tlsReset(threadStateContainer, @sizeOf(P1_State), Query20_Pipeline1_InitPipelineState, Query20_Pipeline1_TearDownPipelineState, queryState)                                                                                                                                                                               +
     return                                                                                                                                                                                                                                                                                                                     +
 }                                                                                                                                                                                                                                                                                                                              +
                                                                                                                                                                                                                                                                                                                                +
 fun Query20_Pipeline1_Run(queryState: *QueryState) -> nil {                                                                                                                                                                                                                                                                    +
     var pipelineState = @ptrCast(*P1_State, @tlsGetCurrentThreadState(@execCtxGetTLS(queryState.execCtx)))                                                                                                                                                                                                                     +
     Query20_Pipeline1_SerialWork(queryState, pipelineState)                                                                                                                                                                                                                                                                    +
     return                                                                                                                                                                                                                                                                                                                     +
 }                                                                                                                                                                                                                                                                                                                              +
                                                                                                                                                                                                                                                                                                                                +
 fun Query20_Pipeline1_TearDown(queryState: *QueryState) -> nil {                                                                                                                                                                                                                                                               +
     @tlsClear(@execCtxGetTLS(queryState.execCtx))                                                                                                                                                                                                                                                                              +
     @ensureTrackersStopped(queryState.execCtx)                                                                                                                                                                                                                                                                                 +
     return                                                                                                                                                                                                                                                                                                                     +
 }                                                                                                                                                                                                                                                                                                                              +
                                                                                                                                                                                                                                                                                                                                +
 fun Query20_TearDown(queryState: *QueryState) -> nil {                                                                                                                                                                                                                                                                         +
     @aggHTFree(&queryState.aggHashTable)                                                                                                                                                                                                                                                                                       +
     return                                                                                                                                                                                                                                                                                                                     +
 }                                                                                                                                                                                                                                                                                                                              +
                                                                                                                                                                                                                                                                                                                                +
                                                                                                                                                                                                                                                                                                                                +
 
(1 row)