SQL Server Actual Plan. Incorrect row estimates While practicing in tuning queries on StackOverflow database I got stuck on the following one:

SELECT TOP 50 Id AS [User Link],
(
    SELECT COUNT(*)
    FROM Posts
    WHERE PostTypeId = 1 AND 
          LastEditorUserId = Users.Id AND 
          OwnerUserId != Users.Id
) AS QuestionEdits,
(
    SELECT COUNT(*)
    FROM Posts
    WHERE PostTypeId = 2 AND 
          LastEditorUserId = Users.Id AND 
          OwnerUserId != Users.Id
) AS AnswerEdits,
(
    SELECT COUNT(*)
    FROM Posts
    WHERE LastEditorUserId = Users.Id AND 
          OwnerUserId != Users.Id
) AS TotalEdits
FROM Users
ORDER BY TotalEdits DESC;


The plan is here.

Execution stats:

(50 row(s) affected)
Table 'Posts'. Scan count 43217677, logical reads 172988050, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Users'. Scan count 5, logical reads 12692, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 701344 ms,  elapsed time = 192167 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.


I have three questions here:


What does the operator 'Constant scan' refer to?
Why row estimates can be incorrect in the following blocks:


a.



b.



What to do to make estimates more accurate? What index/technique to consider to improve the query performance?


Any hint will be deeply appreciated.
The question is tough to answer because the query does not match the plan provided, and the query used to generate the plan is somewhat meaningless:

SELECT TOP 50 Id AS [User Link],
(
 SELECT top 1 1
 FROM Posts
 WHERE PostTypeId = 1 AND 
    LastEditorUserId = Users.Id AND 
    OwnerUserId != Users.Id
) AS QuestionEdits,
(
 SELECT top 1 1
 FROM Posts
 WHERE PostTypeId = 2 AND 
    LastEditorUserId = Users.Id AND 
    OwnerUserId != Users.Id
) AS AnswerEdits,
(
 SELECT top 1 1
 FROM Posts
 WHERE LastEditorUserId = Users.Id AND 
    OwnerUserId != Users.Id
) AS TotalEdits
FROM Users
ORDER BY TotalEdits DESC


Nevertheless:


  
  What does the operator 'Constant scan' refer to?
  


A Constant Scan reads from an in-memory table of constant values. It may have one or more rows, and zero or more columns. Yes, a Constant Scan may have zero columns; it is often introduced by the optimizer simply as a source of (empty) rows, to which columns may be added by later operators (typically Compute Scalars).

To a large extent, Constant Scans are often purely architectural artifacts necessary for the correct operation of the query, or as part of an optimization.

When Constant Scans and Compute Scalars sit underneath a Merge Interval, this is a sure sign that these elements were introduced post-optimization to compute and collapse multiple logical intervals into a single interval suitable for driving an Index Seek on that range.

In this particular case, the Constant Scans represent the expansion of OwnerUserId &lt;&gt; Users.Id into:


OwnerUserId &lt; Users.Id; OR
OwnerUserId &gt; Users.Id


The post-optimization rewrite into the Merge Interval subtree is triggered by this x &lt; y OR x &gt; y predicate. The machinery tests the ranges for overlaps and collapses the resulting "merged interval" into start and end points that can be used with an index seek.

The following minimal example shows SQL Server rewriting the predicate in this way so an index seek can be generated:

CREATE TABLE #U (u integer NOT NULL);
CREATE TABLE #P (u integer NOT NULL PRIMARY KEY);

INSERT #U VALUES (1);
INSERT #P VALUES (0), (2);

SELECT 
    U.u,
    (
        SELECT TOP (1) 1
        FROM #P AS P
            WITH (FORCESEEK)
        WHERE P.u &lt;&gt; U.u
    )
FROM #U AS U;

DROP TABLE #U, #P




The resulting Clustered Index Seek there has the properties:



The end result is scanning the index forward until the end point (excluding the target value) then restarting the scan just after the target value to the end of the index. [Expr1011] and [Expr1012] are the computed start and end points of the excluded range.

You can read more about this in my article Dynamic Seeks and Hidden Implicit Conversions.


  
  Why row estimates can be incorrect in the following blocks
  


SSMS shows estimates on the inner side of a nested loops join per iteration whereas the actual row counts are the total over all iterations. This is confusing to many people, and the result of a questionable design decision.


  
    Posted by Microsoft on 10/14/2009 at 2:47 PM
    The difference between estimated and actual that you refer to is not really a bug in the optimizer; it's a questionable design decision in the way we present information in showplan. This table is on the inner side of a loop join, and in that position the numbers we report for actual and expected rows don't mean the same thing: The actual row count includes all exections, while the estimated row count is per execution.
  


You can get a more natural comparison by viewing the plan in Sentry One Plan Explorer. It does the per-iteration calculations for you, so you can compare estimates and actuals more directly. For example, this is a view of one of the Merge Interval subtrees viewed in Plan Explorer:




  
  What to do to make estimates more accurate? What index/technique to consider to improve the query performance?
  


The difference in showplan display accounts for the first question. David's answer has addressed rewriting the query for better performance (and meaningful results).