Content-Disposition: inline
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset="Big5"
MIME-Version: 1.0
From: "CN" <cnliou9@fastmail.fm>
To: pgsql-sql@postgresql.org
Date: Mon, 24 Mar 2003 00:17:14 -0800
Subject: Which Approach Performs Better?
Message-Id: <20030324081714.9820E4F4B0@smtp.us2.messagingengine.com>

Hi!

I have a tree table:

CREATE TABLE tree (
CONSTRAINT fktree FOREIGN KEY (parent) REFERENCES tree (dept),
dept int primary key, --department
parent int
);

insert into tree values(1,1);
insert into tree values(2,1);
insert into tree values(3,2);

and a history table:

CREATE TABLE history (
CONSTRAINT fkhistory FOREIGN KEY (dept) REFERENCES tree (dept),
dept int primary key, --department
amount int
);

insert into history values(1,100);
insert into history values(2,200);
insert into history values(3,300);

My purpose is to retrieve the amount detail of department "1" and all
departments under it.
I have come out with 2 approaches:

APPROACH A:

--Returns TRUE if department $2 reports to department $1.
CREATE FUNCTION IsChild(TEXT,TEXT) RETURNS BOOLEAN AS '
DECLARE
  p ALIAS FOR $1; --parent
  c ALIAS FOR $2; --child
  vparent INT;
BEGIN
  IF c = p THEN RETURN TRUE; END IF;
  SELECT parent INTO vparent FROM tree WHERE dept=c;
  IF NOT FOUND THEN
    RETURN FALSE;
  ELSE
    RETURN IsChild(p,vparent);
  END IF;
END' LANGUAGE 'plpgsql' STABLE;

SELECT amount FROM history WHERE IsChild(1,dept);
---------------------
APPROACH B:

(Assuming the number of layers of this tree is predicatable. Let's take 3
layers as an example.)

SELECT amount FROM history WHERE
dept=1
OR dept IN (SELECT dept FROM tree WHERE parent=1)
OR dept IN (SELECT dept FROM tree WHERE parent IN (SELECT dept FROM tree
WHERE parent=1));

Both queries return

 amount
--------
    100
    200
    300
(3 rows)

APPROACH A is obviously easier to implement.
My question is which approach gets better performance when the number of
rows in history and the layers in tree grows?
I don't intend to apply "Joy's worm" tree algorism as it is too
complicate to me to understand.

Thank you in advance for any input!

Regards,

CN

-- 
http://www.fastmail.fm - A no graphics, no pop-ups email service