I have an RDS cluster consisting of One read replica and one Primary.

recently I have made a change in my code such that a join that used to be a "left_join" would now be an "inner_join" this caused a degradation in performance and queries that used to run fine with "left_join" are now taking much longer to finish.

I started to see a spike in IPC:MessageQueueSend wait events in the AWS console.

Additional information that might be helpful:

1. there are multiple indexes on that table if that helps and I am using them as part of the join
2. I am running a create table as select query (this is part of a dbt transformation)
3. I have a compound primary key

Why would this change cause that big of a difference ?

there are multiple CTEs that are being used in this query and they are not materialized and this might be the reason it does not show row estimate

edit with detailed explain analyze explain (analyze, verbose, buffers, format text)

inner join :

left join: