From: | "CN" <cnliou9(at)fastmail(dot)fm> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Which Approach Performs Better? |
Date: | 2003-03-24 08:17:14 |
Message-ID: | 20030324081714.9820E4F4B0@smtp.us2.messagingengine.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Victor Yegorov | 2003-03-24 08:20:14 | Re: Seeking help with a query.... |
Previous Message | Rajesh Kumar Mallah | 2003-03-24 04:43:37 | Re: FUNCTIONS PROBLEM |