Обсуждение: recursive table performance (CTE)
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 nodeswill 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 willbe more independent trees (up to hundreds), some of them will be much smaller then others. Count of nodes in table willbe 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 tablelike 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 performanceon 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
2015-11-11 10:44 GMT+01:00 Dusan <fesz21@seznam.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@postgresql.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
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