4 ms·

PostgreSQL Common Table Expressions and LedgerSMB

30 points by einhverfr 14y ago

einhverfr 14y ago
I figured it might be good to add a couple of practical suggestions for folks using CTE's. The first is don't be afraid of them. They are extremely powerful and you can do a lot with them. They can also go a long ways towards making code a lot more readable. the second though is that they are optimization fences. Consequently you really want to do as much filtering in a CTE as you can. having a CTE that returns a very large result set is probably not a good idea. The smaller the amount of data returned the better. This is particularly important with recursive CTE's because you are doing one additional scan per level of recursion, and it is having to go back and populate the returned set after each one. If you are generating a tree with millions of nodes you better have a heck of a lot of RAM and appropriate tuning done in advance. It's much better though if you can just render the portion of the tree you are interested in.
- smiler 14y ago
  Why not use a better non-recursive method for creating your menu tree [eg modified preorder tree traversal], no recursion required, no complicated CTE required, just a simple select? Although it is slightly more expensive / complicated to update / insert, menu's seem to be the perfect tree for that as the chances of the tree changing that often are relatively slim.
  - einhverfr 14y ago
    The menu is rendered relatively rarely. Even if we were to go that route, I think the simple solution would be to take what goes into the CTE and materialize it after menu update. But it's not a lot of nodes and not a lot of depth. Why complicate your data storage model if you don't need to?
jacques_chester 14y ago
I've used CTEs in Oracle and used judiciously they save a great deal of faffing about.
- einhverfr 14y ago
  Actually the trial balance example came about because of addressing dependencies in join conditions that would have made it very difficult to address the same thing without it. I haven't tested it for large query sets but the idea is that usually you won't need to have a CTE covering more than about 14 months of data (assuming books are closed 2 months after fiscal end of year), or will only cover a small part of the data over a longer period. Of course for some businesses 14 months of data is still a lot of data for a CTE, maybe a couple hundred thousand rows, but given enough memory, PostgreSQL can be tuned for this.
  - jacques_chester 14y ago
    Yeah -- it's definitely a "pro tip" that you should cut down the CTE as much as possible. But a smart query planner can actually fold the CTE efficiently into the larger query plan and migrate where conditions up into the CTE and perform a union. This is why CTEs are better than temporary tables, for example.

30 points by einhverfr 14y ago

I figured it might be good to add a couple of practical suggestions for folks using CTE's. The first is don't be afraid of them. They are extremely powerful and you can do a lot with them. They can also go a long ways towards making code a lot more readable. the second though is that they are optimization fences. Consequently you really want to do as much filtering in a CTE as you can. having a CTE that returns a very large result set is probably not a good idea. The smaller the amount of data returned the better. This is particularly important with recursive CTE's because you are doing one additional scan per level of recursion, and it is having to go back and populate the returned set after each one. If you are generating a tree with millions of nodes you better have a heck of a lot of RAM and appropriate tuning done in advance. It's much better though if you can just render the portion of the tree you are interested in.

smiler 14y ago
Why not use a better non-recursive method for creating your menu tree [eg modified preorder tree traversal], no recursion required, no complicated CTE required, just a simple select? Although it is slightly more expensive / complicated to update / insert, menu's seem to be the perfect tree for that as the chances of the tree changing that often are relatively slim.
- einhverfr 14y ago
  The menu is rendered relatively rarely. Even if we were to go that route, I think the simple solution would be to take what goes into the CTE and materialize it after menu update. But it's not a lot of nodes and not a lot of depth. Why complicate your data storage model if you don't need to?

jacques_chester 14y ago

I've used CTEs in Oracle and used judiciously they save a great deal of faffing about.

einhverfr 14y ago
Actually the trial balance example came about because of addressing dependencies in join conditions that would have made it very difficult to address the same thing without it. I haven't tested it for large query sets but the idea is that usually you won't need to have a CTE covering more than about 14 months of data (assuming books are closed 2 months after fiscal end of year), or will only cover a small part of the data over a longer period. Of course for some businesses 14 months of data is still a lot of data for a CTE, maybe a couple hundred thousand rows, but given enough memory, PostgreSQL can be tuned for this.
- jacques_chester 14y ago
  Yeah -- it's definitely a "pro tip" that you should cut down the CTE as much as possible. But a smart query planner can actually fold the CTE efficiently into the larger query plan and migrate where conditions up into the CTE and perform a union. This is why CTEs are better than temporary tables, for example.