Query speed with where clause extremely slow

Gerrit_van_Zyl1 · May 8, 2024, 4:20pm

Starrocks Version 3.2.6-2585333

I have two queries - both give the same result but are just structured differently.

Query 1: Takes more than 5 minutes to complete

select statement.branch_id,
sum(si.amount) AS production_inc_vat,
SUM(si.amount_exclusive) AS production,
SUM(si.premium) AS premuim,
SUM(si.fees) AS fee,
SUM(si.commission) AS commission
FROM statement
JOIN statement_item si ON si.statement_id = statement.id
JOIN product_agreement pa ON pa.id = si.product_agreement_id
JOIN branch_product bp ON bp.id = pa.branch_product_id
join branch_client bc on bc.id = bp.branch_client_id and bc.branch_id = statement.branch_id
WHERE statement.branch_id = 74
GROUP BY statement.branch_id;

Explain Cost query 1

PLAN FRAGMENT 0(F12)
Output Exprs:7: branch_id | 130: sum | 131: sum | 132: sum | 133: sum | 134: sum
Input Partition: UNPARTITIONED
RESULT SINK

21:EXCHANGE
cardinality: 562

PLAN FRAGMENT 1(F11)

Input Partition: HASH_PARTITIONED: 7: branch_id
OutPut Partition: UNPARTITIONED
OutPut Exchange Id: 21

20:AGGREGATE (merge finalize)
" | aggregate: sum[([130: sum, DECIMAL128(38,0), true]); args: DECIMAL64; result: DECIMAL128(38,0); args nullable: true; result nullable: true], sum[([131: sum, DECIMAL128(38,0), true]); args: DECIMAL64; result: DECIMAL128(38,0); args nullable: true; result nullable: true], sum[([132: sum, DECIMAL128(38,0), true]); args: DECIMAL64; result: DECIMAL128(38,0); args nullable: true; result nullable: true], sum[([133: sum, DECIMAL128(38,0), true]); args: DECIMAL64; result: DECIMAL128(38,0); args nullable: true; result nullable: true], sum[([134: sum, DECIMAL128(38,0), true]); args: DECIMAL64; result: DECIMAL128(38,0); args nullable: true; result nullable: true]"
" | group by: [7: branch_id, BIGINT, true]"
| cardinality: 562
| column statistics:
" | * branch_id–>[74.0, 74.0, 0.0, 8.0, 562.0] ESTIMATE"
" | * sum–>[-5.3390441E7, 5.3390441E7, 0.0, 8.0, 562.0] ESTIMATE"
" | * sum–>[-4.6426471E7, 4.6426471E7, 0.0, 8.0, 562.0] ESTIMATE"
" | * sum–>[-2.2353515E7, 9.017323693E9, 0.0, 8.0, 562.0] ESTIMATE"
" | * sum–>[-128772.0, 880898.0, 0.0, 8.0, 562.0] ESTIMATE"
" | * sum–>[-5.3390441E7, 5.3390441E7, 0.0, 8.0, 562.0] ESTIMATE"
|
19:EXCHANGE
distribution type: SHUFFLE
" partition exprs: [7: branch_id, BIGINT, true]"
cardinality: 562

PLAN FRAGMENT 2(F00)

Input Partition: RANDOM
OutPut Partition: HASH_PARTITIONED: 7: branch_id
OutPut Exchange Id: 19

