storing intermediate results from recursive plpgsql

From: Fran Fabrizio <ffabrizio(at)mmrd(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: storing intermediate results from recursive plpgsql
Date: 2001-12-13 21:43:34
Message-ID: 3C192106.D2651F1E@mmrd.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


Hello,

I've got a plpgsql function that is recursive. Basically, it traverses
a table that represents a tree, which in turn represents parent-child
relationships. So, I have a function, get_descendants. For each pass,
it gets the children of some id. Then it recurses and looks for the
children of all of those children, etc...So, along the way, I'm building
a list of ids that represent the whole family.

For lack of a better idea, I'm storing the id's into a table on each
pass. So, if I recurse three levels, I'm doing three inserts. When the
recursion exits, I simply select the entire table and then I delete all
rows from it. The performance hit I take is unacceptable, something
like .02 - .03 seconds per insert, and it's adding up due to the amount
of times I have to run this function. The end result is that the web
page that displays this data takes many seconds to run.

Is there some sort of data structure in plpgsql (an array) that I can
use instead of the hack of inserting into a table on each pass and
selecting back out at the end? I have to find a way to optimize this
process further.

Thanks,
Fran

Responses

Browse pgsql-general by date

  From Date Subject
Next Message GB Clark II 2001-12-13 22:02:24 Re: How to increase shared mem for PostgreSQL on FreeBSD
Previous Message Vince Vielhaber 2001-12-13 21:10:17 Re: Can I call unix/linux commands within plsql?