Re: recursive table performance (CTE)

2015-11-11 10:44 GMT+01:00 Dusan <fesz21(at)seznam(dot)cz>:
> Hi,
> I'm using table with parent_id to themselve and WITH RECURSIVE in SELECT on
> about 3thousands records.
> The "tree" of data is wide (each node has more children) but not deep
> (maximal depth of branch is 10 nodes).
>
> I'm planning to use same schema on much deeper but narrower tree (most of
> nodes will have only one child, only few nodes will have two or little bit
> more childs).
> It will represent points on map of line construction with many points
> (nodes). It would have thousands of nodes, there will be more independent
> trees (up to hundreds), some of them will be much smaller then others. Count
> of nodes in table will be about few hundreds of thousands.
>
> Alternatively I can divide line constructions to many sections (from cross
> of lines to other) and have it on separate table like this:
> CREATE TABLE sections (
> id_section SERIAL PRIMARY KEY
> );
>
> CREATE TABLE section_nodes (
> id_node SERIAL PRIMARY KEY,
> sections_id_section INTEGER REFERENCES sections (id_section),
> x INTEGER,
> y INTEGER,
> sortid INTEGER -- serial number of onde in one section
> );
>
> Solution with recursive is nicer and easier for administration (and
> SELECTing from it), but won't be problem with performance on so many
> recursion? Is there some limitations of recursive tables?
> Or is better solution the second one with seperated sections?
>
> Thanks for help and your opinion.
>
> Dusan
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Hello,

I don't get how your "section way" exactly works.

What about closure tables ?
http://karwin.blogspot.fr/2010/03/rendering-trees-with-closure-tables.html
The performances are good