On Wed, Nov 11, 2015 at 3:44 AM, Dusan <fesz21@seznam.cz> wrote:
> 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.
Well, WITH RECURSIVE is not really recursive -- it's iterative. So if
you have maximum depth of 10 you get 10 iterations. Back in the day,
we'd keep two temp tables and ping pong the data back between them
(deleting one side or the other each time) -- the new approach is
faster and cleaner. A fully recursive approach which can be done via
nested set returning function calls will typically be even slower.
IMHO, the only way to do better is via materialized path approaches
where you store all the path components of a tree in a column. This
gives best case performance for extractions (a single sweep of the
btree) at the cost of bigger data and much more expensive move
operations.
merlin