From: Markus Bertheau <twanger@bluetwanger.de>
To: postgres sql list <pgsql-sql@postgresql.org>
Cc: Horst Schwarz <schwarz@bab24.de>
Subject: slow query
Date: 22 Jul 2003 19:07:02 +0200
Message-ID: <1058893618.1857.94.camel@fluor> (raw)
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
Attachments:schema.sql (text/x-sql, 5.1K) download
\c template1
drop database "ibh2";
create database "ibh2" with encoding='unicode';
\c ibh2
create table auftragsarten (
auftragsart_id serial primary key,
name text not null,
tabellenname text not null,
tabellen_id_name text not null
);
create table auftraege (
auftrag_id serial primary key,
auftragsart_id int references auftragsarten on delete restrict on update cascade not null,
bezeichnung text not null
);
create table semester (
semester_id serial primary key,
name text not null
);
create table fachbereiche (
fachbereich_id serial primary key,
name text not null
);
create table studiengaenge (
studiengang_id serial primary key,
fachbereich_id int references fachbereiche on delete cascade on update cascade not null,
name text not null,
kuerzel text not null
);
create table ressourcenarten (
ressourcenart_id serial primary key,
name text not null,
tabellenname text not null,
tabellen_id_name text not null,
bezeichnungsausdruck text not null
);
create table ressourcen (
ressource_id serial primary key,
ressourcenart_id integer not null references ressourcenarten on delete restrict on update cascade,
menge integer not null,
constraint menge_groessergleich_null check (menge >= 0)
);
create table raumtypen (
raumtyp_id serial primary key,
bezeichnung text not null
);
create table standorte (
standort_id serial primary key,
kurzbezeichnung text not null,
bezeichnung text not null
);
create table gebaeude (
gebaeude_id serial primary key,
standort_id int not null references standorte on delete cascade on update cascade,
kurzbezeichnung text not null,
bezeichnung text not null,
nummer text not null
);
create table raeume (
raum_id serial primary key,
ressource_id integer not null unique references ressourcen on delete cascade on update cascade,
gebaeude_id int not null references gebaeude on delete cascade on update cascade,
raumtyp_id int not null references raumtypen on delete cascade on update cascade,
nummer text,
flaeche numeric(8,2),
bemerkung text
);
create table auftragsressourcen (
auftragsressource_id serial primary key,
ressource_id integer not null references ressourcen on delete restrict on update cascade,
auftrag_id integer not null references auftraege on update cascade on delete cascade,
menge integer not null,
status integer default 0,
unique(ressource_id, auftrag_id),
constraint gueltiger_status check (status between 0 and 2)
);
create table belegungen (
belegung_id serial primary key,
auftragsressource_id integer not null references auftragsressourcen on update cascade on delete cascade,
von timestamp not null,
bis timestamp not null,
constraint von_kleiner_bis check (von < bis)
);
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;
largequery.sql (text/x-sql, 15.5K) download
-- example query with ~80 rows in the turnus subselect
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 ( select bGetNumOfBookedSingleRes(ressourcen.ressource_id, turnus.von, turnus.bis) as da from (select timestamp '2002-10-16 08:00:00' AS von, timestamp '2002-10-16 08:45:00' AS bis union select timestamp '2002-10-16 08:45:00' AS von, timestamp '2002-10-16 09:30:00' AS bis union select timestamp '2002-10-21 08:00:00' AS von, timestamp '2002-10-21 08:45:00' AS bis union select timestamp '2002-10-21 08:45:00' AS von, timestamp '2002-10-21 09:30:00' AS bis union select timestamp '2002-10-23 08:00:00' AS von, timestamp '2002-10-23 08:45:00' AS bis union select timestamp '2002-10-23 08:45:00' AS von, timestamp '2002-10-23 09:30:00' AS bis union select timestamp '2002-10-28 08:00:00' AS von, timestamp '2002-10-28 08:45:00' AS bis union select timestamp '2002-10-28 08:45:00' AS von, timestamp '2002-10-28 09:30:00' AS bis union select timestamp '2002-10-30 08:00:00' AS von, timestamp '2002-10-30 08:45:00' AS bis union select timestamp '2002-10-30 08:45:00' AS von, timestamp '2002-10-30 09:30:00' AS bis union select timestamp '2002-11-04 08:00:00' AS von, timestamp '2002-11-04 08:45:00' AS bis union select timestamp '2002-11-04 08:45:00' AS von, timestamp '2002-11-04 09:30:00' AS bis union select timestamp '2002-11-06 08:00:00' AS von, timestamp '2002-11-06 08:45:00' AS bis union select timestamp '2002-11-06 08:45:00' AS von, timestamp '2002-11-06 09:30:00' AS bis union select timestamp '2002-11-11 08:00:00' AS von, timestamp '2002-11-11 08:45:00' AS bis union select timestamp '2002-11-11 08:45:00' AS von, timestamp '2002-11-11 09:30:00' AS bis union select timestamp '2002-11-13 08:00:00' AS von, timestamp '2002-11-13 08:45:00' AS bis union select timestamp '2002-11-13 08:45:00' AS von, timestamp '2002-11-13 09:30:00' AS bis union select timestamp '2002-11-18 08:00:00' AS von, timestamp '2002-11-18 08:45:00' AS bis union select timestamp '2002-11-18 08:45:00' AS von, timestamp '2002-11-18 09:30:00' AS bis union select timestamp '2002-11-20 08:00:00' AS von, timestamp '2002-11-20 08:45:00' AS bis union select timestamp '2002-11-20 08:45:00' AS von, timestamp '2002-11-20 09:30:00' AS bis union select timestamp '2002-11-25 08:00:00' AS von, timestamp '2002-11-25 08:45:00' AS bis union select timestamp '2002-11-25 08:45:00' AS von, timestamp '2002-11-25 09:30:00' AS bis union select timestamp '2002-11-27 08:00:00' AS von, timestamp '2002-11-27 08:45:00' AS bis union select timestamp '2002-11-27 08:45:00' AS von, timestamp '2002-11-27 09:30:00' AS bis union select timestamp '2002-12-02 08:00:00' AS von, timestamp '2002-12-02 08:45:00' AS bis union select timestamp '2002-12-02 08:45:00' AS von, timestamp '2002-12-02 09:30:00' AS bis union select timestamp '2002-12-04 08:00:00' AS von, timestamp '2002-12-04 08:45:00' AS bis union select timestamp '2002-12-04 08:45:00' AS von, timestamp '2002-12-04 09:30:00' AS bis union select timestamp '2002-12-09 08:00:00' AS von, timestamp '2002-12-09 08:45:00' AS bis union select timestamp '2002-12-09 08:45:00' AS von, timestamp '2002-12-09 09:30:00' AS bis union select timestamp '2002-12-11 08:00:00' AS von, timestamp '2002-12-11 08:45:00' AS bis union select timestamp '2002-12-11 08:45:00' AS von, timestamp '2002-12-11 09:30:00' AS bis union select timestamp '2002-12-16 08:00:00' AS von, timestamp '2002-12-16 08:45:00' AS bis union select timestamp '2002-12-16 08:45:00' AS von, timestamp '2002-12-16 09:30:00' AS bis union select timestamp '2002-12-18 08:00:00' AS von, timestamp '2002-12-18 08:45:00' AS bis union select timestamp '2002-12-18 08:45:00' AS von, timestamp '2002-12-18 09:30:00' AS bis union select timestamp '2002-12-23 08:00:00' AS von, timestamp '2002-12-23 08:45:00' AS bis union select timestamp '2002-12-23 08:45:00' AS von, timestamp '2002-12-23 09:30:00' AS bis union select timestamp '2002-12-25 08:00:00' AS von, timestamp '2002-12-25 08:45:00' AS bis union select timestamp '2002-12-25 08:45:00' AS von, timestamp '2002-12-25 09:30:00' AS bis union select timestamp '2002-12-30 08:00:00' AS von, timestamp '2002-12-30 08:45:00' AS bis union select timestamp '2002-12-30 08:45:00' AS von, timestamp '2002-12-30 09:30:00' AS bis union select timestamp '2003-01-01 08:00:00' AS von, timestamp '2003-01-01 08:45:00' AS bis union select timestamp '2003-01-01 08:45:00' AS von, timestamp '2003-01-01 09:30:00' AS bis union select timestamp '2003-01-06 08:00:00' AS von, timestamp '2003-01-06 08:45:00' AS bis union select timestamp '2003-01-06 08:45:00' AS von, timestamp '2003-01-06 09:30:00' AS bis union select timestamp '2003-01-08 08:00:00' AS von, timestamp '2003-01-08 08:45:00' AS bis union select timestamp '2003-01-08 08:45:00' AS von, timestamp '2003-01-08 09:30:00' AS bis union select timestamp '2003-01-13 08:00:00' AS von, timestamp '2003-01-13 08:45:00' AS bis union select timestamp '2003-01-13 08:45:00' AS von, timestamp '2003-01-13 09:30:00' AS bis union select timestamp '2003-01-15 08:00:00' AS von, timestamp '2003-01-15 08:45:00' AS bis union select timestamp '2003-01-15 08:45:00' AS von, timestamp '2003-01-15 09:30:00' AS bis union select timestamp '2003-01-20 08:00:00' AS von, timestamp '2003-01-20 08:45:00' AS bis union select timestamp '2003-01-20 08:45:00' AS von, timestamp '2003-01-20 09:30:00' AS bis union select timestamp '2003-01-22 08:00:00' AS von, timestamp '2003-01-22 08:45:00' AS bis union select timestamp '2003-01-22 08:45:00' AS von, timestamp '2003-01-22 09:30:00' AS bis union select timestamp '2003-01-27 08:00:00' AS von, timestamp '2003-01-27 08:45:00' AS bis union select timestamp '2003-01-27 08:45:00' AS von, timestamp '2003-01-27 09:30:00' AS bis union select timestamp '2003-01-29 08:00:00' AS von, timestamp '2003-01-29 08:45:00' AS bis union select timestamp '2003-01-29 08:45:00' AS von, timestamp '2003-01-29 09:30:00' AS bis union select timestamp '2003-02-03 08:00:00' AS von, timestamp '2003-02-03 08:45:00' AS bis union select timestamp '2003-02-03 08:45:00' AS von, timestamp '2003-02-03 09:30:00' AS bis union select timestamp '2003-02-05 08:00:00' AS von, timestamp '2003-02-05 08:45:00' AS bis union select timestamp '2003-02-05 08:45:00' AS von, timestamp '2003-02-05 09:30:00' AS bis union select timestamp '2003-02-10 08:00:00' AS von, timestamp '2003-02-10 08:45:00' AS bis union select timestamp '2003-02-10 08:45:00' AS von, timestamp '2003-02-10 09:30:00' AS bis union select timestamp '2003-02-12 08:00:00' AS von, timestamp '2003-02-12 08:45:00' AS bis union select timestamp '2003-02-12 08:45:00' AS von, timestamp '2003-02-12 09:30:00' AS bis union select timestamp '2003-02-17 08:00:00' AS von, timestamp '2003-02-17 08:45:00' AS bis union select timestamp '2003-02-17 08:45:00' AS von, timestamp '2003-02-17 09:30:00' AS bis union select timestamp '2003-02-19 08:00:00' AS von, timestamp '2003-02-19 08:45:00' AS bis union select timestamp '2003-02-19 08:45:00' AS von, timestamp '2003-02-19 09:30:00' AS bis union select timestamp '2003-02-24 08:00:00' AS von, timestamp '2003-02-24 08:45:00' AS bis union select timestamp '2003-02-24 08:45:00' AS von, timestamp '2003-02-24 09:30:00' AS bis union select timestamp '2003-02-26 08:00:00' AS von, timestamp '2003-02-26 08:45:00' AS bis union select timestamp '2003-02-26 08:45:00' AS von, timestamp '2003-02-26 09:30:00' AS bis) as turnus) as belegte where da)then 0 else 1 end) else ressourcen.menge - (select max(anzahl) from (select iGetNumOfBookedRes(ressourcen.ressource_id, turnus.von, turnus.bis) as anzahl from (select timestamp '2002-10-16 08:00:00' AS von, timestamp '2002-10-16 08:45:00' AS bis union select timestamp '2002-10-16 08:45:00' AS von, timestamp '2002-10-16 09:30:00' AS bis union select timestamp '2002-10-21 08:00:00' AS von, timestamp '2002-10-21 08:45:00' AS bis union select timestamp '2002-10-21 08:45:00' AS von, timestamp '2002-10-21 09:30:00' AS bis union select timestamp '2002-10-23 08:00:00' AS von, timestamp '2002-10-23 08:45:00' AS bis union select timestamp '2002-10-23 08:45:00' AS von, timestamp '2002-10-23 09:30:00' AS bis union select timestamp '2002-10-28 08:00:00' AS von, timestamp '2002-10-28 08:45:00' AS bis union select timestamp '2002-10-28 08:45:00' AS von, timestamp '2002-10-28 09:30:00' AS bis union select timestamp '2002-10-30 08:00:00' AS von, timestamp '2002-10-30 08:45:00' AS bis union select timestamp '2002-10-30 08:45:00' AS von, timestamp '2002-10-30 09:30:00' AS bis union select timestamp '2002-11-04 08:00:00' AS von, timestamp '2002-11-04 08:45:00' AS bis union select timestamp '2002-11-04 08:45:00' AS von, timestamp '2002-11-04 09:30:00' AS bis union select timestamp '2002-11-06 08:00:00' AS von, timestamp '2002-11-06 08:45:00' AS bis union select timestamp '2002-11-06 08:45:00' AS von, timestamp '2002-11-06 09:30:00' AS bis union select timestamp '2002-11-11 08:00:00' AS von, timestamp '2002-11-11 08:45:00' AS bis union select timestamp '2002-11-11 08:45:00' AS von, timestamp '2002-11-11 09:30:00' AS bis union select timestamp '2002-11-13 08:00:00' AS von, timestamp '2002-11-13 08:45:00' AS bis union select timestamp '2002-11-13 08:45:00' AS von, timestamp '2002-11-13 09:30:00' AS bis union select timestamp '2002-11-18 08:00:00' AS von, timestamp '2002-11-18 08:45:00' AS bis union select timestamp '2002-11-18 08:45:00' AS von, timestamp '2002-11-18 09:30:00' AS bis union select timestamp '2002-11-20 08:00:00' AS von, timestamp '2002-11-20 08:45:00' AS bis union select timestamp '2002-11-20 08:45:00' AS von, timestamp '2002-11-20 09:30:00' AS bis union select timestamp '2002-11-25 08:00:00' AS von, timestamp '2002-11-25 08:45:00' AS bis union select timestamp '2002-11-25 08:45:00' AS von, timestamp '2002-11-25 09:30:00' AS bis union select timestamp '2002-11-27 08:00:00' AS von, timestamp '2002-11-27 08:45:00' AS bis union select timestamp '2002-11-27 08:45:00' AS von, timestamp '2002-11-27 09:30:00' AS bis union select timestamp '2002-12-02 08:00:00' AS von, timestamp '2002-12-02 08:45:00' AS bis union select timestamp '2002-12-02 08:45:00' AS von, timestamp '2002-12-02 09:30:00' AS bis union select timestamp '2002-12-04 08:00:00' AS von, timestamp '2002-12-04 08:45:00' AS bis union select timestamp '2002-12-04 08:45:00' AS von, timestamp '2002-12-04 09:30:00' AS bis union select timestamp '2002-12-09 08:00:00' AS von, timestamp '2002-12-09 08:45:00' AS bis union select timestamp '2002-12-09 08:45:00' AS von, timestamp '2002-12-09 09:30:00' AS bis union select timestamp '2002-12-11 08:00:00' AS von, timestamp '2002-12-11 08:45:00' AS bis union select timestamp '2002-12-11 08:45:00' AS von, timestamp '2002-12-11 09:30:00' AS bis union select timestamp '2002-12-16 08:00:00' AS von, timestamp '2002-12-16 08:45:00' AS bis union select timestamp '2002-12-16 08:45:00' AS von, timestamp '2002-12-16 09:30:00' AS bis union select timestamp '2002-12-18 08:00:00' AS von, timestamp '2002-12-18 08:45:00' AS bis union select timestamp '2002-12-18 08:45:00' AS von, timestamp '2002-12-18 09:30:00' AS bis union select timestamp '2002-12-23 08:00:00' AS von, timestamp '2002-12-23 08:45:00' AS bis union select timestamp '2002-12-23 08:45:00' AS von, timestamp '2002-12-23 09:30:00' AS bis union select timestamp '2002-12-25 08:00:00' AS von, timestamp '2002-12-25 08:45:00' AS bis union select timestamp '2002-12-25 08:45:00' AS von, timestamp '2002-12-25 09:30:00' AS bis union select timestamp '2002-12-30 08:00:00' AS von, timestamp '2002-12-30 08:45:00' AS bis union select timestamp '2002-12-30 08:45:00' AS von, timestamp '2002-12-30 09:30:00' AS bis union select timestamp '2003-01-01 08:00:00' AS von, timestamp '2003-01-01 08:45:00' AS bis union select timestamp '2003-01-01 08:45:00' AS von, timestamp '2003-01-01 09:30:00' AS bis union select timestamp '2003-01-06 08:00:00' AS von, timestamp '2003-01-06 08:45:00' AS bis union select timestamp '2003-01-06 08:45:00' AS von, timestamp '2003-01-06 09:30:00' AS bis union select timestamp '2003-01-08 08:00:00' AS von, timestamp '2003-01-08 08:45:00' AS bis union select timestamp '2003-01-08 08:45:00' AS von, timestamp '2003-01-08 09:30:00' AS bis union select timestamp '2003-01-13 08:00:00' AS von, timestamp '2003-01-13 08:45:00' AS bis union select timestamp '2003-01-13 08:45:00' AS von, timestamp '2003-01-13 09:30:00' AS bis union select timestamp '2003-01-15 08:00:00' AS von, timestamp '2003-01-15 08:45:00' AS bis union select timestamp '2003-01-15 08:45:00' AS von, timestamp '2003-01-15 09:30:00' AS bis union select timestamp '2003-01-20 08:00:00' AS von, timestamp '2003-01-20 08:45:00' AS bis union select timestamp '2003-01-20 08:45:00' AS von, timestamp '2003-01-20 09:30:00' AS bis union select timestamp '2003-01-22 08:00:00' AS von, timestamp '2003-01-22 08:45:00' AS bis union select timestamp '2003-01-22 08:45:00' AS von, timestamp '2003-01-22 09:30:00' AS bis union select timestamp '2003-01-27 08:00:00' AS von, timestamp '2003-01-27 08:45:00' AS bis union select timestamp '2003-01-27 08:45:00' AS von, timestamp '2003-01-27 09:30:00' AS bis union select timestamp '2003-01-29 08:00:00' AS von, timestamp '2003-01-29 08:45:00' AS bis union select timestamp '2003-01-29 08:45:00' AS von, timestamp '2003-01-29 09:30:00' AS bis union select timestamp '2003-02-03 08:00:00' AS von, timestamp '2003-02-03 08:45:00' AS bis union select timestamp '2003-02-03 08:45:00' AS von, timestamp '2003-02-03 09:30:00' AS bis union select timestamp '2003-02-05 08:00:00' AS von, timestamp '2003-02-05 08:45:00' AS bis union select timestamp '2003-02-05 08:45:00' AS von, timestamp '2003-02-05 09:30:00' AS bis union select timestamp '2003-02-10 08:00:00' AS von, timestamp '2003-02-10 08:45:00' AS bis union select timestamp '2003-02-10 08:45:00' AS von, timestamp '2003-02-10 09:30:00' AS bis union select timestamp '2003-02-12 08:00:00' AS von, timestamp '2003-02-12 08:45:00' AS bis union select timestamp '2003-02-12 08:45:00' AS von, timestamp '2003-02-12 09:30:00' AS bis union select timestamp '2003-02-17 08:00:00' AS von, timestamp '2003-02-17 08:45:00' AS bis union select timestamp '2003-02-17 08:45:00' AS von, timestamp '2003-02-17 09:30:00' AS bis union select timestamp '2003-02-19 08:00:00' AS von, timestamp '2003-02-19 08:45:00' AS bis union select timestamp '2003-02-19 08:45:00' AS von, timestamp '2003-02-19 09:30:00' AS bis union select timestamp '2003-02-24 08:00:00' AS von, timestamp '2003-02-24 08:45:00' AS bis union select timestamp '2003-02-24 08:45:00' AS von, timestamp '2003-02-24 09:30:00' AS bis union select timestamp '2003-02-26 08:00:00' AS von, timestamp '2003-02-26 08:45:00' AS bis union select timestamp '2003-02-26 08:45:00' AS von, timestamp '2003-02-26 09:30: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;
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: <1058893618.1857.94.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