> CTEs: First, yes they can be an optimisation boundary.
Note that this varies by database engine. They are an optimisation fence in postgres, they generally aren't in MS SQL Server, I'm not sure about Oracle or DB2 but I think they can optimise predicate application over CTE boundaries too.
Note that this varies by database engine. They are an optimisation fence in postgres, they generally aren't in MS SQL Server, I'm not sure about Oracle or DB2 but I think they can optimise predicate application over CTE boundaries too.