

PostgreSQL Common Table Expressions and LedgerSMB - einhverfr
http://ledgersmbdev.blogspot.com/2012/07/ctes-and-ledgersmb.html

======
einhverfr
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
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
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
I've used CTEs in Oracle and used judiciously they save a great deal of
faffing about.

~~~
einhverfr
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
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.

