Re: recursing down a tree

On 12 Jul 2002, --CELKO-- wrote:

> I am writing a separate book on "Trees in SQL" which will cover
> several different models; I hope to be done by the end of the year. I
> also hope to win the lottery.

Are you *the* Joe Celko? "I'm not worthy! I'm not worthy!" :-)
Though I hope you'll let us know when your book comes out.

> Updating is not the problem people think it is. The nodes are in one
> table and the structure is in another. The Tree table has (node_id,
> lft, rgt) in its rows and those the rows are very short; a lot of them
> fit into main storage at once.

Unfortunately, this is not such a great assumption for postgres,
unless you otherwise have longish rows. The row overhead is over
40 bytes, including the row pointers in the page (giving you about
150 rows per page, when all is said and done). So with smallish
rows anyway, and depending on the application, and yada yada yada,
you might be better off not using a separate table.

cjs
--
Curt Sampson <cjs(at)cynic(dot)net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC