Concept
CTE
Common Table Expression: a named subquery introduced with WITH that can be referenced like a table inside the same statement.
A CTE is defined with a WITH clause and behaves like a temporary, named result set scoped to the surrounding statement. CTEs make complex queries easier to read by giving each step a name, and they are the only way to write recursive queries in standard SQL via WITH RECURSIVE.
In modern PostgreSQL versions a non-recursive CTE is inlined into the main query by default, so the planner can rewrite and optimise across the boundary. Older versions treated every CTE as an optimisation fence; you can still force that with WITH ... AS MATERIALIZED ....
Use CTEs for clarity and recursion; avoid them as a tuning trick on modern PostgreSQL because the planner usually does the right thing on its own.