From: "Matthew Nuzum" <cobalt@bearfruit.org>
To: <pgsql-sql@postgresql.org>,
	"'Pgsql-Performance'" <pgsql-performance@postgresql.org>
Cc: "'Matthew Nuzum'" <matt@followers.net>
Subject: recursive srf
Date: Wed, 28 May 2003 17:30:01 -0400
Organization: Bearfruit.org
Message-ID: <002601c32560$4c8468e0$a322fea9@mattspc>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Importance: Normal

Working on my first set returning function... So far the examples from
http://techdocs.postgresql.org/guides/SetReturningFunctions have worked well
for me...

I'd like to see what kind of performance I get from a particularly slow
piece of code by replacing it with a recursive srf (right now, I do the
recursion in php).

So, here's my working example, I haven't bench marked it yet, but if someone
would look at it and tell me if there's any improvements that can be made,
I'd appreciate it.  My first impression is that it's fast, because it
appeared to have returned instantaneously.  I really don't understand the
"explain analyze" output, but I'm including it as well.

I'd love to get some feedback on this (did I say that already?).

Imagine this:
CREATE TYPE nav_list AS (id int8, accountid varchar(12),=20
		...snip... , parent int8, subfolders int8);

subfolders is the count() of records that have their parent set to this
record's id.  I want to take a list of something like this:
home
  - item 1
  - item 2
     - sub item 1
  - item 3
and return it so that it comes out in this order
home
item1
item2
sub item 1
item 3

create or replace function nav_srf(varchar(12), int8) returns setof nav_list
as '
DECLARE=20
	r nav_list%rowtype;
	depth int8;
	last_id int8;
	records RECORD;
BEGIN
	FOR r IN SELECT * FROM navigation WHERE accountid =3D $1 AND parent =3D
$2 ORDER BY dsply_order LOOP
		depth :=3D r.subfolders;
		last_id :=3D r.id;
		RETURN NEXT r;
		IF depth > 0 THEN
			FOR records IN SELECT * FROM nav_srf($1, last_id)
LOOOP
				RETURN NEXT records;
			END LOOP;
		END IF;
	END LOOP;
	RETURN;
END
' LANGUAGE 'plpgsql';


# EXPLAIN ANALYZE SELECT * FROM nav_srf('GOTDNS000000', 0);
QUERY PLAN
Function Scan on nav_srf  (cost=3D0.00..12.50 rows=3D1000 width=3D134) (act=
ual
time=3D85.78..86.19 rows=3D22 loops=3D1)
Total runtime: 86.37 msec
(2 rows)

I then ran it again a moment later and got:
# EXPLAIN ANALYZE SELECT * FROM nav_srf('GOTDNS000000', 0);
QUERY PLAN
Function Scan on nav_srf  (cost=3D0.00..12.50 rows=3D1000 width=3D134) (act=
ual
time=3D23.54..23.97 rows=3D22 loops=3D1)
Total runtime: 24.15 msec
(2 rows)

BTW, this started out as a question about how to do it, but in the process
of thinking my question out, the answer came to me.  ;-)

Matthew Nuzum
www.bearfruit.org
cobalt@bearfruit.org