Re: slow query - Mailing list pgsql-sql

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 |        42000-06-06 00:00:00 |        62000-07-07 00:00:00 |
-62000-07-1608:00:00 |        72000-08-16 16:00:00 |       -72000-09-01 00:00:00 |        82000-11-01 00:00:00 |
-82001-01-0100: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 | 42000-06-06 00:00:00 |        6 |
102000-07-0700:00:00 |       -6 | 42000-07-16 08:00:00 |        7 | 112000-08-16 16:00:00 |       -7 | 42000-09-01
00:00:00|        8 | 122000-11-01 00:00:00 |       -8 | 42001-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