Thread: Defer a functional index calculation?

Hi folks,

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.

Is there a way to set a functional index to be deferred (like for FKs), or calculated on an AFTER INSERT ON basis (like with triggers), or similar?

If not, my backup plan is to precalculate get_root_id via a trigger and store it in a column, but that kind of smells (even worse than my current schema, I know).

Thank you,

Randall

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

On Fri, Mar 20, 2009 at 05:37:33PM -0700, Randall Lucas wrote:
> 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)

I don't think you can do that; are you lying in the function's
definition that it's "immutable"?  As far as I know, and a quick check
of the manual[1] seems to confirm it, all functions used in functional
indexes must be immutable and the execution of an immutable function
can't depend on any data in the database.  If you're creating this
function as "immutable" then this is lying to the database and it's
guaranteed to go wrong at some point (as you seem to be finding out).

What about having some trigger to "cache" the entry's root "parent_id"
in another column?

--
  Sam  http://samason.me.uk/

 [1] http://www.postgresql.org/docs/current/static/sql-createindex.html

On Mon, Mar 23, 2009 at 4:46 AM, Sam Mason <sam@samason.me.uk> wrote: