client_account_via_portfolio_mv · explain

← cluster insights objects client_account_via_portfolio_mv explain

Overview Objects Graph History

materialized view · insights.client_account_via_portfolio_mv profiled over 5s

Job is idle — throughput ~0; structure shown.

Stateful hash join (4 state tables) — consider a temporal join for dimension lookups

Heat tree Operator graph

44 operators

Materialize · insights.client_account_via_portfolio_mv

0% idle 2 actors

Project

— — 2 actors

Merge

— — 2 actors

Exchange

0% idle 0 actors

SyncLogStore · LeftAnti · account_to_portfolios_dm.account_id = accounts_to_clients_d…

— — 2 actors

HashJoin · LeftAnti · account_to_portfolios_dm.account_id = accounts_to_clients_d… Stateful hash join (4 state tables) — consider a temporal join for dimension lookups

0% idle 2 actors

Merge

— — 2 actors

Exchange

0% idle 0 actors

Project · accounts_to_clients_dm

— — 2 actors

Filter · accounts_to_clients_dm

0% idle 2 actors

StreamScan · accounts_to_clients_dm

0% idle 2 actors

BatchPlan

— — 2 actors

Merge

— — 2 actors

Merge

— — 2 actors

Exchange

0% idle 0 actors

SyncLogStore · Inner · account_to_portfolios_dm.account_id = open_accounts_mv.acco…

— — 2 actors

HashJoin · Inner · account_to_portfolios_dm.account_id = open_accounts_mv.acco… Stateful hash join (4 state tables) — consider a temporal join for dimension lookups

0% idle 2 actors

Merge

— — 2 actors

Exchange

0% idle 0 actors

StreamScan · open_accounts_mv

0% idle 2 actors

BatchPlan

— — 2 actors

Merge

— — 2 actors

Merge

— — 2 actors

Exchange

0% idle 0 actors

