Re:Hierarchical Structures in PostgreSQL (2020)
by zozbot234 on Saturday June 26, 2021 @03:18AM
(#27639864)
> They're the database equivalent of pointer-chasing in code.
That's the general case, but more specific CTE queries can be optimized, e.g. by adding database indexes. Recent versions of Postgres have greatly improved wrt. not making CTE's overly inefficient.
Re:Hierarchical Structures in PostgreSQL (2020)
by barrkel on Saturday June 26, 2021 @05:41PM
(#27646163)
I said recursive CTEs. CTEs being an optimization barrier is a different issue - and often desirable with Postgres with its lack of optimization hints. Hence with not / materialized etc.
That's the general case, but more specific CTE queries can be optimized, e.g. by adding database indexes. Recent versions of Postgres have greatly improved wrt. not making CTE's overly inefficient.