High CPU Usage on certain query when changing DB Compatibility Level We migrated from SQL 2008R2 to SQL 2016 a few years ago and have been running on Compatibility Level 100 since that point.
We are looking to make use of memory optimization introduced in SQL 2016 and that requires setting the compatibility level to 130.
We have had a few instances where a certain query will max out the CPU (that normally only takes a few seconds to run on Compatibility Lvl 100, but when we change it to Level 130 the CPU maxes out and does takes 2 minutes and 37 seconds to run  vs about 2 seconds on the old compatibility level)

I did pull the execution plan for each of these
CompatibilityLvl100 Plan
CompatibilityLvl130 Plan
I am not a DBA so I can see there are differences between the execution plans but I could not see any reason for it to max out the CPU and take almost 6000% longer to run the same query with the same indexes etc.
I also executed this query to get some more details about the running query, I have uploaded the results as a comparison to a Google Sheet here.
One thing i noticed is that the slow running query was using 192 worker threads, whereas the fast running query only used 32.  But I still couldn't figure out what was causing the high CPU usage.
Be glad of anyone's help or direction of something I may have missed!
On higher compatibility level SQL Server starts to use new cardinality estimator which leads to different execution plan. Sometimes this new plan may be less efficient but it doesn't mean that new cardinality estimator works worse. Usually it means you need to review your query and probably adjust your indexes or statistics because probably your original plan was far away from optimal too. You attached estimated execution plans to question description. To find out the root reason of the issue you need to compare actual plans.
You can use traceflag or hint to force SQL Server to use old cardinality estimator for your query: OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'))