18:AGGREGATE (update serialize)
| STREAMING
" | aggregate: sum[([35: amount, DECIMAL64(10,0), true]); args: DECIMAL64; result: DECIMAL128(38,0); args nullable: true; result nullable: true], sum[([50: amount_exclusive, DECIMAL64(10,0), true]); args: DECIMAL64; result: DECIMAL128(38,0); args nullable: true; result nullable: true], sum[([33: premium, DECIMAL64(10,0), true]); args: DECIMAL64; result: DECIMAL128(38,0); args nullable: true; result nullable: true], sum[([34: fees, DECIMAL64(10,0), true]); args: DECIMAL64; result: DECIMAL128(38,0); args nullable: true; result nullable: true], sum[([65: commission, DECIMAL64(10,0), true]); args: DECIMAL64; result: DECIMAL128(38,0); args nullable: true; result nullable: true]"
" | group by: [7: branch_id, BIGINT, true]"
| cardinality: 562
| column statistics:
" | * branch_id–>[74.0, 74.0, 0.0, 8.0, 562.0] ESTIMATE"
" | * sum–>[-5.3390441E7, 5.3390441E7, 0.0, 8.0, 562.0] ESTIMATE"
" | * sum–>[-4.6426471E7, 4.6426471E7, 0.0, 8.0, 562.0] ESTIMATE"
" | * sum–>[-2.2353515E7, 9.017323693E9, 0.0, 8.0, 562.0] ESTIMATE"
" | * sum–>[-128772.0, 880898.0, 0.0, 8.0, 562.0] ESTIMATE"
" | * sum–>[-5.3390441E7, 5.3390441E7, 0.0, 8.0, 562.0] ESTIMATE"
|
17:Project
| output columns:
" | 7 ↔ [7: branch_id, BIGINT, true]"
" | 33 ↔ [33: premium, DECIMAL64(10,0), true]"
" | 34 ↔ [34: fees, DECIMAL64(10,0), true]"
" | 35 ↔ [35: amount, DECIMAL64(10,0), true]"
" | 50 ↔ [50: amount_exclusive, DECIMAL64(10,0), true]"
" | 65 ↔ [65: commission, DECIMAL64(10,0), true]"
| cardinality: 1254
| column statistics:
" | * branch_id–>[74.0, 74.0, 0.0, 8.0, 562.0] ESTIMATE"
" | * premium–>[-2.2353515E7, 9.017323693E9, 0.0, 8.0, 1392.8576512455516] ESTIMATE"
" | * fees–>[-128772.0, 880898.0, 0.0, 8.0, 1392.8576512455516] ESTIMATE"
" | * amount–>[-5.3390441E7, 5.3390441E7, 0.0, 8.0, 1392.8576512455516] ESTIMATE"
" | * amount_exclusive–>[-4.6426471E7, 4.6426471E7, 0.0, 8.0, 1392.8576512455516] ESTIMATE"
" | * commission–>[-5.3390441E7, 5.3390441E7, 0.0, 8.0, 1392.8576512455516] ESTIMATE"
|
16:HASH JOIN
| join op: INNER JOIN (BROADCAST)
" | equal join conjunct: [66: product_agreement_id, BIGINT, true] = [90: id, BIGINT, false]"
" | equal join conjunct: [37: statement_id, BIGINT, true] = [1: id, BIGINT, false]"
| build runtime filters:
" | - filter_id = 3, build_expr = (90: id), remote = false"
" | - filter_id = 4, build_expr = (1: id), remote = false"
" | output columns: 7, 33, 34, 35, 50, 65"
| can local shuffle: true
| cardinality: 1254
| column statistics:
" | * id–>[381.0, 997937.0, 0.0, 8.0, 1392.8576512455516] ESTIMATE"
" | * branch_id–>[74.0, 74.0, 0.0, 8.0, 562.0] ESTIMATE"
" | * premium–>[-2.2353515E7, 9.017323693E9, 0.0, 8.0, 1392.8576512455516] ESTIMATE"
" | * fees–>[-128772.0, 880898.0, 0.0, 8.0, 1392.8576512455516] ESTIMATE"
" | * amount–>[-5.3390441E7, 5.3390441E7, 0.0, 8.0, 1392.8576512455516] ESTIMATE"
" | * statement_id–>[381.0, 997937.0, 0.0, 8.0, 1392.8576512455516] ESTIMATE"
" | * amount_exclusive–>[-4.6426471E7, 4.6426471E7, 0.0, 8.0, 1392.8576512455516] ESTIMATE"
" | * commission–>[-5.3390441E7, 5.3390441E7, 0.0, 8.0, 1392.8576512455516] ESTIMATE"
" | * branch_id–>[74.0, 74.0, 0.0, 8.0, 548.0] ESTIMATE"
|
|----15:EXCHANGE
| distribution type: BROADCAST
| cardinality: 10910
|
0:OlapScanNode
" table: statement_item, rollup: statement_item"
preAggregation: on
Predicates: 37: statement_id IS NOT NULL
" partitionsRatio=1/1, tabletsRatio=2/2"
" tabletList=18386,18388"
" actualRows=60602235, avgRowSize=56.0"
cardinality: 60602233
probe runtime filters:
" - filter_id = 3, probe_expr = (66: product_agreement_id)"
" - filter_id = 4, probe_expr = (37: statement_id)"
column statistics:
" * premium–>[-2.2353515E7, 9.017323693E9, 0.0, 8.0, 180343.0] ESTIMATE"
" * fees–>[-128772.0, 880898.0, 0.0, 8.0, 8957.0] ESTIMATE"
" * amount–>[-5.3390441E7, 5.3390441E7, 0.0, 8.0, 72604.0] ESTIMATE"
" * statement_id–>[381.0, 997937.0, 0.0, 8.0, 786710.0] ESTIMATE"
" * amount_exclusive–>[-4.6426471E7, 4.6426471E7, 0.0, 8.0, 67754.0] ESTIMATE"
" * commission–>[-5.3390441E7, 5.3390441E7, 0.0, 8.0, 72158.0] ESTIMATE"
" * product_agreement_id–>[6063.0, 3894394.0, 0.03156966179777567, 8.0, 1946814.0] ESTIMATE"

PLAN FRAGMENT 3(F09)

Input Partition: HASH_PARTITIONED: 118: branch_id
OutPut Partition: UNPARTITIONED
OutPut Exchange Id: 15

PLAN FRAGMENT 4(F07)

Input Partition: RANDOM
OutPut Partition: HASH_PARTITIONED: 7: branch_id
OutPut Exchange Id: 12

