Query with LEFT OUTER JOIN returns bad performance result

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):

outter-join-gist

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

looking at the Visual profile i can spot slowlyness on some steps. just Iam not sure what that means.

Consider using a bloom filter for the join table.

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
(select event_id, status from winner.winner where status IN (‘ACCEPTED’, ‘CANCEL’) ) win ON cce.event_id = win.event_id 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’