X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.225.181]) by svr1.postgresql.org (Postfix) with ESMTP id DAEE1D1C4C8 for ; Tue, 22 Jul 2003 17:56:53 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr5.postgresql.org [64.117.225.181]) (amavisd-new, port 10024) with ESMTP id 91536-05 for ; Tue, 22 Jul 2003 17:56:42 +0000 (GMT) Received: from fluor.cenes.de (pD9E75221.dip.t-dialin.net [217.231.82.33]) by svr1.postgresql.org (Postfix) with ESMTP id E56A1D1C941 for ; Tue, 22 Jul 2003 14:56:41 -0300 (ADT) Received: by fluor.cenes.de (Postfix, from userid 1000) id 2F8FF676AA; Tue, 22 Jul 2003 19:56:58 +0200 (CEST) Subject: Re: slow query From: Markus Bertheau To: postgres sql list Cc: Horst Schwarz In-Reply-To: <1058893618.1857.94.camel@fluor> References: <1058893618.1857.94.camel@fluor> Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Message-Id: <1058896617.1391.108.camel@fluor> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.3 Date: 22 Jul 2003 19:56:58 +0200 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/323 X-Sequence-Number: 14437 I'm trying to explain the bigger function a bit although it's only called in 2% of the cases. =D0=92 =D0=92=D1=82=D1=80, 22.07.2003, =D0=B2 19:07, Markus Bertheau =D0=BF= =D0=B8=D1=88=D0=B5=D1=82: > CREATE FUNCTION iGetNumOfBookedRes(integer, timestamp, timestamp) RETURNS= numeric AS ' > SELECT=20 > CASE WHEN (MAX(kumulierte) IS NULL) THEN 0 ELSE MAX(kumulierte) END > FROM > (SELECT=20 > (SELECT=20 > SUM(dynmenge)=20 > FROM -- (1) start > (SELECT > -- Ressource wird ausgeliehen > von, menge AS dynmenge > FROM auftragsressourcen JOIN belegungen USING (auftragsre= ssource_id) > WHERE ressource_id =3D $1 AND von > $2 AND von < $3 > UNION SELECT > -- Ressource wird zur=C3=BCckgegeben > bis AS von, -menge AS dynmenge > FROM auftragsressourcen JOIN belegungen USING (auftragsre= ssource_id) > WHERE ressource_id =3D $1 AND bis > $2 AND bis < $3 > UNION SELECT > -- Anfangsstand > $2, SUM(auftragsressourcen.menge) AS dynmenge > FROM auftragsressourcen JOIN belegungen USING (auftragsre= ssource_id) > WHERE ressource_id =3D $1 AND von <=3D $2 and bis > $2 > ) AS bel1=20 -- (1) end > WHERE bel1.von <=3D bel2.von > ) AS kumulierte > FROM -- (1) start > (SELECT > -- Ressource wird ausgeliehen > von, menge AS dynmenge > FROM auftragsressourcen JOIN belegungen USING (auftragsressou= rce_id) > WHERE ressource_id =3D $1 AND von > $2 AND von < $3 > UNION SELECT > -- Ressource wird zur=C3=BCckgegeben > bis AS von, -menge AS dynmenge > FROM auftragsressourcen JOIN belegungen USING (auftragsressou= rce_id) > WHERE ressource_id =3D $1 AND bis > $2 AND bis < $3 > UNION SELECT > -- Anfangsstand > $2, SUM(auftragsressourcen.menge) AS dynmenge > FROM auftragsressourcen JOIN belegungen USING (auftragsressou= rce_id) > WHERE ressource_id =3D $1 AND von <=3D $2 and bis > $2 > ) AS bel2 -- (1) end > ) AS belegte > ' LANGUAGE SQL; >=20 The query (1) with some values gives: von | dynmenge=20 ---------------------+---------- 2000-01-01 08:00:00 | 4 2000-06-06 00:00:00 | 6 2000-07-07 00:00:00 | -6 2000-07-16 08:00:00 | 7 2000-08-16 16:00:00 | -7 2000-09-01 00:00:00 | 8 2000-11-01 00:00:00 | -8 2001-01-01 00:00:00 | -4 What we actually do with the WHERE bel1.von <=3D bel2.von trick is calculate the sum over dynmenge over the first i rows where i \in (1..n) A positive dynmenge signifies that x items of a ressource are given away, a negative value that it is returned. This gives us von | dynmenge | given_away ---------------------+----------+------------ 2000-01-01 08:00:00 | 4 | 4 2000-06-06 00:00:00 | 6 | 10 2000-07-07 00:00:00 | -6 | 4 2000-07-16 08:00:00 | 7 | 11 2000-08-16 16:00:00 | -7 | 4 2000-09-01 00:00:00 | 8 | 12 2000-11-01 00:00:00 | -8 | 4 2001-01-01 00:00:00 | -4 | 0 num_of_ressources - max(given_away) gives us the number of ressources available in the given time interval. I hope this makes the function more clear. -- Markus Bertheau Cenes Data GmbH