Randall Lucas <rlucas@xxxxxxxxxxx> writes: > I am holding hierarchical data in a table and often need to calculate the "root" of a hierarchy. Initially, > a recursive plpgsql function worked just fine. But performance started to lag when I got to scale. > > So, I added a functional index. > > create table example (id serial primary key, stuff text, parent_id int); > create index example_root_idx on example (get_root_id(id)); > > (get_root_id(id) pulls an example row and recurses onto parent_id until it hits a root) > > This works fine for speeding up access to the existing data, but breaks for inserting new rows because > get_root_id(id) can't find the new row. It looks like the index is getting calculated, and the function > called, before the row becomes visible to the function. Change your get_root_id(id) into get_root_id(example), after this you can start searching for root not from current id but from it parent_id. Parent is already exist in table. If parent_id is null - it is root and get_root_id(example) do not need scan table and will return example.id from it input argument. -- Sergey Burladyan -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