11:OlapScanNode
" table: statement, rollup: statement"
preAggregation: on
" Predicates: [7: branch_id, BIGINT, true] = 74"
" partitionsRatio=1/1, tabletsRatio=2/2"
" tabletList=20069,20071"
" actualRows=782786, avgRowSize=16.0"
cardinality: 1393
column statistics:
" * id–>[381.0, 9.99999999999999E14, 0.0, 8.0, 1392.8576512455516] ESTIMATE"
" * branch_id–>[74.0, 74.0, 0.0, 8.0, 562.0] ESTIMATE"

PLAN FRAGMENT 5(F01)

Input Partition: RANDOM
OutPut Partition: HASH_PARTITIONED: 118: branch_id
OutPut Exchange Id: 10

9:Project
| output columns:
" | 90 ↔ [90: id, BIGINT, false]"
" | 118 ↔ [118: branch_id, BIGINT, true]"
| cardinality: 4402
| column statistics:
" | * id–>[6063.0, 3894394.0, 0.0, 8.0, 4401.895643395273] ESTIMATE"
" | * branch_id–>[74.0, 74.0, 0.0, 8.0, 548.0] ESTIMATE"
|
8:HASH JOIN
| join op: INNER JOIN (BROADCAST)
" | equal join conjunct: [92: branch_product_id, BIGINT, true] = [106: id, BIGINT, false]"
| build runtime filters:
" | - filter_id = 1, build_expr = (106: id), remote = false"
" | output columns: 90, 118"
| can local shuffle: false
| cardinality: 4402
| column statistics:
" | * id–>[6063.0, 3894394.0, 0.0, 8.0, 4401.895643395273] ESTIMATE"
" | * branch_client_id–>[1.0, 1676333.0, 0.0, 8.0, 2176.21897810219] ESTIMATE"
" | * id–>[1.0, 1676333.0, 0.0, 8.0, 2176.21897810219] ESTIMATE"
" | * branch_id–>[74.0, 74.0, 0.0, 8.0, 548.0] ESTIMATE"
|
|----7:EXCHANGE
| distribution type: BROADCAST
| cardinality: 3525
|
1:OlapScanNode
" table: product_agreement, rollup: product_agreement"
preAggregation: on
" partitionsRatio=1/1, tabletsRatio=2/2"
" tabletList=18393,18395"
" actualRows=2377227, avgRowSize=16.0"
cardinality: 2377227
probe runtime filters:
" - filter_id = 1, probe_expr = (92: branch_product_id)"
column statistics:
" * id–>[6063.0, 3894394.0, 0.0, 8.0, 2376417.0] ESTIMATE"
" * branch_product_id–>[5.0, 2403508.0, 0.0, 8.0, 1832221.0] ESTIMATE"

PLAN FRAGMENT 6(F02)

Input Partition: RANDOM
OutPut Partition: UNPARTITIONED
OutPut Exchange Id: 07

6:Project
| output columns:
" | 106 ↔ [106: id, BIGINT, false]"
" | 118 ↔ [118: branch_id, BIGINT, true]"
| cardinality: 3525
| column statistics:
" | * id–>[1.0, 2403508.0, 0.0, 8.0, 3524.8521552806287] ESTIMATE"
" | * branch_id–>[74.0, 74.0, 0.0, 8.0, 548.0] ESTIMATE"
|
5:HASH JOIN
| join op: INNER JOIN (BROADCAST)
" | equal join conjunct: [108: branch_client_id, BIGINT, true] = [117: id, BIGINT, false]"
| build runtime filters:
" | - filter_id = 0, build_expr = (117: id), remote = false"
" | output columns: 106, 118"
| can local shuffle: false
| cardinality: 3525
| column statistics:
" | * id–>[1.0, 2403508.0, 0.0, 8.0, 3524.8521552806287] ESTIMATE"
" | * branch_client_id–>[1.0, 1676333.0, 0.0, 8.0, 2176.21897810219] ESTIMATE"
" | * id–>[1.0, 1676333.0, 0.0, 8.0, 2176.21897810219] ESTIMATE"
" | * branch_id–>[74.0, 74.0, 0.0, 8.0, 548.0] ESTIMATE"
|
|----4:EXCHANGE
| distribution type: BROADCAST
| cardinality: 2176
|
2:OlapScanNode
" table: branch_product, rollup: branch_product"
preAggregation: on
" partitionsRatio=1/1, tabletsRatio=2/2"
" tabletList=18400,18402"
" actualRows=1909092, avgRowSize=16.0"
cardinality: 1909092
probe runtime filters:
" - filter_id = 0, probe_expr = (108: branch_client_id)"
column statistics:
" * id–>[1.0, 2403508.0, 0.0, 8.0, 1903583.0] ESTIMATE"
" * branch_client_id–>[1.0, 1676333.0, 0.0, 8.0, 1178660.0] ESTIMATE"

PLAN FRAGMENT 7(F03)

Input Partition: RANDOM
OutPut Partition: UNPARTITIONED
OutPut Exchange Id: 04

