CHBenchmark Q17 Performance

[lmwnshn]

noisepage=# \pset pager off
Pager usage is off.
noisepage=# explain (format tpl) SELECT SUM (ol_amount) / 2.0 AS avg_yearly
FROM order_line,
  (SELECT i_id, AVG (ol_quantity) AS a
   FROM item,
        order_line
   WHERE i_data LIKE '%b'
     AND ol_i_id = i_id
   GROUP BY i_id) t
WHERE ol_i_id = t.i_id
  AND ol_quantity < t.a;;
                                                                            QUERY PLAN                                                                            
------------------------------------------------------------------------------------------------------------------------------------------------------------------
 struct AggPayload {                                                                                                                                             +
                                                                                                                                                                 +
 }                                                                                                                                                               +
 struct AggValues {                                                                                                                                              +
                                                                                                                                                                 +
 }                                                                                                                                                               +
 struct AggPayload1 {                                                                                                                                            +
     gb_term_attr0 : Integer                                                                                                                                     +
     agg_term_attr0: AvgAggregate                                                                                                                                +
 }                                                                                                                                                               +
 struct AggValues1 {                                                                                                                                             +
     gb_term_attr0 : Integer                                                                                                                                     +
     agg_term_attr0: Real                                                                                                                                        +
 }                                                                                                                                                               +
 struct QueryState {                                                                                                                                             +
     execCtx     : *ExecutionContext                                                                                                                             +
     aggHashTable: AggregationHashTable                                                                                                                          +
     aggs        : AggPayload                                                                                                                                    +
 }                                                                                                                                                               +
 struct P3_State {                                                                                                                                               +
     filterManager: FilterManager                                                                                                                                +
     tviBase1     : TableVectorIterator                                                                                                                          +
     tviNeedsFree1: bool                                                                                                                                         +
     tviBase2     : TableVectorIterator                                                                                                                          +
     tviNeedsFree2: bool                                                                                                                                         +
     aggHashTable1: AggregationHashTable                                                                                                                         +
 }                                                                                                                                                               +
 struct P2_State {                                                                                                                                               +
     tviBase     : TableVectorIterator                                                                                                                           +
     tviNeedsFree: bool                                                                                                                                          +
     aggs1       : AggPayload                                                                                                                                    +
 }                                                                                                                                                               +
 struct P1_State {                                                                                                                                               +
                                                                                                                                                                 +
 }                                                                                                                                                               +
 fun MergeAggregates(queryState: *QueryState, pipelineState: *P2_State) -> nil {                                                                                 +
     return                                                                                                                                                      +
 }                                                                                                                                                               +
                                                                                                                                                                 +
 fun Query4_Pipeline2_KeyCheckPartial(lhs: *AggPayload1, rhs: *AggPayload1) -> bool {                                                                            +
     if (SqlBoolToBool(lhs.gb_term_attr0 != rhs.gb_term_attr0)) {                                                                                                +
         return false                                                                                                                                            +
     }                                                                                                                                                           +
     return true                                                                                                                                                 +
 }                                                                                                                                                               +
                                                                                                                                                                 +
 fun Query4_Pipeline2_MergePartitions(queryState: *QueryState, aggHashTable: *AggregationHashTable, ahtOvfIter: *AHTOverflowPartitionIterator) -> nil {          +
     for (; @aggPartIterHasNext(ahtOvfIter); @aggPartIterNext(ahtOvfIter)) {                                                                                     +
         var hashVal = @aggPartIterGetHash(ahtOvfIter)                                                                                                           +
         var partialRow = @ptrCast(*AggPayload1, @aggPartIterGetRow(ahtOvfIter))                                                                                 +
         var aggPayload = @ptrCast(*AggPayload1, @aggHTLookup(aggHashTable, hashVal, Query4_Pipeline2_KeyCheckPartial, partialRow))                              +
         if (aggPayload == nil) {                                                                                                                                +
             @aggHTLink(aggHashTable, @aggPartIterGetRowEntry(ahtOvfIter))                                                                                       +
         } else {                                                                                                                                                +
             @aggMerge(&aggPayload.agg_term_attr0, &partialRow.agg_term_attr0)                                                                                   +
         }                                                                                                                                                       +
     }                                                                                                                                                           +
     return                                                                                                                                                      +
 }                                                                                                                                                               +
                                                                                                                                                                 +
 fun Query4_Pipeline2_KeyCheck(aggPayload: *AggPayload1, aggValues: *AggValues1) -> bool {                                                                       +
     if (SqlBoolToBool(aggPayload.gb_term_attr0 != aggValues.gb_term_attr0)) {                                                                                   +
         return false                                                                                                                                            +
     }                                                                                                                                                           +
     return true                                                                                                                                                 +
 }                                                                                                                                                               +
                                                                                                                                                                 +
 fun Query4_Pipeline3_FilterClause(execCtx: *ExecutionContext, vp: *VectorProjection, tids: *TupleIdList, context: *uint8) -> nil {                              +
     @filterLike(execCtx, vp, 0, @stringToSql("%b"), tids)                                                                                                       +
     return                                                                                                                                                      +
 }                                                                                                                                                               +
                                                                                                                                                                 +
 fun Query4_Init(queryState: *QueryState) -> nil {                                                                                                               +
     @aggHTInit(&queryState.aggHashTable, queryState.execCtx, @sizeOf(AggPayload1))                                                                              +
     return                                                                                                                                                      +
 }                                                                                                                                                               +
                                                                                                                                                                 +
 fun Query4_Pipeline3_InitPipelineState(queryState: *QueryState, pipelineState: *P3_State) -> nil {                                                              +
     @aggHTInit(&pipelineState.aggHashTable1, queryState.execCtx, @sizeOf(AggPayload1))                                                                          +
     @filterManagerInit(&pipelineState.filterManager, queryState.execCtx)                                                                                        +
     @filterManagerInsertFilter(&pipelineState.filterManager, Query4_Pipeline3_FilterClause)                                                                     +
     return                                                                                                                                                      +
 }                                                                                                                                                               +
                                                                                                                                                                 +
 fun Query4_Pipeline3_TearDownPipelineState(queryState: *QueryState, pipelineState: *P3_State) -> nil {                                                          +
     @aggHTFree(&pipelineState.aggHashTable1)                                                                                                                    +
     @filterManagerFree(&pipelineState.filterManager)                                                                                                            +
     if (pipelineState.tviNeedsFree1) {                                                                                                                          +
         @tableIterClose(&pipelineState.tviBase1)                                                                                                                +
         pipelineState.tviNeedsFree1 = false                                                                                                                     +
     }                                                                                                                                                           +
     if (pipelineState.tviNeedsFree2) {                                                                                                                          +
         @tableIterClose(&pipelineState.tviBase2)                                                                                                                +
         pipelineState.tviNeedsFree2 = false                                                                                                                     +
     }                                                                                                                                                           +
     return                                                                                                                                                      +
 }                                                                                                                                                               +
                                                                                                                                                                 +
 fun Query4_Pipeline3_ParallelWork(queryState: *QueryState, pipelineState: *P3_State, tvi2: *TableVectorIterator) -> nil {                                       +
     var slot2: TupleSlot                                                                                                                                        +
     for (@tableIterAdvance(tvi2)) {                                                                                                                             +
         var vpi2 = @tableIterGetVPI(tvi2)                                                                                                                       +
         for (; @vpiHasNext(vpi2); @vpiAdvance(vpi2)) {                                                                                                          +
             slot2 = @vpiGetSlot(vpi2)                                                                                                                           +
             var tvi1 = &pipelineState.tviBase1                                                                                                                  +
             var col_oids1: [2]uint32                                                                                                                            +
             col_oids1[0] = 4                                                                                                                                    +
             col_oids1[1] = 1                                                                                                                                    +
             @tableIterInit(tvi1, queryState.execCtx, 1081, col_oids1)                                                                                           +
             pipelineState.tviNeedsFree1 = true                                                                                                                  +
             var slot1: TupleSlot                                                                                                                                +
             for (@tableIterAdvance(tvi1)) {                                                                                                                     +
                 var vpi1 = @tableIterGetVPI(tvi1)                                                                                                               +
                 @filterManagerRunFilters(&pipelineState.filterManager, vpi1, queryState.execCtx)                                                                +
                 for (; @vpiHasNextFiltered(vpi1); @vpiAdvanceFiltered(vpi1)) {                                                                                  +
                     slot1 = @vpiGetSlot(vpi1)                                                                                                                   +
                     if (SqlBoolToBool(@vpiGetInt(vpi2, 1) == @vpiGetInt(vpi1, 1))) {                                                                            +
                         var aggValues: AggValues1                                                                                                               +
                         aggValues.gb_term_attr0 = @vpiGetInt(vpi1, 1)                                                                                           +
                         aggValues.agg_term_attr0 = @vpiGetDouble(vpi2, 0)                                                                                       +
                         var hashVal = @hash(aggValues.gb_term_attr0)                                                                                            +
                         var aggPayload = @ptrCast(*AggPayload1, @aggHTLookup(&pipelineState.aggHashTable1, hashVal, Query4_Pipeline2_KeyCheck, &aggValues))     +
                         if (aggPayload == nil) {                                                                                                                +
                             aggPayload = @ptrCast(*AggPayload1, @aggHTInsert(&pipelineState.aggHashTable1, hashVal, true))                                      +
                             aggPayload.gb_term_attr0 = aggValues.gb_term_attr0                                                                                  +
                             @aggInit(&aggPayload.agg_term_attr0)                                                                                                +
                         }                                                                                                                                       +
                         @aggAdvance(&aggPayload.agg_term_attr0, &aggValues.agg_term_attr0)                                                                      +
                     }                                                                                                                                           +
                 }                                                                                                                                               +
                 var vpi_num_tuples = @tableIterGetVPINumTuples(tvi1)                                                                                            +
             }                                                                                                                                                   +
             @tableIterClose(&pipelineState.tviBase1)                                                                                                            +
             pipelineState.tviNeedsFree1 = false                                                                                                                 +
         }                                                                                                                                                       +
         var vpi_num_tuples1 = @tableIterGetVPINumTuples(tvi2)                                                                                                   +
     }                                                                                                                                                           +
     return                                                                                                                                                      +
 }                                                                                                                                                               +
                                                                                                                                                                 +
 fun Query4_Pipeline3_Init(queryState: *QueryState) -> nil {                                                                                                     +
     var threadStateContainer = @execCtxGetTLS(queryState.execCtx)                                                                                               +
     @tlsReset(threadStateContainer, @sizeOf(P3_State), Query4_Pipeline3_InitPipelineState, Query4_Pipeline3_TearDownPipelineState, queryState)                  +
     return                                                                                                                                                      +
 }                                                                                                                                                               +
                                                                                                                                                                 +
 fun Query4_Pipeline3_Run(queryState: *QueryState) -> nil {                                                                                                      +
     var pipelineState = @ptrCast(*P3_State, @tlsGetCurrentThreadState(@execCtxGetTLS(queryState.execCtx)))                                                      +
     var col_oids2: [2]uint32                                                                                                                                    +
     col_oids2[0] = 9                                                                                                                                            +
     col_oids2[1] = 5                                                                                                                                            +
     @iterateTableParallel(1068, col_oids2, queryState, queryState.execCtx, Query4_Pipeline3_ParallelWork)                                                       +
     @aggHTMoveParts(&queryState.aggHashTable, @execCtxGetTLS(queryState.execCtx), @offsetOf(P3_State, aggHashTable1), Query4_Pipeline2_MergePartitions)         +
     return                                                                                                                                                      +
 }                                                                                                                                                               +
                                                                                                                                                                 +
 fun Query4_Pipeline3_TearDown(queryState: *QueryState) -> nil {                                                                                                 +
     @tlsClear(@execCtxGetTLS(queryState.execCtx))                                                                                                               +
     @ensureTrackersStopped(queryState.execCtx)                                                                                                                  +
     return                                                                                                                                                      +
 }                                                                                                                                                               +
                                                                                                                                                                 +
 fun Query4_Pipeline2_InitPipelineState(queryState: *QueryState, pipelineState: *P2_State) -> nil {                                                              +
     return                                                                                                                                                      +
 }                                                                                                                                                               +
                                                                                                                                                                 +
 fun Query4_Pipeline2_TearDownPipelineState(queryState: *QueryState, pipelineState: *P2_State) -> nil {                                                          +
     if (pipelineState.tviNeedsFree) {                                                                                                                           +
         @tableIterClose(&pipelineState.tviBase)                                                                                                                 +
         pipelineState.tviNeedsFree = false                                                                                                                      +
     }                                                                                                                                                           +
     return                                                                                                                                                      +
 }                                                                                                                                                               +
                                                                                                                                                                 +
 fun Query4_Pipeline2_ParallelWork(queryState: *QueryState, pipelineState: *P2_State, aggHashTable: *AggregationHashTable) -> nil {                              +
     var iterBase: AHTIterator                                                                                                                                   +
     var iter = &iterBase                                                                                                                                        +
     for (@aggHTIterInit(iter, aggHashTable); @aggHTIterHasNext(iter); @aggHTIterNext(iter)) {                                                                   +
         var aggRow1 = @ptrCast(*AggPayload1, @aggHTIterGetRow(iter))                                                                                            +
         var tvi = &pipelineState.tviBase                                                                                                                        +
         var col_oids: [2]uint32                                                                                                                                 +
         col_oids[0] = 9                                                                                                                                         +
         col_oids[1] = 5                                                                                                                                         +
         @tableIterInit(tvi, queryState.execCtx, 1068, 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) == aggRow1.gb_term_attr0) and SqlBoolToBool(@vpiGetDouble(vpi, 0) < @aggResult(&aggRow1.agg_term_attr0))) {+
                     var aggValues: AggValues                                                                                                                    +
                 }                                                                                                                                               +
             }                                                                                                                                                   +
             var vpi_num_tuples = @tableIterGetVPINumTuples(tvi)                                                                                                 +
         }                                                                                                                                                       +
         @tableIterClose(&pipelineState.tviBase)                                                                                                                 +
         pipelineState.tviNeedsFree = false                                                                                                                      +
     }                                                                                                                                                           +
     @aggHTIterClose(iter)                                                                                                                                       +
     return                                                                                                                                                      +
 }                                                                                                                                                               +
                                                                                                                                                                 +
 fun Query4_Pipeline2_Init(queryState: *QueryState) -> nil {                                                                                                     +
     var threadStateContainer = @execCtxGetTLS(queryState.execCtx)                                                                                               +
     @tlsReset(threadStateContainer, @sizeOf(P2_State), Query4_Pipeline2_InitPipelineState, Query4_Pipeline2_TearDownPipelineState, queryState)                  +
     return                                                                                                                                                      +
 }                                                                                                                                                               +
                                                                                                                                                                 +
 fun Query4_Pipeline2_Run(queryState: *QueryState) -> nil {                                                                                                      +
     var pipelineState = @ptrCast(*P2_State, @tlsGetCurrentThreadState(@execCtxGetTLS(queryState.execCtx)))                                                      +
     @aggHTParallelPartScan(&queryState.aggHashTable, queryState, @execCtxGetTLS(queryState.execCtx), Query4_Pipeline2_ParallelWork)                             +
     @tlsIterate(@execCtxGetTLS(queryState.execCtx), queryState, MergeAggregates)                                                                                +
     return                                                                                                                                                      +
 }                                                                                                                                                               +
                                                                                                                                                                 +
 fun Query4_Pipeline2_TearDown(queryState: *QueryState) -> nil {                                                                                                 +
     @tlsClear(@execCtxGetTLS(queryState.execCtx))                                                                                                               +
     @ensureTrackersStopped(queryState.execCtx)                                                                                                                  +
     return                                                                                                                                                      +
 }                                                                                                                                                               +
                                                                                                                                                                 +
 fun Query4_Pipeline1_InitPipelineState(queryState: *QueryState, pipelineState: *P1_State) -> nil {                                                              +
     return                                                                                                                                                      +
 }                                                                                                                                                               +
                                                                                                                                                                 +
 fun Query4_Pipeline1_TearDownPipelineState(queryState: *QueryState, pipelineState: *P1_State) -> nil {                                                          +
     return                                                                                                                                                      +
 }                                                                                                                                                               +
                                                                                                                                                                 +
 fun Query4_Pipeline1_SerialWork(queryState: *QueryState, pipelineState: *P1_State) -> nil {                                                                     +
     var aggRow = &queryState.aggs                                                                                                                               +
     return                                                                                                                                                      +
 }                                                                                                                                                               +
                                                                                                                                                                 +
 fun Query4_Pipeline1_Init(queryState: *QueryState) -> nil {                                                                                                     +
     var threadStateContainer = @execCtxGetTLS(queryState.execCtx)                                                                                               +
     @tlsReset(threadStateContainer, @sizeOf(P1_State), Query4_Pipeline1_InitPipelineState, Query4_Pipeline1_TearDownPipelineState, queryState)                  +
     return                                                                                                                                                      +
 }                                                                                                                                                               +
                                                                                                                                                                 +
 fun Query4_Pipeline1_Run(queryState: *QueryState) -> nil {                                                                                                      +
     var pipelineState = @ptrCast(*P1_State, @tlsGetCurrentThreadState(@execCtxGetTLS(queryState.execCtx)))                                                      +
     Query4_Pipeline1_SerialWork(queryState, pipelineState)                                                                                                      +
     return                                                                                                                                                      +
 }                                                                                                                                                               +
                                                                                                                                                                 +
 fun Query4_Pipeline1_TearDown(queryState: *QueryState) -> nil {                                                                                                 +
     @tlsClear(@execCtxGetTLS(queryState.execCtx))                                                                                                               +
     @ensureTrackersStopped(queryState.execCtx)                                                                                                                  +
     return                                                                                                                                                      +
 }                                                                                                                                                               +
                                                                                                                                                                 +
 fun Query4_TearDown(queryState: *QueryState) -> nil {                                                                                                           +
     @aggHTFree(&queryState.aggHashTable)                                                                                                                        +
     return                                                                                                                                                      +
 }                                                                                                                                                               +
                                                                                                                                                                 +
                                                                                                                                                                 +
 
(1 row)