Project · (IsNull(Case((IsNull(account_to_portfolios_dm.effective_end…

— — 2 actors

Filter · (IsNull(Case((IsNull(account_to_portfolios_dm.effective_end…

0% idle 2 actors

Merge

— — 2 actors

Exchange

0% idle 0 actors

SyncLogStore · Inner · account_to_portfolios_dm.portfolio_id = clients_portfolios_…

— — 2 actors

HashJoin · Inner · account_to_portfolios_dm.portfolio_id = clients_portfolios_… Stateful hash join (4 state tables) — consider a temporal join for dimension lookups

0% idle 2 actors

Merge

— — 2 actors

Exchange

0% idle 0 actors

Project · clients_portfolios_dm

— — 2 actors

Filter · clients_portfolios_dm

0% idle 2 actors

StreamScan · clients_portfolios_dm

0% idle 2 actors

BatchPlan

— — 2 actors

Merge

— — 2 actors

Merge

— — 2 actors

Exchange

0% idle 0 actors

Project · account_to_portfolios_dm

— — 2 actors

Filter · account_to_portfolios_dm

0% idle 2 actors

StreamScan · account_to_portfolios_dm

0% idle 2 actors

BatchPlan

— — 2 actors

Merge

— — 2 actors

Heat = the operator's output-buffer backpressure over the sampling window. Click a node to fold its subtree.

Streaming operator plan from EXPLAIN ANALYZE. Node heat = backpressure. Drag to pan, scroll to zoom.

Fragments (DESCRIBE FRAGMENTS) — click to expand

Fragment 13355 (Actor 99300,99301)
StreamMaterialize { columns: [client_id, account_id, effective_start_date, effective_end_date, type, account_to_portfolios_dm.portfolio_id(hidden), account_to_portfolios_dm.effective_start_date(hidden), clients_portfolios_dm.effective_start_date(hidden)], stream_key: [account_id, account_to_portfolios_dm.portfolio_id, account_to_portfolios_dm.effective_start_date, client_id, clients_portfolios_dm.effective_start_date], pk_columns: [account_id, account_to_portfolios_dm.portfolio_id, account_to_portfolios_dm.effective_start_date, client_id, clients_portfolios_dm.effective_start_date], pk_conflict: NoCheck }
├── output: [ clients_portfolios_dm.client_id, account_to_portfolios_dm.account_id, $expr1, $expr2, 'all':Varchar, account_to_portfolios_dm.portfolio_id, account_to_portfolios_dm.effective_start_date, clients_portfolios_dm.effective_start_date ]
├── stream key: [ account_to_portfolios_dm.account_id, account_to_portfolios_dm.portfolio_id, account_to_portfolios_dm.effective_start_date, clients_portfolios_dm.client_id, clients_portfolios_dm.effective_start_date ]
└── StreamProject { exprs: [clients_portfolios_dm.client_id, account_to_portfolios_dm.account_id, Greatest(account_to_portfolios_dm.effective_start_date, clients_portfolios_dm.effective_start_date) as $expr1, Case((IsNull(account_to_portfolios_dm.effective_end_date) AND IsNull(clients_portfolios_dm.effective_end_date)), null:Date, IsNull(account_to_portfolios_dm.effective_end_date), clients_portfolios_dm.effective_end_date, IsNull(clients_portfolios_dm.effective_end_date), account_to_portfolios_dm.effective_end_date, Least(account_to_portfolios_dm.effective_end_date, clients_portfolios_dm.effective_end_date)) as $expr2, 'all':Varchar, account_to_portfolios_dm.portfolio_id, account_to_portfolios_dm.effective_start_date, clients_portfolios_dm.effective_start_date] }
    ├── output: [ clients_portfolios_dm.client_id, account_to_portfolios_dm.account_id, $expr1, $expr2, 'all':Varchar, account_to_portfolios_dm.portfolio_id, account_to_portfolios_dm.effective_start_date, clients_portfolios_dm.effective_start_date ]
    ├── stream key: [ account_to_portfolios_dm.account_id, account_to_portfolios_dm.portfolio_id, account_to_portfolios_dm.effective_start_date, clients_portfolios_dm.client_id, clients_portfolios_dm.effective_start_date ]
    └── MergeExecutor { output: [ account_to_portfolios_dm.account_id, account_to_portfolios_dm.effective_start_date, account_to_portfolios_dm.effective_end_date, clients_portfolios_dm.client_id, clients_portfolios_dm.effective_start_date, clients_portfolios_dm.effective_end_date, account_to_portfolios_dm.portfolio_id ], stream key: [ account_to_portfolios_dm.account_id, account_to_portfolios_dm.portfolio_id, account_to_portfolios_dm.effective_start_date, clients_portfolios_dm.client_id, clients_portfolios_dm.effective_start_date ] }

Fragment 13356 (Actor 99299,99298)
StreamSyncLogStore { output: [ account_to_portfolios_dm.account_id, account_to_portfolios_dm.effective_start_date, account_to_portfolios_dm.effective_end_date, clients_portfolios_dm.client_id, clients_portfolios_dm.effective_start_date, clients_portfolios_dm.effective_end_date, account_to_portfolios_dm.portfolio_id ], stream key: [ account_to_portfolios_dm.account_id, account_to_portfolios_dm.portfolio_id, account_to_portfolios_dm.effective_start_date, clients_portfolios_dm.client_id, clients_portfolios_dm.effective_start_date ] }
└── StreamHashJoin { type: LeftAnti, predicate: account_to_portfolios_dm.account_id = accounts_to_clients_dm.account_id AND clients_portfolios_dm.client_id = accounts_to_clients_dm.client_id } { output: [ account_to_portfolios_dm.account_id, account_to_portfolios_dm.effective_start_date, account_to_portfolios_dm.effective_end_date, clients_portfolios_dm.client_id, clients_portfolios_dm.effective_start_date, clients_portfolios_dm.effective_end_date, account_to_portfolios_dm.portfolio_id ], stream key: [ account_to_portfolios_dm.account_id, account_to_portfolios_dm.portfolio_id, account_to_portfolios_dm.effective_start_date, clients_portfolios_dm.client_id, clients_portfolios_dm.effective_start_date ] }
    ├── MergeExecutor { output: [ account_to_portfolios_dm.account_id, account_to_portfolios_dm.effective_start_date, account_to_portfolios_dm.effective_end_date, clients_portfolios_dm.client_id, clients_portfolios_dm.effective_start_date, clients_portfolios_dm.effective_end_date, account_to_portfolios_dm.portfolio_id, open_accounts_mv.account_id ], stream key: [ account_to_portfolios_dm.account_id, account_to_portfolios_dm.portfolio_id, account_to_portfolios_dm.effective_start_date, clients_portfolios_dm.client_id, clients_portfolios_dm.effective_start_date ] }
    └── MergeExecutor { output: [ accounts_to_clients_dm.account_id, accounts_to_clients_dm.client_id, accounts_to_clients_dm.effective_start_date ], stream key: [ accounts_to_clients_dm.account_id, accounts_to_clients_dm.client_id, accounts_to_clients_dm.effective_start_date ] }

Fragment 13357 (Actor 99307,99306)
StreamSyncLogStore { output: [ account_to_portfolios_dm.account_id, account_to_portfolios_dm.effective_start_date, account_to_portfolios_dm.effective_end_date, clients_portfolios_dm.client_id, clients_portfolios_dm.effective_start_date, clients_portfolios_dm.effective_end_date, account_to_portfolios_dm.portfolio_id, open_accounts_mv.account_id ], stream key: [ account_to_portfolios_dm.account_id, account_to_portfolios_dm.portfolio_id, account_to_portfolios_dm.effective_start_date, clients_portfolios_dm.client_id, clients_portfolios_dm.effective_start_date ] }
└── StreamHashJoin { type: Inner, predicate: account_to_portfolios_dm.account_id = open_accounts_mv.account_id } { output: [ account_to_portfolios_dm.account_id, account_to_portfolios_dm.effective_start_date, account_to_portfolios_dm.effective_end_date, clients_portfolios_dm.client_id, clients_portfolios_dm.effective_start_date, clients_portfolios_dm.effective_end_date, account_to_portfolios_dm.portfolio_id, open_accounts_mv.account_id ], stream key: [ account_to_portfolios_dm.account_id, account_to_portfolios_dm.portfolio_id, account_to_portfolios_dm.effective_start_date, clients_portfolios_dm.client_id, clients_portfolios_dm.effective_start_date ] }
    ├── MergeExecutor { output: [ account_to_portfolios_dm.account_id, account_to_portfolios_dm.effective_start_date, account_to_portfolios_dm.effective_end_date, clients_portfolios_dm.client_id, clients_portfolios_dm.effective_start_date, clients_portfolios_dm.effective_end_date, account_to_portfolios_dm.portfolio_id, clients_portfolios_dm.portfolio_id ], stream key: [ account_to_portfolios_dm.account_id, account_to_portfolios_dm.portfolio_id, account_to_portfolios_dm.effective_start_date, clients_portfolios_dm.client_id, clients_portfolios_dm.effective_start_date ] }
    └── MergeExecutor { output: [ open_accounts_mv.account_id ], stream key: [ open_accounts_mv.account_id ] }

Fragment 13358 (Actor 99311,99310)
StreamProject { exprs: [account_to_portfolios_dm.account_id, account_to_portfolios_dm.effective_start_date, account_to_portfolios_dm.effective_end_date, clients_portfolios_dm.client_id, clients_portfolios_dm.effective_start_date, clients_portfolios_dm.effective_end_date, account_to_portfolios_dm.portfolio_id, clients_portfolios_dm.portfolio_id] } { output: [ account_to_portfolios_dm.account_id, account_to_portfolios_dm.effective_start_date, account_to_portfolios_dm.effective_end_date, clients_portfolios_dm.client_id, clients_portfolios_dm.effective_start_date, clients_portfolios_dm.effective_end_date, account_to_portfolios_dm.portfolio_id, clients_portfolios_dm.portfolio_id ], stream key: [ account_to_portfolios_dm.account_id, account_to_portfolios_dm.portfolio_id, account_to_portfolios_dm.effective_start_date, clients_portfolios_dm.client_id, clients_portfolios_dm.effective_start_date ] }
└── StreamFilter { predicate: (IsNull(Case((IsNull(account_to_portfolios_dm.effective_end_date) AND IsNull(clients_portfolios_dm.effective_end_date)), null:Date, IsNull(account_to_portfolios_dm.effective_end_date), clients_portfolios_dm.effective_end_date, IsNull(clients_portfolios_dm.effective_end_date), account_to_portfolios_dm.effective_end_date, Least(account_to_portfolios_dm.effective_end_date, clients_portfolios_dm.effective_end_date))) OR (Greatest(account_to_portfolios_dm.effective_start_date, clients_portfolios_dm.effective_start_date) < Case((IsNull(account_to_portfolios_dm.effective_end_date) AND IsNull(clients_portfolios_dm.effective_end_date)), null:Date, IsNull(account_to_portfolios_dm.effective_end_date), clients_portfolios_dm.effective_end_date, IsNull(clients_portfolios_dm.effective_end_date), account_to_portfolios_dm.effective_end_date, Least(account_to_portfolios_dm.effective_end_date, clients_portfolios_dm.effective_end_date)))) }
    ├── output: [ account_to_portfolios_dm.account_id, account_to_portfolios_dm.portfolio_id, account_to_portfolios_dm.effective_start_date, account_to_portfolios_dm.effective_end_date, clients_portfolios_dm.client_id, clients_portfolios_dm.portfolio_id, clients_portfolios_dm.effective_start_date, clients_portfolios_dm.effective_end_date ]
    ├── stream key: [ account_to_portfolios_dm.account_id, account_to_portfolios_dm.portfolio_id, account_to_portfolios_dm.effective_start_date, clients_portfolios_dm.client_id, clients_portfolios_dm.effective_start_date ]
    └── MergeExecutor { output: [ account_to_portfolios_dm.account_id, account_to_portfolios_dm.portfolio_id, account_to_portfolios_dm.effective_start_date, account_to_portfolios_dm.effective_end_date, clients_portfolios_dm.client_id, clients_portfolios_dm.portfolio_id, clients_portfolios_dm.effective_start_date, clients_portfolios_dm.effective_end_date ], stream key: [ account_to_portfolios_dm.account_id, account_to_portfolios_dm.portfolio_id, account_to_portfolios_dm.effective_start_date, clients_portfolios_dm.client_id, clients_portfolios_dm.effective_start_date ] }

Fragment 13359 (Actor 99309,99308)
StreamSyncLogStore { output: [ account_to_portfolios_dm.account_id, account_to_portfolios_dm.portfolio_id, account_to_portfolios_dm.effective_start_date, account_to_portfolios_dm.effective_end_date, clients_portfolios_dm.client_id, clients_portfolios_dm.portfolio_id, clients_portfolios_dm.effective_start_date, clients_portfolios_dm.effective_end_date ], stream key: [ account_to_portfolios_dm.account_id, account_to_portfolios_dm.portfolio_id, account_to_portfolios_dm.effective_start_date, clients_portfolios_dm.client_id, clients_portfolios_dm.effective_start_date ] }
└── StreamHashJoin { type: Inner, predicate: account_to_portfolios_dm.portfolio_id = clients_portfolios_dm.portfolio_id } { output: [ account_to_portfolios_dm.account_id, account_to_portfolios_dm.portfolio_id, account_to_portfolios_dm.effective_start_date, account_to_portfolios_dm.effective_end_date, clients_portfolios_dm.client_id, clients_portfolios_dm.portfolio_id, clients_portfolios_dm.effective_start_date, clients_portfolios_dm.effective_end_date ], stream key: [ account_to_portfolios_dm.account_id, account_to_portfolios_dm.portfolio_id, account_to_portfolios_dm.effective_start_date, clients_portfolios_dm.client_id, clients_portfolios_dm.effective_start_date ] }
    ├── MergeExecutor { output: [ account_to_portfolios_dm.account_id, account_to_portfolios_dm.portfolio_id, account_to_portfolios_dm.effective_start_date, account_to_portfolios_dm.effective_end_date ], stream key: [ account_to_portfolios_dm.account_id, account_to_portfolios_dm.portfolio_id, account_to_portfolios_dm.effective_start_date ] }
    └── MergeExecutor { output: [ clients_portfolios_dm.client_id, clients_portfolios_dm.portfolio_id, clients_portfolios_dm.effective_start_date, clients_portfolios_dm.effective_end_date ], stream key: [ clients_portfolios_dm.client_id, clients_portfolios_dm.portfolio_id, clients_portfolios_dm.effective_start_date ] }

Fragment 13360 (Actor 99286,99287)
StreamProject { exprs: [account_to_portfolios_dm.account_id, account_to_portfolios_dm.portfolio_id, account_to_portfolios_dm.effective_start_date, account_to_portfolios_dm.effective_end_date] } { output: [ account_to_portfolios_dm.account_id, account_to_portfolios_dm.portfolio_id, account_to_portfolios_dm.effective_start_date, account_to_portfolios_dm.effective_end_date ], stream key: [ account_to_portfolios_dm.account_id, account_to_portfolios_dm.portfolio_id, account_to_portfolios_dm.effective_start_date ] }
└── StreamFilter { predicate: IsNull(account_to_portfolios_dm.disabled_at) AND (IsNull(account_to_portfolios_dm.effective_end_date) OR (account_to_portfolios_dm.effective_end_date > account_to_portfolios_dm.effective_start_date)) } { output: [ account_to_portfolios_dm.account_id, account_to_portfolios_dm.portfolio_id, account_to_portfolios_dm.effective_start_date, account_to_portfolios_dm.effective_end_date, account_to_portfolios_dm.disabled_at ], stream key: [ account_to_portfolios_dm.account_id, account_to_portfolios_dm.portfolio_id, account_to_portfolios_dm.effective_start_date ] }
    └── StreamTableScan { table: account_to_portfolios_dm, columns: [account_id, portfolio_id, effective_start_date, effective_end_date, disabled_at] } { output: [ account_to_portfolios_dm.account_id, account_to_portfolios_dm.portfolio_id, account_to_portfolios_dm.effective_start_date, account_to_portfolios_dm.effective_end_date, account_to_portfolios_dm.disabled_at ], stream key: [ account_to_portfolios_dm.account_id, account_to_portfolios_dm.portfolio_id, account_to_portfolios_dm.effective_start_date ] }
        ├── Upstream { output: [ account_id, portfolio_id, effective_start_date, effective_end_date, disabled_at ], stream key: [] }
        └── BatchPlanNode { output: [ account_id, portfolio_id, effective_start_date, effective_end_date, disabled_at ], stream key: [] }

Fragment 13361 (Actor 99313,99312)
StreamProject { exprs: [clients_portfolios_dm.client_id, clients_portfolios_dm.portfolio_id, clients_portfolios_dm.effective_start_date, clients_portfolios_dm.effective_end_date] } { output: [ clients_portfolios_dm.client_id, clients_portfolios_dm.portfolio_id, clients_portfolios_dm.effective_start_date, clients_portfolios_dm.effective_end_date ], stream key: [ clients_portfolios_dm.client_id, clients_portfolios_dm.portfolio_id, clients_portfolios_dm.effective_start_date ] }
└── StreamFilter { predicate: IsNull(clients_portfolios_dm.disabled_at) } { output: [ clients_portfolios_dm.client_id, clients_portfolios_dm.portfolio_id, clients_portfolios_dm.effective_start_date, clients_portfolios_dm.effective_end_date, clients_portfolios_dm.disabled_at ], stream key: [ clients_portfolios_dm.client_id, clients_portfolios_dm.portfolio_id, clients_portfolios_dm.effective_start_date ] }
    └── StreamTableScan { table: clients_portfolios_dm, columns: [client_id, portfolio_id, effective_start_date, effective_end_date, disabled_at] } { output: [ clients_portfolios_dm.client_id, clients_portfolios_dm.portfolio_id, clients_portfolios_dm.effective_start_date, clients_portfolios_dm.effective_end_date, clients_portfolios_dm.disabled_at ], stream key: [ clients_portfolios_dm.client_id, clients_portfolios_dm.portfolio_id, clients_portfolios_dm.effective_start_date ] }
        ├── Upstream { output: [ client_id, portfolio_id, effective_start_date, effective_end_date, disabled_at ], stream key: [] }
        └── BatchPlanNode { output: [ client_id, portfolio_id, effective_start_date, effective_end_date, disabled_at ], stream key: [] }

Fragment 13362 (Actor 99315,99314)
StreamTableScan { table: open_accounts_mv, columns: [account_id] } { output: [ open_accounts_mv.account_id ], stream key: [ open_accounts_mv.account_id ] }
├── Upstream { output: [ account_id ], stream key: [] }
└── BatchPlanNode { output: [ account_id ], stream key: [] }

Fragment 13363 (Actor 99316,99317)
StreamProject { exprs: [accounts_to_clients_dm.account_id, accounts_to_clients_dm.client_id, accounts_to_clients_dm.effective_start_date] } { output: [ accounts_to_clients_dm.account_id, accounts_to_clients_dm.client_id, accounts_to_clients_dm.effective_start_date ], stream key: [ accounts_to_clients_dm.account_id, accounts_to_clients_dm.client_id, accounts_to_clients_dm.effective_start_date ] }
└── StreamFilter { predicate: IsNull(accounts_to_clients_dm.disabled_at) AND (IsNull(accounts_to_clients_dm.effective_end_date) OR (accounts_to_clients_dm.effective_end_date > accounts_to_clients_dm.effective_start_date)) } { output: [ accounts_to_clients_dm.account_id, accounts_to_clients_dm.client_id, accounts_to_clients_dm.effective_start_date, accounts_to_clients_dm.disabled_at, accounts_to_clients_dm.effective_end_date ], stream key: [ accounts_to_clients_dm.account_id, accounts_to_clients_dm.client_id, accounts_to_clients_dm.effective_start_date ] }
    └── StreamTableScan { table: accounts_to_clients_dm, columns: [account_id, client_id, effective_start_date, disabled_at, effective_end_date] } { output: [ accounts_to_clients_dm.account_id, accounts_to_clients_dm.client_id, accounts_to_clients_dm.effective_start_date, accounts_to_clients_dm.disabled_at, accounts_to_clients_dm.effective_end_date ], stream key: [ accounts_to_clients_dm.account_id, accounts_to_clients_dm.client_id, accounts_to_clients_dm.effective_start_date ] }
        ├── Upstream { output: [ account_id, client_id, effective_start_date, disabled_at, effective_end_date ], stream key: [] }
        └── BatchPlanNode { output: [ account_id, client_id, effective_start_date, disabled_at, effective_end_date ], stream key: [] }

RWM Console cluster: risingwave-adib.adib-rw.svc.cluster.local