Re: storing intermediate results from recursive plpgsql

wsheldah@lexmark.com wrote:

> It sounds like each batch of children gets operated on three different times:
> once when you select the children of a particular id, again when you insert them
> into the temporary table, and a third time when you select from the temp table.

It's true that I select them and then insert them on each pass.  At the very end
after the recursion is when I select back out from the temp table, so that just
happens once.

>
> The first and easiest optimization would be to truncate the temp table instead
> of deleting from it, if you're not doing that alread. That won't solve the real
> problem though.

Indeed the delete also takes .02 seconds, but I only delete once whereas I insert
once per pass so optimizing the insert would be more helpful (though I will try
this one too).

> I have basically the same design. What I'm doing is issuing the selects from
> perl, and storing the results in a perl hash structure. I only have to select
> each batch of id's once this way. I'm sure this makes up for whatever I lose by
> not doing it in a postgres function. Seems to work well. In some cases I'm using
> Storable to cache the resulting perl hash in a Postgresql bytea field so I don't
> always rebuild the entire tree from scratch.

I really need to have it happen in the database so that I can do things like

select current_status from status where entity_id IN (select
get_descendants(12345));

Since get_descendants has so many applications/uses distributed across many client
apps, I really need it centralized.  Unless you mean plperl, which could be an
option but I was skeptical that moving from plpgsql to plperl would make anything
faster.

> You might also google for Joe Celko and his nested set model. It's a bit
> complex, but looks like it could be a win, especially if you have a very high
> ratio of selects to inserts/updates. Other people have tried other variations of

This is in fact my long term solution.  I bought his book last week and have begun
digesting this approach.  I was looking for something I can deploy in the meantime
to hold us over for a few weeks. =)

Thanks Wes, very helpful feedback!

-Fran