From: Matthew Nuzum <cobalt@bearfruit.org>
To: pgsql-sql@postgresql.org
To: 'Pgsql-Performance' <pgsql-performance@postgresql.org>
Cc: 'Matthew Nuzum' <matt@followers.net>
Subject: recursive srf
Date: Wed, 28 May 2003 17:30:01 -0400
Message-ID: <002601c32560$4c8468e0$a322fea9@mattspc> (raw)
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),
...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
r nav_list%rowtype;
depth int8;
last_id int8;
records RECORD;
BEGIN
FOR r IN SELECT * FROM navigation WHERE accountid = $1 AND parent =
$2 ORDER BY dsply_order LOOP
depth := r.subfolders;
last_id := 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=0.00..12.50 rows=1000 width=134) (actual
time=85.78..86.19 rows=22 loops=1)
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=0.00..12.50 rows=1000 width=134) (actual
time=23.54..23.97 rows=22 loops=1)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: cobalt@bearfruit.org, pgsql-performance@postgresql.org, matt@followers.net
Subject: Re: recursive srf
In-Reply-To: <002601c32560$4c8468e0$a322fea9@mattspc>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox