recursive table performance (CTE)

From: Dusan <fesz21(at)seznam(dot)cz>
To: pgsql-general(at)postgresql(dot)org
Subject: recursive table performance (CTE)
Date: 2015-11-11 09:44:16
Message-ID: 56430DF0.3080404@seznam.cz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Albe Laurenz 2015-11-11 09:45:28 Re: can postgres run well on NFS mounted partitions?
Previous Message Albe Laurenz 2015-11-11 08:52:59 Re: attempting to install tds_fw-master on redhat