Why can't unused columns and tables be ignored in a recursive CTE

Question

Usually, SQL Server can optimize away any unused columns in the execution plan, and unused joined tables are not queried. But as soon as a recursive CTE comes into play, apparently all columns and joined tables are queried, even if not necessary. Why is that? Can I help SQL Server to ignore unused columns/tables here?

Example:

;WITH cte AS
(
    SELECT cn.CatalogNodeId,
           cn.ParentId,
           cn.CatalogNodeType,
           (SELECT Name FROM dbo.LocalizedCatalogNodes WHERE CatalogNodeId = cn.CatalogNodeId) AS Name
    FROM dbo.CatalogNodes AS cn
    WHERE cn.ParentId IS NULL
    UNION ALL
    SELECT cn.CatalogNodeId,
           cn.ParentId,
           cn.CatalogNodeType,
           (SELECT Name FROM dbo.LocalizedCatalogNodes WHERE CatalogNodeId = cn.CatalogNodeId) AS Name
    FROM dbo.CatalogNodes AS cn
    JOIN cte ON cte.CatalogNodeId = cn.ParentId
)
SELECT CatalogNodeId FROM cte

This is a simple recursive CTE resolving a hierarchical parent-child structure, adding a localized name to each node.

Even though not necessary, the execution plan shows that column CatalogNodeType is retrieved and dbo.LocalizedCatalogNodes is joined for each iteration.

Now comment out the UNION ALL part making it a non-recursive CTE, and the execution plan suddenly consists of just two steps, not containing column CatalogNodeType or table dbo.LocalizedCatalogNodes.

Answer 1

But as soon as a recursive CTE comes into play, apparently all columns and joined tables are queried, even if not necessary. Why is that?

1. Because recursive CTEs have a minimal initial implementation that has never been improved on (as of SQL Server 2022).
2. Recursion isn't a relational concept. Most of the optimizer's abilities are founded on relational equivalences. It's usual bag of tricks mostly can't be applied within your CTE, especially in the recursive part where the semantics can be unusual at times.

As a result, the entire area of the plan representing the recursive CTE is a hard-coded iterative translation of recursion with a stack spool serving as the, well, stack.

There are very few optimizations applied to this area of the plan, limited to eliminating the recursive part if it is provably empty (leaving only the anchor), or introducing another spool after the whole thing in case it is referenced and reused many times in the calling query.

Can I help SQL Server to ignore unused columns/tables here?

Not while continuing to use the recursive CTE as written, no. The ability to do what you want is simply not available.

In some ways, the current implementation reflects what you might write using a recursive multi-statement table-valued function. Clearly, the table variable used by the function requires a fixed structure, which is populated in full regardless of the requirements of the caller of the function. A recursive CTE does not have the same nesting level limit of 32 as a recursive function call though.

In this case, you could usefully move the repeated subquery outside the CTE, either keeping it as a correlated subquery or expressing it as an outer join. This resolves one part of the question but doesn't really address the spirit of it (as I interpret it anyway).

---

As a side note, SQL Graph reuses and builds on several basic features of recursive CTEs in the parser and query processor, including the initial translation from recursive syntax to iteration. SQL Graph has seen more than one round of investment and uses a different logical and physical implementation, which is more amenable to relational manipulations.

Your example, when represented as a graph in SQL Server, does not require recursion but in other queries where recursion is needed, it is implemented very differently. Graph plans (recursive or otherwise) are much more amenable to the range of simplifications available to 'normal' SQL queries, including unnecessary projection removal as requested here.

Answer 2

It's just a matter of order of operations, which is dependent on what the query optimizer thinks is going to be the most efficient execution plan. Yes, we can obviously see that in the end, the final SELECT list only consists of the CatalogNodeId column, and therefore the other columns of the CTE don't matter, so Microsoft probably could've implemented recursive CTEs to be a little bit better with their available execution plans in this regard. But even easier it is for the developer to recognize the lack of necessity for those columns in the recursive CTE, and therefore can be removed from the query, which is generally best practice anyway.

Answer 3

For more complex examples with nested CTEs, even without recursion, it might take a not insignificant amount of CPU time to work out what it needed. This might be one of those cases where the potential saving for some query patterns isn't worth potentially adding a few microseconds to many more that are used more often, so it is deliberately left for the coder to optimise.

The query planner's job is not actually too produce the best plan. It's job is to produce a good enough plan in as many cases as practical (not even a good enough plan in all cases), very quickly. It just so happens that it manages produce the best plan when presented with most simple patterns, and sometimes a surprising number of more complex ones.