SQL Server: INNER JOIN after UNION leads to slow Hash Match (Aggregate)

Here is a CTE that slows down the whole stored procedure:

select * 
from #finalResults
where intervalEnd is not null
union
select            
    two.startTime, 
    two.endTime,
    two.intervalEnd,
    one.barcodeID,
    one.id,
    one.pairId,
    one.bookingTypeID,
    one.cardID,
    one.factor,
    two.openIntervals,
    two.factorSumConcurrentJobs
from #finalResults as one
inner join #finalResults as two
    on  two.cardID = one.cardID
    and two.startTime > one.startTime
    and two.startTime < one.intervalEnd

The table #finalResults contains a little over 600K lines, the upper part of the UNION (where intervalEnd is not null) about 580K rows, the lower part with the joined #finalResults roughly 300K rows. However, this inner join estimates to end up with a whooping 100 mio. rows, which might be responsible for the long-running Hash Match here:

Now if I understand Hash Joins correctly, the smaller table should be hashed first and the larger table inserted, and if you guess the sizes wrong at first, you get performance penalties due to mid-process role reversal. Might this be responsible for the slowness?

I tried an explicit inner me开发者_如何转开发rge join and inner loop join in hopes of improving the row count estimate, but to no avail.

One other thing: the Eager Spool on the bottom right estimates 17K rows, ends up with 300K rows and performs almost half a million rebinds and rewrites. Is this normal?

Edit: The temp table #finalResults has an index on it:

create nonclustered index "finalResultsIDX_cardID_intervalEnd_startTime__REST"
on #finalresults( "cardID", "intervalEnd", "startTime" )
include( barcodeID, id, pairID, bookingTypeID, factor,
         openIntervals, factorSumConcurrentJobs );

Do I need to build a separate statistic on it as well?

I have experienced situations where UNION's made a query much slower than UNION ALL with a DISTINCT afterwards. So while I don't have an explanation for the bad query plan (statistics and indexes are okay?), I suggest that you try the following:

select distinct * from (
    select * 
    from #finalResults
    where intervalEnd is not null
    union all
    select            
        two.startTime, 
        two.endTime,
        two.intervalEnd,
        one.barcodeID,
        one.id,
        one.pairId,
        one.bookingTypeID,
        one.cardID,
        one.factor,
        two.openIntervals,
        two.factorSumConcurrentJobs
    from #finalResults as one
    inner join #finalResults as two
        on  two.cardID = one.cardID
        and two.startTime > one.startTime
        and two.startTime < one.intervalEnd
)

Perhaps it will improve if you create statistics for the cardID column.