Where does Sql Server get the cardinality estimates on Table3.Index3 and Table2.Index5
I can post the output of dbcc show_statistics – but they are not even close to the estimates on those two tables.
In the simplest case, the two inputs to the join have unfiltered histograms provided by statistics on base tables. For joins above the leaf level of the plan, or where filters are applied to the base tables, the input histograms have been modified by the intervening operations.
Where the base tables have filters applied, simple containment is assumed for the join selectivity estimation. The base histograms are first scaled down by the selectivity of the base table predicate(s), then the histograms are aligned to estimate join selectivity.
SQL Server 2014 uses base containment instead of simple containment. The selectivities of the base table predicates and join are calculated separately and simply multiplied together. SQL Server 2014 uses a coarse alignment of histogram steps for join selectivity estimation based on minimum and maximum values rather than aligning each step using linear interpolation.
Estimating join selectivity is a difficult problem. Try deriving a good estimate yourself from the raw statistical data.
Note: I'm talking about join selectivity so much because the question asked about estimation for the inner side of correlated nested loops joins.