An ex colleague of mine has written many highly complex nested-view views using multiple ctes.
The end views are very fast.
It seems to me that his end views filter the initial ctes, making the rest of the ctes in the view much lighter.
However, when I try to replicate such views, the filter is only applied after all the ctes have run. This can result in huge amounts of reads and thus long execution times.
My thinking is that, because the filtered columns from the initial cte are used in the views’ end SELECT, that those should be filtered on at the start the query plan.