Nested Loop has very low estimates due to skewed data
Normally I would use filtered statistics to improve estimations when I have a table with skewed data. But in this case that doesn't seem to work.


You should find the following filtered statistic helpful:

CREATE STATISTICS [stats id (field=varchar)]
ON dbo.MyTable (id)
WHERE field = 'varchar'
WITH FULLSCAN;


This gives the optimizer information about the distribution of id values that match field = 'varchar', giving a much better selectivity estimate for the join:



The execution plan above shows exactly correct estimates with the filtered statistic, leading the optimizer to choose a hash join (for cost reasons).

This distribution information is much more important than the exact method used by the estimator to match the join histograms (fine or coarse alignment), or even the general assumptions (e.g. simple join, base containment).

If you can't do that, your options are broadly as outlined in answer to your previous question Sort spills to tempdb due to varchar(max). My preference would probably be an intermediate temporary table.