3 ms·

When I didn't know any better I wrote a finite state machine in it. In my opinion, try and use as much SQL as you can first. Functions are often an optimizati

When I didn't know any better I wrote a finite state machine in it. In my opinion, try and use as much SQL as you can first. Functions are often an optimization barrier for the postgresql query planner. The postgresql implementation of SQL has a bunch of neat functionality in it that replaces things that often happen in apps, like window functions, and the docs are so great. They are hard to debug. Try to keep their functionality small. My experience, overall, is positive. Consider using it if the amount of data transferred to the app and evaluated there is getting absurd. But try to creatively solve the problem with pure SQL first before moving to functions.

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 ago
  Optimization 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 ago
    These 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.

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 ago
Optimization 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 ago
  These 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.