From: | Nicolas Paris <niparisco(at)gmail(dot)com> |
---|---|
To: | Dusan <fesz21(at)seznam(dot)cz> |
Cc: | Forums postgresql <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: recursive table performance (CTE) |
Date: | 2015-11-11 21:27:13 |
Message-ID: | CA+ssMOQrK0S45t+w-CCYKEsQE7-EkBYSuQsp9o2aUyYdm=iaOg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2015-11-12 14:36:57 | Re: recursive table performance (CTE) |
Previous Message | Adrian Klaver | 2015-11-11 20:05:00 | Re: error connecting to the server: fatal :could not open file "base/12029/11801" permission denied |