Re: Defer a functional index calculation?

Randall Lucas <rlucas@tercent.com> 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