3:OlapScanNode
" table: branch_client, rollup: branch_client"
preAggregation: on
" Predicates: [118: branch_id, BIGINT, true] = 74"
" partitionsRatio=1/1, tabletsRatio=2/2"
" tabletList=18407,18409"
" actualRows=1192568, avgRowSize=16.0"
cardinality: 2176
probe runtime filters:
" - filter_id = 2, probe_expr = (118: branch_id)"
column statistics:
" * id–>[1.0, 1676333.0, 0.0, 8.0, 2176.21897810219] ESTIMATE"
" * branch_id–>[74.0, 74.0, 0.0, 8.0, 548.0] ESTIMATE"

Query 2: completes in two seconds (filter on cte first then do joins)

select
*
from(
with statement_filtered as
(select * from statement s where s.branch_id = 74)
select s.branch_id,
sum(si.amount) AS production_inc_vat,
SUM(si.amount_exclusive) AS production,
SUM(si.premium) AS premuim,
SUM(si.fees) AS fee,
SUM(si.commission) AS commission
FROM statement_filtered s
JOIN statement_item si ON si.statement_id = s.id
JOIN product_agreement pa ON pa.id = si.product_agreement_id
JOIN branch_product bp ON bp.id = pa.branch_product_id
join branch_client bc on bc.id = bp.branch_client_id and bc.branch_id = s.branch_id
GROUP BY s.branch_id)t1
;

Explain cost query two

PLAN FRAGMENT 0(F10)
Output Exprs:7: branch_id | 130: sum | 131: sum | 132: sum | 133: sum | 134: sum
Input Partition: UNPARTITIONED
RESULT SINK

20:EXCHANGE
cardinality: 562

PLAN FRAGMENT 1(F09)

Input Partition: HASH_PARTITIONED: 7: branch_id
OutPut Partition: UNPARTITIONED
OutPut Exchange Id: 20

19:AGGREGATE (merge finalize)
" | aggregate: sum[([130: sum, DECIMAL128(38,0), true]); args: DECIMAL64; result: DECIMAL128(38,0); args nullable: true; result nullable: true], sum[([131: sum, DECIMAL128(38,0), true]); args: DECIMAL64; result: DECIMAL128(38,0); args nullable: true; result nullable: true], sum[([132: sum, DECIMAL128(38,0), true]); args: DECIMAL64; result: DECIMAL128(38,0); args nullable: true; result nullable: true], sum[([133: sum, DECIMAL128(38,0), true]); args: DECIMAL64; result: DECIMAL128(38,0); args nullable: true; result nullable: true], sum[([134: sum, DECIMAL128(38,0), true]); args: DECIMAL64; result: DECIMAL128(38,0); args nullable: true; result nullable: true]"
" | group by: [7: branch_id, BIGINT, true]"
| cardinality: 562
| column statistics:
" | * branch_id–>[74.0, 74.0, 0.0, 8.0, 562.0] ESTIMATE"
" | * sum–>[-5.3390441E7, 5.3390441E7, 0.0, 8.0, 562.0] ESTIMATE"
" | * sum–>[-4.6426471E7, 4.6426471E7, 0.0, 8.0, 562.0] ESTIMATE"
" | * sum–>[-2.2353515E7, 9.017323693E9, 0.0, 8.0, 562.0] ESTIMATE"
" | * sum–>[-128772.0, 880898.0, 0.0, 8.0, 562.0] ESTIMATE"
" | * sum–>[-5.3390441E7, 5.3390441E7, 0.0, 8.0, 562.0] ESTIMATE"
|
18:EXCHANGE
distribution type: SHUFFLE
" partition exprs: [7: branch_id, BIGINT, true]"
cardinality: 562

PLAN FRAGMENT 2(F00)

Input Partition: RANDOM
OutPut Partition: HASH_PARTITIONED: 7: branch_id
OutPut Exchange Id: 18

