Is this horrendously low row estimate causing a slow insert?

Question

reference: https://www.brentozar.com/pastetheplan/?id=Bklc3fGNt

I have a clustered index on both #LargerRange and #SmallerRange. These indexes contain 5 key columns: Name, InOrOut, LocCode, VisitID, and AccountNumber [in that order]. Most of the estimates look decent except this Merge Join operator that's between two Compute Scalars. This particular operator returns ~53,000% of the estimated rows. The operators that follow that (Concat and the Table Insert) also have poor estimates but not quite as bad. Still, this insert takes roughly a minute or two on it's own, and this isn't dealing with nearly as many rows as it could in production.

I'm a little stumped as to why that particular operator is wildly underestimating the number of rows. Is that first bad estimate causing the following estimates to be way off as well? Is that slowing the insert down (this is a select into; an insert into a table + with(tablock) with a clustered index has performed just as poorly)? Why is that estimate so bad, and how can I improve it? I suspect that it has something to do with the ordering of the key columns in the clustered index but I feel like I'm just missing something because I'm basically stumped. Any advice is much appreciated. Thank you.

Answer 1

Doesn't look like it. The time stats for that operator don't show it running for long,

<RunTimeCountersPerThread Thread="0" ActualRows="2302947" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="5417" ActualCPUms="5406" />

and the bulk of the time is spent inserting 2,302,947 rows into the temp table.

Do you expect that many rows? The query in the plan is truncated, so we can only see the first two queries in the UNION ALL.