slow query

Hi,

we have this large query about which we want to know if it can be made
faster. The query is:

select * from (
select
ressourcen.*, gebaeude.bezeichnung as
"gebaeude.bezeichnung", gebaeude.gebaeude_id as "gebaeude.gebaeude_id",
gebaeude.kurzbezeichnung as "gebaeude.kurzbezeichnung", gebaeude.nummer
as "gebaeude.nummer", raeume.bemerkung as "raeume.bemerkung",
raeume.flaeche as "raeume.flaeche", raeume.nummer as "raeume.nummer",
raeume.raum_id as "raeume.raum_id", raumtypen.bezeichnung as
"raumtypen.bezeichnung", raumtypen.raumtyp_id as "raumtypen.raumtyp_id",
standorte.kurzbezeichnung as "standorte.kurzbezeichnung",
standorte.standort_id as "standorte.standort_id",
exists (
select *
from auftragsressourcen where auftrag_id = '46' and ressource_id
= ressourcen.ressource_id
) as schon_in_auftrag_verwendet,
case when (ressourcen.menge = 1) then (
case when exists(
select *
from (
-- does a row in belegungen exist for the given time intervals
select bGetNumOfBookedSingleRes(ressourcen.ressource_id, turnus.von, turnus.bis) as da from (
select timestamp '2003-07-22 08:00' AS von, timestamp '2003-07-22 20:00' AS bis
union select timestamp '2003-07-28 08:00' AS von, timestamp '2003-07-28 20:00' AS bis
union select timestamp '2003-07-29 08:00' AS von, timestamp '2003-07-29 20:00' AS bis
) as turnus
) as belegte where da
) then 0 else 1 end
) else ressourcen.menge - (
select max(anzahl) from (
-- sum(auftragsressourcen.menge) for all rows in belegungen that intersect the given time interval
-- i.e. the number of used items of a ressource in the given time interval
select iGetNumOfBookedRes(ressourcen.ressource_id, turnus.von, turnus.bis)
as anzahl from (
select timestamp '2003-07-22 08:00' AS von, timestamp '2003-07-22 20:00' AS bis
union select timestamp '2003-07-28 08:00' AS von, timestamp '2003-07-28 20:00' AS bis
union select timestamp '2003-07-29 08:00' AS von, timestamp '2003-07-29 20:00' AS bis
) as turnus
) as belegte
) end as verfuegbar
from ressourcen join raeume using
(ressource_id) join gebaeude using (gebaeude_id) join standorte using
(standort_id) join raumtypen using (raumtyp_id)
) as verfuegbare_ressourcen
where verfuegbare_ressourcen.verfuegbar > 0;

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
(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
WHERE bel1.von <= bel2.von
) AS kumulierte
FROM
(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
) AS belegte
' LANGUAGE SQL;

CREATE FUNCTION bGetNumOfBookedSingleRes(integer, timestamp, timestamp) RETURNS boolean AS '
select exists (select * from auftragsressourcen JOIN belegungen USING
(auftragsressource_id) WHERE ressource_id = $1 AND bis > $2 and von < $3)
' LANGUAGE SQL;