17:AGGREGATE (update serialize)
| STREAMING
" | aggregate: sum[([35: amount, DECIMAL64(10,0), true]); args: DECIMAL64; result: DECIMAL128(38,0); args nullable: true; result nullable: true], sum[([50: amount_exclusive, DECIMAL64(10,0), true]); args: DECIMAL64; result: DECIMAL128(38,0); args nullable: true; result nullable: true], sum[([33: premium, DECIMAL64(10,0), true]); args: DECIMAL64; result: DECIMAL128(38,0); args nullable: true; result nullable: true], sum[([34: fees, DECIMAL64(10,0), true]); args: DECIMAL64; result: DECIMAL128(38,0); args nullable: true; result nullable: true], sum[([65: commission, DECIMAL64(10,0), true]); args: DECIMAL64; result: DECIMAL128(38,0); args nullable: true; result nullable: true]"
" | group by: [7: branch_id, BIGINT, true]"
| cardinality: 562
| column statistics:
" | * branch_id–>[74.0, 74.0, 0.0, 8.0, 562.0] ESTIMATE"
" | * sum–>[-5.3390441E7, 5.3390441E7, 0.0, 8.0, 562.0] ESTIMATE"
" | * sum–>[-4.6426471E7, 4.6426471E7, 0.0, 8.0, 562.0] ESTIMATE"
" | * sum–>[-2.2353515E7, 9.017323693E9, 0.0, 8.0, 562.0] ESTIMATE"
" | * sum–>[-128772.0, 880898.0, 0.0, 8.0, 562.0] ESTIMATE"
" | * sum–>[-5.3390441E7, 5.3390441E7, 0.0, 8.0, 562.0] ESTIMATE"
|
16:Project
| output columns:
" | 7 ↔ [7: branch_id, BIGINT, true]"
" | 33 ↔ [33: premium, DECIMAL64(10,0), true]"
" | 34 ↔ [34: fees, DECIMAL64(10,0), true]"
" | 35 ↔ [35: amount, DECIMAL64(10,0), true]"
" | 50 ↔ [50: amount_exclusive, DECIMAL64(10,0), true]"
" | 65 ↔ [65: commission, DECIMAL64(10,0), true]"
| cardinality: 93820
| column statistics:
" | * branch_id–>[74.0, 74.0, 0.0, 8.0, 562.0] ESTIMATE"
" | * premium–>[-2.2353515E7, 9.017323693E9, 0.0, 8.0, 104244.25272970865] ESTIMATE"
" | * fees–>[-128772.0, 880898.0, 0.0, 8.0, 8957.0] ESTIMATE"
" | * amount–>[-5.3390441E7, 5.3390441E7, 0.0, 8.0, 72604.0] ESTIMATE"
" | * amount_exclusive–>[-4.6426471E7, 4.6426471E7, 0.0, 8.0, 67754.0] ESTIMATE"
" | * commission–>[-5.3390441E7, 5.3390441E7, 0.0, 8.0, 72158.0] ESTIMATE"
|
15:HASH JOIN
| join op: INNER JOIN (BUCKET_SHUFFLE)
" | equal join conjunct: [117: id, BIGINT, false] = [108: branch_client_id, BIGINT, true]"
" | equal join conjunct: [118: branch_id, BIGINT, true] = [7: branch_id, BIGINT, true]"
| build runtime filters:
" | - filter_id = 3, build_expr = (108: branch_client_id), remote = false"
" | - filter_id = 4, build_expr = (7: branch_id), remote = false"
" | output columns: 7, 33, 34, 35, 50, 65"
| can local shuffle: true
| cardinality: 93820
| column statistics:
" | * id–>[381.0, 997937.0, 0.0, 8.0, 1392.8576512455516] ESTIMATE"
" | * branch_id–>[74.0, 74.0, 0.0, 8.0, 562.0] ESTIMATE"
" | * premium–>[-2.2353515E7, 9.017323693E9, 0.0, 8.0, 104244.25272970865] ESTIMATE"
" | * fees–>[-128772.0, 880898.0, 0.0, 8.0, 8957.0] ESTIMATE"
" | * amount–>[-5.3390441E7, 5.3390441E7, 0.0, 8.0, 72604.0] ESTIMATE"
" | * statement_id–>[381.0, 997937.0, 0.0, 8.0, 1392.8576512455516] ESTIMATE"
" | * amount_exclusive–>[-4.6426471E7, 4.6426471E7, 0.0, 8.0, 67754.0] ESTIMATE"
" | * commission–>[-5.3390441E7, 5.3390441E7, 0.0, 8.0, 72158.0] ESTIMATE"
" | * branch_id–>[1.0, 627.0, 0.0, 8.0, 548.0] ESTIMATE"
|
|----14:EXCHANGE
| distribution type: SHUFFLE
" | partition exprs: [108: branch_client_id, BIGINT, true]"
| cardinality: 104244
|
0:OlapScanNode
" table: branch_client, rollup: branch_client"
preAggregation: on
Predicates: 118: branch_id IS NOT NULL
" partitionsRatio=1/1, tabletsRatio=2/2"
" tabletList=18407,18409"
" actualRows=1192568, avgRowSize=16.0"
cardinality: 1192568
probe runtime filters:
" - filter_id = 3, probe_expr = (117: id)"
" - filter_id = 4, probe_expr = (118: branch_id)"
column statistics:
" * id–>[1.0, 1676333.0, 0.0, 8.0, 1192568.0] ESTIMATE"
" * branch_id–>[1.0, 627.0, 0.0, 8.0, 548.0] ESTIMATE"

PLAN FRAGMENT 3(F01)

Input Partition: RANDOM
OutPut Partition: BUCKET_SHUFFLE_HASH_PARTITIONED: 108: branch_client_id
OutPut Exchange Id: 14

