I have query with really bad performances on our columnar datastore tables whenever I add left join to it. The thing is that it’s very hard for me to understand how EXPLAIN and PROFILE work to troubleshoot this.
if i do the same query without the outer left join it works super fast.
so iam attaching two profiles: one with and other without the left join.
perhaps anyone could pour some light on this issue:
with outter join
query:
PROFILE: SELECT cce.player_id as player_id, cce.game_id as game_id, cce.bet_amount_converted as bet_amount_converted, cce.cont_amount_jackpot as cont_amount_jackpot, cce.base_currency as base_currency FROM contribution.cont_event cce LEFT OUTER JOIN winner.winner win ON cce.event_id = win.event_id AND win.status IN ('ACCEPTED', 'CANCEL') WHERE cce.game_id IN ('2k9d71558609680765') AND cce.action IN ('PLACE_BET', 'CANCEL_BET') AND cce.event_arrival_time BETWEEN '2019-12-22 00:00:00' AND '2019-12-23 15:00:00' AND cce.status = 'ACCEPTED'
profile output(with outter join):
query without the outter join:
PROFILE: SELECT cce.player_id as player_id, cce.game_id as game_id, cce.bet_amount_converted as bet_amount_converted, cce.cont_amount_jackpot as cont_amount_jackpot, cce.base_currency as base_currency FROM contribution.cont_event cce WHERE cce.game_id IN ('2k9d71558609680765') AND cce.action IN ('PLACE_BET', 'CANCEL_BET') AND cce.event_arrival_time BETWEEN '2019-12-22 00:00:00' AND '2019-12-23 15:00:00' AND cce.status = 'ACCEPTED'
the profile output:
without-outter-join-gist
winner table index:
operator_id, game_id, operator_game_code, player_id, create_time
