3 ms·
Functions don't have to be optimization barries. If you mark them as stable or immutable I believe postgres will inline them whenever they are used such that th
by arisb 8y ago
Functions don't have to be optimization barries. If you mark them as stable or immutable I believe postgres will inline them whenever they are used such that they can be optimized.
- davidgould 8y agoOptimization barrier means something a bit different, it means that the optimizer can't push down filters or change the join order, or flatten subqueries into joins. That is, it splits the query into parts that are separately considered for rewriting and plan selection. In postgres, CTEs are optimization barriers, that is, the implementation plans and executes the CTE separately and the main query consumes the result of the CTE. Pure SQL functions can sometimes be inlined, that is, made part of the containing query, but functions in the other languages cannot. In particular, if a function runs queries itself those happen in a new execution context (portal) and don't share anything with the function callers plan.
- tomlock 8y agoThese are both great observations. VOLATILE/STABLE/IMMUTABLE is definitely a choice worth considering and making in an educated way when writing a function. Also, using WITH to create CTEs makes SQL easy to read, but sometimes will slow down a query considerably. I have a feeling that new releases will make attempts to mitigate this issue in some cases.