13:Project
| output columns:
" | 7 ↔ [7: branch_id, BIGINT, true]"
" | 33 ↔ [33: premium, DECIMAL64(10,0), true]"
" | 34 ↔ [34: fees, DECIMAL64(10,0), true]"
" | 35 ↔ [35: amount, DECIMAL64(10,0), true]"
" | 50 ↔ [50: amount_exclusive, DECIMAL64(10,0), true]"
" | 65 ↔ [65: commission, DECIMAL64(10,0), true]"
" | 108 ↔ [108: branch_client_id, BIGINT, true]"
| cardinality: 104244
| column statistics:
" | * branch_id–>[74.0, 74.0, 0.0, 8.0, 562.0] ESTIMATE"
" | * premium–>[-2.2353515E7, 9.017323693E9, 0.0, 8.0, 103943.43873630867] ESTIMATE"
" | * fees–>[-128772.0, 880898.0, 0.0, 8.0, 8957.0] ESTIMATE"
" | * amount–>[-5.3390441E7, 5.3390441E7, 0.0, 8.0, 72604.0] ESTIMATE"
" | * amount_exclusive–>[-4.6426471E7, 4.6426471E7, 0.0, 8.0, 67754.0] ESTIMATE"
" | * commission–>[-5.3390441E7, 5.3390441E7, 0.0, 8.0, 72158.0] ESTIMATE"
" | * branch_client_id–>[1.0, 1676333.0, 0.0, 8.0, 104244.25272970865] ESTIMATE"
|
12:HASH JOIN
| join op: INNER JOIN (BUCKET_SHUFFLE)
" | equal join conjunct: [106: id, BIGINT, false] = [92: branch_product_id, BIGINT, true]"
| build runtime filters:
" | - filter_id = 2, build_expr = (92: branch_product_id), remote = false"
" | output columns: 7, 33, 34, 35, 50, 65, 108"
| can local shuffle: false
| cardinality: 104244
| column statistics:
" | * branch_id–>[74.0, 74.0, 0.0, 8.0, 562.0] ESTIMATE"
" | * premium–>[-2.2353515E7, 9.017323693E9, 0.0, 8.0, 103943.43873630867] ESTIMATE"
" | * fees–>[-128772.0, 880898.0, 0.0, 8.0, 8957.0] ESTIMATE"
" | * amount–>[-5.3390441E7, 5.3390441E7, 0.0, 8.0, 72604.0] ESTIMATE"
" | * amount_exclusive–>[-4.6426471E7, 4.6426471E7, 0.0, 8.0, 67754.0] ESTIMATE"
" | * commission–>[-5.3390441E7, 5.3390441E7, 0.0, 8.0, 72158.0] ESTIMATE"
" | * branch_product_id–>[5.0, 2403508.0, 0.0, 8.0, 103943.43873630867] ESTIMATE"
" | * id–>[5.0, 2403508.0, 0.0, 8.0, 103943.43873630867] ESTIMATE"
" | * branch_client_id–>[1.0, 1676333.0, 0.0, 8.0, 104244.25272970865] ESTIMATE"
|
|----11:EXCHANGE
| distribution type: SHUFFLE
" | partition exprs: [92: branch_product_id, BIGINT, true]"
| cardinality: 103943
|
1:OlapScanNode
" table: branch_product, rollup: branch_product"
preAggregation: on
Predicates: 108: branch_client_id IS NOT NULL
" partitionsRatio=1/1, tabletsRatio=2/2"
" tabletList=18400,18402"
" actualRows=1909092, avgRowSize=16.0"
cardinality: 1909092
probe runtime filters:
" - filter_id = 2, probe_expr = (106: id)"
column statistics:
" * id–>[1.0, 2403508.0, 0.0, 8.0, 1903583.0] ESTIMATE"
" * branch_client_id–>[1.0, 1676333.0, 0.0, 8.0, 1178660.0] ESTIMATE"

PLAN FRAGMENT 4(F02)

Input Partition: RANDOM
OutPut Partition: BUCKET_SHUFFLE_HASH_PARTITIONED: 92: branch_product_id
OutPut Exchange Id: 11

