Optimize query that accoridng to plan produces over 500 billions rows - Actual result is only 400k rows

Question

I'm trying to improve DB performance. I'v noticed a query that according to the plan it produces a lot of rows but i know for fact there are no more than 400k rows in the table after that insert. Why there is such a big difference between actuall plan and the query result? Query plan is here https://www.brentozar.com/pastetheplan/?id=rJWX327pB

Answer 1

I cannot understand why the query is so complex.

Query text taken from published plan:

with Object1 as
(
select Object2.Column1 as Column2 
from Schema1.Object3 as Object2 
left outer join Schema1.Object4 as Object5 
    on Object2.Column1 = Object5.Column1
where Object5.Column1 is null
) 
insert into Schema1.Object4 
select * from Schema1.Object3 
where Column1 in ( select Column2 
                   from Object1 )

Let's think.

What do you do in CTE? you select records from Schema1.Object3 which have no according record in Schema1.Object4.

What records are inserted in main query? those which were selected in CTE.

So CTE seems to be excess, and the query may be simplified to

insert into Schema1.Object4 
select Schema1.Object3.* 
from Schema1.Object3 
left outer join Schema1.Object4
    on Schema1.Object3.Column1 = Schema1.Object4.Column1
where Schema1.Object4.Column1 is null

or

insert into Schema1.Object4 
select Schema1.Object3.* 
from Schema1.Object3 
where not exists ( select 1
                   from Schema1.Object4
                   where Schema1.Object3.Column1 = Schema1.Object4.Column1 )

Please check - does I'm right?