2 ms·

There's a good chapter on this in SQL Antipatterns, it covers four or five different approaches in depth. I always just use a parent_id and recursive cte - it'

• dspillett 5y ago
  Careful with CTEs in older postgres. Until the most recent versions they were an optimisation fence (predicate push-down would not pass through, meaning the CTE would often result in an index or table scan, potentially multiple times in the recursive case. This can be a huge problem over large data, though obviously in many circumstances with small enough data it is still efficient enough.
  • paulddraper 5y ago
    That concern doesn't really apply to recursive CTEs though. Or rather, recursive CTEs inherently require that you select only what you need. A little difference that the non-recursive case of "I'll just use a CTE for same functionality but better readability" which can deceptively bite you prior to PostgreSQL 12.