10:Project
| output columns:
" | 7 ↔ [7: branch_id, BIGINT, true]"
" | 33 ↔ [33: premium, DECIMAL64(10,0), true]"
" | 34 ↔ [34: fees, DECIMAL64(10,0), true]"
" | 35 ↔ [35: amount, DECIMAL64(10,0), true]"
" | 50 ↔ [50: amount_exclusive, DECIMAL64(10,0), true]"
" | 65 ↔ [65: commission, DECIMAL64(10,0), true]"
" | 92 ↔ [92: branch_product_id, BIGINT, true]"
| cardinality: 103943
| column statistics:
" | * branch_id–>[74.0, 74.0, 0.0, 8.0, 562.0] ESTIMATE"
" | * premium–>[-2.2353515E7, 9.017323693E9, 0.0, 8.0, 103943.43873630867] ESTIMATE"
" | * fees–>[-128772.0, 880898.0, 0.0, 8.0, 8957.0] ESTIMATE"
" | * amount–>[-5.3390441E7, 5.3390441E7, 0.0, 8.0, 72604.0] ESTIMATE"
" | * amount_exclusive–>[-4.6426471E7, 4.6426471E7, 0.0, 8.0, 67754.0] ESTIMATE"
" | * commission–>[-5.3390441E7, 5.3390441E7, 0.0, 8.0, 72158.0] ESTIMATE"
" | * branch_product_id–>[5.0, 2403508.0, 0.0, 8.0, 103943.43873630867] ESTIMATE"
|
9:HASH JOIN
| join op: INNER JOIN (BUCKET_SHUFFLE)
" | equal join conjunct: [90: id, BIGINT, false] = [66: product_agreement_id, BIGINT, true]"
| build runtime filters:
" | - filter_id = 1, build_expr = (66: product_agreement_id), remote = false"
" | output columns: 7, 33, 34, 35, 50, 65, 92"
| can local shuffle: false
| cardinality: 103943
| column statistics:
" | * branch_id–>[74.0, 74.0, 0.0, 8.0, 562.0] ESTIMATE"
" | * premium–>[-2.2353515E7, 9.017323693E9, 0.0, 8.0, 103943.43873630867] ESTIMATE"
" | * fees–>[-128772.0, 880898.0, 0.0, 8.0, 8957.0] ESTIMATE"
" | * amount–>[-5.3390441E7, 5.3390441E7, 0.0, 8.0, 72604.0] ESTIMATE"
" | * amount_exclusive–>[-4.6426471E7, 4.6426471E7, 0.0, 8.0, 67754.0] ESTIMATE"
" | * commission–>[-5.3390441E7, 5.3390441E7, 0.0, 8.0, 72158.0] ESTIMATE"
" | * product_agreement_id–>[6063.0, 3894394.0, 0.0, 8.0, 103943.43873630867] ESTIMATE"
" | * id–>[6063.0, 3894394.0, 0.0, 8.0, 103943.43873630867] ESTIMATE"
" | * branch_product_id–>[5.0, 2403508.0, 0.0, 8.0, 103943.43873630867] ESTIMATE"
|
|----8:EXCHANGE
| distribution type: SHUFFLE
" | partition exprs: [66: product_agreement_id, BIGINT, true]"
| cardinality: 107295
|
2:OlapScanNode
" table: product_agreement, rollup: product_agreement"
preAggregation: on
" partitionsRatio=1/1, tabletsRatio=2/2"
" tabletList=18393,18395"
" actualRows=2377227, avgRowSize=16.0"
cardinality: 2377227
probe runtime filters:
" - filter_id = 1, probe_expr = (90: id)"
column statistics:
" * id–>[6063.0, 3894394.0, 0.0, 8.0, 2376417.0] ESTIMATE"
" * branch_product_id–>[5.0, 2403508.0, 0.0, 8.0, 1832221.0] ESTIMATE"

PLAN FRAGMENT 5(F03)

Input Partition: RANDOM
OutPut Partition: BUCKET_SHUFFLE_HASH_PARTITIONED: 66: product_agreement_id
OutPut Exchange Id: 08

7:Project
| output columns:
" | 7 ↔ [7: branch_id, BIGINT, true]"
" | 33 ↔ [33: premium, DECIMAL64(10,0), true]"
" | 34 ↔ [34: fees, DECIMAL64(10,0), true]"
" | 35 ↔ [35: amount, DECIMAL64(10,0), true]"
" | 50 ↔ [50: amount_exclusive, DECIMAL64(10,0), true]"
" | 65 ↔ [65: commission, DECIMAL64(10,0), true]"
" | 66 ↔ [66: product_agreement_id, BIGINT, true]"
| cardinality: 107295
| column statistics:
" | * branch_id–>[74.0, 74.0, 0.0, 8.0, 562.0] ESTIMATE"
" | * premium–>[-2.2353515E7, 9.017323693E9, 0.0, 8.0, 107295.29803436546] ESTIMATE"
" | * fees–>[-128772.0, 880898.0, 0.0, 8.0, 8957.0] ESTIMATE"
" | * amount–>[-5.3390441E7, 5.3390441E7, 0.0, 8.0, 72604.0] ESTIMATE"
" | * amount_exclusive–>[-4.6426471E7, 4.6426471E7, 0.0, 8.0, 67754.0] ESTIMATE"
" | * commission–>[-5.3390441E7, 5.3390441E7, 0.0, 8.0, 72158.0] ESTIMATE"
" | * product_agreement_id–>[6063.0, 3894394.0, 0.03156966179777567, 8.0, 107295.29803436546] ESTIMATE"
|
6:HASH JOIN
| join op: INNER JOIN (BROADCAST)
" | equal join conjunct: [37: statement_id, BIGINT, true] = [1: id, BIGINT, false]"
| build runtime filters:
" | - filter_id = 0, build_expr = (1: id), remote = false"
" | output columns: 7, 33, 34, 35, 50, 65, 66"
| can local shuffle: false
| cardinality: 107295
| column statistics:
" | * id–>[381.0, 997937.0, 0.0, 8.0, 1392.8576512455516] ESTIMATE"
" | * branch_id–>[74.0, 74.0, 0.0, 8.0, 562.0] ESTIMATE"
" | * premium–>[-2.2353515E7, 9.017323693E9, 0.0, 8.0, 107295.29803436546] ESTIMATE"
" | * fees–>[-128772.0, 880898.0, 0.0, 8.0, 8957.0] ESTIMATE"
" | * amount–>[-5.3390441E7, 5.3390441E7, 0.0, 8.0, 72604.0] ESTIMATE"
" | * statement_id–>[381.0, 997937.0, 0.0, 8.0, 1392.8576512455516] ESTIMATE"
" | * amount_exclusive–>[-4.6426471E7, 4.6426471E7, 0.0, 8.0, 67754.0] ESTIMATE"
" | * commission–>[-5.3390441E7, 5.3390441E7, 0.0, 8.0, 72158.0] ESTIMATE"
" | * product_agreement_id–>[6063.0, 3894394.0, 0.03156966179777567, 8.0, 107295.29803436546] ESTIMATE"
|
|----5:EXCHANGE
| distribution type: BROADCAST
| cardinality: 1393
|
3:OlapScanNode
" table: statement_item, rollup: statement_item"
preAggregation: on
Predicates: 37: statement_id IS NOT NULL
" partitionsRatio=1/1, tabletsRatio=2/2"
" tabletList=18386,18388"
" actualRows=60602235, avgRowSize=56.0"
cardinality: 60602233
probe runtime filters:
" - filter_id = 0, probe_expr = (37: statement_id)"
column statistics:
" * premium–>[-2.2353515E7, 9.017323693E9, 0.0, 8.0, 180343.0] ESTIMATE"
" * fees–>[-128772.0, 880898.0, 0.0, 8.0, 8957.0] ESTIMATE"
" * amount–>[-5.3390441E7, 5.3390441E7, 0.0, 8.0, 72604.0] ESTIMATE"
" * statement_id–>[381.0, 997937.0, 0.0, 8.0, 786710.0] ESTIMATE"
" * amount_exclusive–>[-4.6426471E7, 4.6426471E7, 0.0, 8.0, 67754.0] ESTIMATE"
" * commission–>[-5.3390441E7, 5.3390441E7, 0.0, 8.0, 72158.0] ESTIMATE"
" * product_agreement_id–>[6063.0, 3894394.0, 0.03156966179777567, 8.0, 1946814.0] ESTIMATE"