-- explain analyze of the above query

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan verfuegbare_ressourcen (cost=56.37..69.97 rows=88 width=142) (actual time=346.60..677.44 rows=265 loops=1)
-> Hash Join (cost=56.37..69.97 rows=88 width=142) (actual time=346.52..668.46 rows=265 loops=1)
Hash Cond: ("outer".raumtyp_id = "inner".raumtyp_id)
-> Hash Join (cost=55.05..66.88 rows=88 width=125) (actual time=318.00..346.16 rows=265 loops=1)
Hash Cond: ("outer".standort_id = "inner".standort_id)
-> Hash Join (cost=53.96..64.25 rows=88 width=115) (actual time=317.80..336.77 rows=265 loops=1)
Hash Cond: ("outer".gebaeude_id = "inner".gebaeude_id)
-> Hash Join (cost=52.66..61.40 rows=88 width=68) (actual time=317.41..328.62 rows=265 loops=1)
Hash Cond: ("outer".ressource_id = "inner".ressource_id)
-> Seq Scan on raeume (cost=0.00..5.65 rows=265 width=56) (actual time=0.03..5.01 rows=265 loops=1)
-> Hash (cost=52.42..52.42 rows=95 width=12) (actual time=317.28..317.28 rows=0 loops=1)
-> Seq Scan on ressourcen (cost=0.00..52.42 rows=95 width=12) (actual time=2.88..315.62 rows=284 loops=1)
Filter: (CASE WHEN (menge = 1) THEN (CASE WHEN (subplan) THEN 0 ELSE 1 END)::numeric ELSE ((menge)::numeric - (subplan)) END > 0::numeric)
SubPlan
-> Subquery Scan turnus (cost=0.05..0.08 rows=1 width=0) (actual time=0.96..0.96 rows=0 loops=282)
-> Unique (cost=0.05..0.08 rows=1 width=0) (actual time=0.96..0.96 rows=0 loops=282)
-> Sort (cost=0.05..0.06 rows=3 width=0) (actual time=0.95..0.95 rows=0 loops=282)
Sort Key: von, bis
-> Append (cost=0.00..0.03 rows=3 width=0) (actual time=0.80..0.80 rows=0 loops=282)
-> Subquery Scan "*SELECT* 1" (cost=0.00..0.01 rows=1 width=0) (actual time=0.28..0.28 rows=0 loops=282)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.28..0.28 rows=0 loops=282)
One-Time Filter: bgetnumofbookedsingleres($0, '2003-07-22 08:00:00'::timestamp without time zone, '2003-07-22 20:00:00'::timestamp without time zone)
-> Subquery Scan "*SELECT* 2" (cost=0.00..0.01 rows=1 width=0) (actual time=0.25..0.25 rows=0 loops=282)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.24..0.24 rows=0 loops=282)
One-Time Filter: bgetnumofbookedsingleres($0, '2003-07-28 08:00:00'::timestamp without time zone, '2003-07-28 20:00:00'::timestamp without time zone)
-> Subquery Scan "*SELECT* 3" (cost=0.00..0.01 rows=1 width=0) (actual time=0.25..0.25 rows=0 loops=282)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.24..0.24 rows=0 loops=282)
One-Time Filter: bgetnumofbookedsingleres($0, '2003-07-29 08:00:00'::timestamp without time zone, '2003-07-29 20:00:00'::timestamp without time zone)
-> Aggregate (cost=0.08..0.08 rows=1 width=0) (actual time=15.06..15.07 rows=1 loops=2)
-> Subquery Scan turnus (cost=0.05..0.08 rows=1 width=0) (actual time=0.14..0.20 rows=3 loops=2)
-> Unique (cost=0.05..0.08 rows=1 width=0) (actual time=0.13..0.16 rows=3 loops=2)
-> Sort (cost=0.05..0.06 rows=3 width=0) (actual time=0.13..0.14 rows=3 loops=2)
Sort Key: von, bis
-> Append (cost=0.00..0.03 rows=3 width=0) (actual time=0.03..0.08 rows=3 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=0.00..0.01 rows=1 width=0) (actual time=0.02..0.03 rows=1 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.01..0.01 rows=1 loops=1)
-> Subquery Scan "*SELECT* 2" (cost=0.00..0.01 rows=1 width=0) (actual time=0.02..0.02 rows=1 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.01..0.01 rows=1 loops=1)
-> Subquery Scan "*SELECT* 3" (cost=0.00..0.01 rows=1 width=0) (actual time=0.02..0.02 rows=1 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.01..0.01 rows=1 loops=1)
-> Hash (cost=1.24..1.24 rows=24 width=47) (actual time=0.32..0.32 rows=0 loops=1)
-> Seq Scan on gebaeude (cost=0.00..1.24 rows=24 width=47) (actual time=0.02..0.21 rows=24 loops=1)
-> Hash (cost=1.07..1.07 rows=7 width=10) (actual time=0.09..0.09 rows=0 loops=1)
-> Seq Scan on standorte (cost=0.00..1.07 rows=7 width=10) (actual time=0.02..0.06 rows=7 loops=1)
-> Hash (cost=1.26..1.26 rows=26 width=17) (actual time=0.28..0.28 rows=0 loops=1)
-> Seq Scan on raumtypen (cost=0.00..1.26 rows=26 width=17) (actual time=0.04..0.19 rows=26 loops=1)
SubPlan
-> Seq Scan on auftragsressourcen (cost=0.00..1.17 rows=1 width=20) (actual time=0.04..0.04 rows=0 loops=265)
Filter: ((auftrag_id = 46) AND (ressource_id = $0))
-> Subquery Scan turnus (cost=0.05..0.08 rows=1 width=0) (actual time=0.96..0.96 rows=0 loops=263)
-> Unique (cost=0.05..0.08 rows=1 width=0) (actual time=0.95..0.95 rows=0 loops=263)
-> Sort (cost=0.05..0.06 rows=3 width=0) (actual time=0.95..0.95 rows=0 loops=263)
Sort Key: von, bis
-> Append (cost=0.00..0.03 rows=3 width=0) (actual time=0.79..0.79 rows=0 loops=263)
-> Subquery Scan "*SELECT* 1" (cost=0.00..0.01 rows=1 width=0) (actual time=0.26..0.26 rows=0 loops=263)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.26..0.26 rows=0 loops=263)
One-Time Filter: bgetnumofbookedsingleres($0, '2003-07-22 08:00:00'::timestamp without time zone, '2003-07-22 20:00:00'::timestamp without time zone)
-> Subquery Scan "*SELECT* 2" (cost=0.00..0.01 rows=1 width=0) (actual time=0.26..0.26 rows=0 loops=263)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.25..0.25 rows=0 loops=263)
One-Time Filter: bgetnumofbookedsingleres($0, '2003-07-28 08:00:00'::timestamp without time zone, '2003-07-28 20:00:00'::timestamp without time zone)
-> Subquery Scan "*SELECT* 3" (cost=0.00..0.01 rows=1 width=0) (actual time=0.25..0.25 rows=0 loops=263)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.25..0.25 rows=0 loops=263)
One-Time Filter: bgetnumofbookedsingleres($0, '2003-07-29 08:00:00'::timestamp without time zone, '2003-07-29 20:00:00'::timestamp without time zone)
-> Aggregate (cost=0.08..0.08 rows=1 width=0) (actual time=18.43..18.43 rows=1 loops=2)
-> Subquery Scan turnus (cost=0.05..0.08 rows=1 width=0) (actual time=0.14..0.20 rows=3 loops=2)
-> Unique (cost=0.05..0.08 rows=1 width=0) (actual time=0.13..0.16 rows=3 loops=2)
-> Sort (cost=0.05..0.06 rows=3 width=0) (actual time=0.13..0.14 rows=3 loops=2)
Sort Key: von, bis
-> Append (cost=0.00..0.03 rows=3 width=0) (actual time=0.03..0.08 rows=3 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=0.00..0.01 rows=1 width=0) (actual time=0.02..0.03 rows=1 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.01..0.01 rows=1 loops=1)
-> Subquery Scan "*SELECT* 2" (cost=0.00..0.01 rows=1 width=0) (actual time=0.02..0.02 rows=1 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.01..0.01 rows=1 loops=1)
-> Subquery Scan "*SELECT* 3" (cost=0.00..0.01 rows=1 width=0) (actual time=0.01..0.02 rows=1 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.01..0.01 rows=1 loops=1)
Total runtime: 680.23 msec
(rows: 76)

At the moment we have ~300 rows in ressourcen, there are going to be
1000-10000 rows in it in production. The turnus union also normally has
around 100 rows.

With 300 rows in ressourcen and ~50 turnus rows the query takes 15
seconds here. With 3000 rows in ressourcen I expect around 150 seconds
which is too long. iGetNumOfBookedRes is pretty expensive, and because
almost all rows in ressource have menge = 1 we wrote an less expensive
function for that case that only checks for the existance of a row in
belegungen for the given time.

Attached are the table schemas, function definitions and the same query
with a larger turnus union. The large query takes ~13 seconds here. Test
data can be downloaded from

http://www.bab24.de/media/testdata.sql

If it helps to explain what this query actually does, I'll gladly follow
up with that. We're using PostgreSQL 7.3.3.

I hope I didn't forget anyting important.

Thanks

--
Markus Bertheau
Cenes Data GmbH