From: Markus Bertheau <twanger@bluetwanger.de>
To: postgres sql list <pgsql-sql@postgresql.org>
Cc: Horst Schwarz <schwarz@bab24.de>
Subject: Re: slow query
Date: 22 Jul 2003 19:56:58 +0200
Message-ID: <1058896617.1391.108.camel@fluor> (raw)
In-Reply-To: <1058893618.1857.94.camel@fluor>
References: <1058893618.1857.94.camel@fluor>
I'm trying to explain the bigger function a bit although it's only
called in 2% of the cases.
В Втр, 22.07.2003, в 19:07, Markus Bertheau пишет:
> CREATE FUNCTION iGetNumOfBookedRes(integer, timestamp, timestamp) RETURNS numeric AS '
> SELECT
> CASE WHEN (MAX(kumulierte) IS NULL) THEN 0 ELSE MAX(kumulierte) END
> FROM
> (SELECT
> (SELECT
> SUM(dynmenge)
> FROM
-- (1) start
> (SELECT
> -- Ressource wird ausgeliehen
> von, menge AS dynmenge
> FROM auftragsressourcen JOIN belegungen USING (auftragsressource_id)
> WHERE ressource_id = $1 AND von > $2 AND von < $3
> UNION SELECT
> -- Ressource wird zurückgegeben
> bis AS von, -menge AS dynmenge
> FROM auftragsressourcen JOIN belegungen USING (auftragsressource_id)
> WHERE ressource_id = $1 AND bis > $2 AND bis < $3
> UNION SELECT
> -- Anfangsstand
> $2, SUM(auftragsressourcen.menge) AS dynmenge
> FROM auftragsressourcen JOIN belegungen USING (auftragsressource_id)
> WHERE ressource_id = $1 AND von <= $2 and bis > $2
> ) AS bel1
-- (1) end
> WHERE bel1.von <= bel2.von
> ) AS kumulierte
> FROM
-- (1) start
> (SELECT
> -- Ressource wird ausgeliehen
> von, menge AS dynmenge
> FROM auftragsressourcen JOIN belegungen USING (auftragsressource_id)
> WHERE ressource_id = $1 AND von > $2 AND von < $3
> UNION SELECT
> -- Ressource wird zurückgegeben
> bis AS von, -menge AS dynmenge
> FROM auftragsressourcen JOIN belegungen USING (auftragsressource_id)
> WHERE ressource_id = $1 AND bis > $2 AND bis < $3
> UNION SELECT
> -- Anfangsstand
> $2, SUM(auftragsressourcen.menge) AS dynmenge
> FROM auftragsressourcen JOIN belegungen USING (auftragsressource_id)
> WHERE ressource_id = $1 AND von <= $2 and bis > $2
> ) AS bel2
-- (1) end
> ) AS belegte
> ' LANGUAGE SQL;
>
The query (1) with some values gives:
von | dynmenge
---------------------+----------
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 <= 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
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: twanger@bluetwanger.de, schwarz@bab24.de
Subject: Re: slow query
In-Reply-To: <1058896617.1391.108.camel@fluor>
* 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