From: | Fran Fabrizio <ffabrizio(at)mmrd(dot)com> |
---|---|
To: | wsheldah(at)lexmark(dot)com |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: storing intermediate results from recursive plpgsql |
Date: | 2001-12-13 22:51:57 |
Message-ID: | 3C19310D.4255A801@mmrd.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
wsheldah(at)lexmark(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2001-12-13 23:06:44 | Re: timestamp('01/12/01') doesn't work in 7.2 beta4 |
Previous Message | wsheldah | 2001-12-13 22:37:14 | Re: storing intermediate results from recursive plpgsql |