PLAN FRAGMENT 6(F04)

Input Partition: RANDOM
OutPut Partition: UNPARTITIONED
OutPut Exchange Id: 05

4:OlapScanNode
" table: statement, rollup: statement"
preAggregation: on
" Predicates: 7: branch_id IS NOT NULL, [7: branch_id, BIGINT, true] = 74"
" partitionsRatio=1/1, tabletsRatio=2/2"
" tabletList=20069,20071"
" actualRows=782786, avgRowSize=16.0"
cardinality: 1393
column statistics:
" * id–>[381.0, 9.99999999999999E14, 0.0, 8.0, 1392.8576512455516] ESTIMATE"
" * branch_id–>[74.0, 74.0, 0.0, 8.0, 562.0] ESTIMATE"

How do i force starrocks to execute the query like the second example - why is it not doing the filter for the where clause at the start in the 1ste query?

Gerrit_van_Zyl1 · May 8, 2024, 7:19pm

This seems to be resolved when i changed the join order for the slow query to this

select
s.branch_id,
sum(si.amount) AS production_inc_vat,
SUM(si.amount_exclusive) AS production,
SUM(si.premium) AS premuim,
SUM(si.fees) AS fee,
SUM(si.commission) AS commission
from statement_item si
join product_agreement pa on si.product_agreement_id = pa.id
join branch_product bp on pa.branch_product_id = bp.id
join branch_client bc on bp.branch_client_id = bc.id
join statement s on si.statement_id = s.id
WHERE s.branch_id = 74
GROUP BY s.branch_id;

Any pointers on how we should order our joins for optimal performance?

Murphy_Wang · May 9, 2024, 5:03pm

did you do the “analyze table” to take advantages of the statistics, which may impact the join order

Gerrit_van_Zyl1 · May 9, 2024, 6:08pm

@Murphy_Wang again coming to my rescue! that made a massive difference to my query speed - Thanks!

D_K · June 30, 2024, 6:14pm

hi,
We are using starrocks 3.1 and while doing a performance test by running concurrent queries via jmeter, it was noticed that jdbc based connection perform very poorly compared to starrocks native package(alchemy engine). Is this expected? With JDBC , we frequently get Limit: 26853820047. Mem usage has exceed the limit of BE.

Topic		Replies	Views
Looking for Best Practices to Enhance StarRocks Query Performance Release Notes	0	38	July 27, 2024
Inserting data is very slow Working with Data	7	390	January 30, 2024
Technical Feature Advantage: JOIN Presentations, Articles and Webinars technical-advantage	0	162	January 27, 2024
Technical Feature Advantage: Query Rewrite Presentations, Articles and Webinars technical-advantage	0	81	January 30, 2024
StarRocks vs. ClickHouse: The Quest for Analytical Database Performance Presentations, Articles and Webinars	0	189	January 26, 2024

Query speed with where clause extremely slow

Explain Cost query 1

Query 2: completes in two seconds (filter on cte first then do joins)

Explain cost query two

Related Topics