pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSlow query
11+ messages / 7 participants
[nested] [flat]
* Slow query
@ 2003-01-23 14:16 Ricardo Javier Aranibar León <ricardo_jal@hotmail.com>
2003-01-23 14:43 ` Re: Slow query Tomasz Myrta <jasiek@klaster.net>
0 siblings, 1 reply; 11+ messages in thread
From: Ricardo Javier Aranibar León @ 2003-01-23 14:16 UTC (permalink / raw)
To: pgsql-sql; +Cc: ricardo_jal@linuxmail.org
Hi List,
I need your help, I don't know what happend with my queries when I use this
table "simon".When I like display the result the query is very slow and take
five minutes or more.
The number of registers in simon is:2421580
and the other table "llamada" the number of registers is :1779741 but the
time of my query is faster that when i run the query but using my table
simon
The struct of my table "simon" is:
Table "simon"
Attribute | Type | Modifier
-----------+-------------+-------------------------------------------------
num | integer | not null default nextval('simon_num_seq'::text)
ip | varchar(16) |
fecha | date |
hora | time |
tras | smallint |
reci | smallint |
perd | smallint |
min | float4 |
avg | float4 |
max | float4 |
Indices:
avg_si,fecha_si,hora_si,ip_si,max_si,min_si,perd_si,reci_si,simon_hora,
simon_pkey,tras_si
I run Explain and this is the result:
EXPLAIN SELECT * from simon where fecha='20030122' order by hora desc;
NOTICE: QUERY PLAN:
Sort (cost=57700.73..57700.73 rows=23682 width=46)
-> Seq Scan on simon (cost=0.00..55591.05 rows=23682 width=46)
and when I run explain for another table this is te result:
EXPLAIN SELECT * from llamada where fecha='20030122' order by hora desc;
NOTICE: QUERY PLAN:
Sort (cost=41102.70..41102.70 rows=12413 width=82)
-> Index Scan using fecha_lla on llamada (cost=0.00..39944.95 rows=12413
width=82)
This is the sintax when i created my index:
CREATE INDEX fecha_si ON simon USING btree(fecha);
If somebody can help me or tell me why my queries using the table simon are
slow, I will thankfull
Regards,
Ricardo
PD:Sorry for my grammar Enghish
_________________________________________________________________
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Slow query
2003-01-23 14:16 Slow query Ricardo Javier Aranibar León <ricardo_jal@hotmail.com>
@ 2003-01-23 14:43 ` Tomasz Myrta <jasiek@klaster.net>
0 siblings, 0 replies; 11+ messages in thread
From: Tomasz Myrta @ 2003-01-23 14:43 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
Ricardo Javier Aranibar León wrote:
> I run Explain and this is the result:
> EXPLAIN SELECT * from simon where fecha='20030122' order by hora desc;
> NOTICE: QUERY PLAN:
> Sort (cost=57700.73..57700.73 rows=23682 width=46)
> -> Seq Scan on simon (cost=0.00..55591.05 rows=23682 width=46)
>
> and when I run explain for another table this is te result:
> EXPLAIN SELECT * from llamada where fecha='20030122' order by hora desc;
> NOTICE: QUERY PLAN:
> Sort (cost=41102.70..41102.70 rows=12413 width=82)
> -> Index Scan using fecha_lla on llamada (cost=0.00..39944.95
> rows=12413 width=82)
I'm not sure if there is much to help. You have a lots of rows in result -
23k rows and 12k rows. The problem is in output, not in query or indexing.
Do you really use such queries?
Anyway, did you vaccum analyze before testing?
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 11+ messages in thread
* slow query
@ 2003-07-22 17:07 Markus Bertheau <twanger@bluetwanger.de>
2003-07-22 17:56 ` Re: slow query Markus Bertheau <twanger@bluetwanger.de>
0 siblings, 1 reply; 11+ messages in thread
From: Markus Bertheau @ 2003-07-22 17:07 UTC (permalink / raw)
To: pgsql-sql; +Cc: Horst Schwarz <schwarz@bab24.de>
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;
* Re: slow query
2003-07-22 17:07 slow query Markus Bertheau <twanger@bluetwanger.de>
@ 2003-07-22 17:56 ` Markus Bertheau <twanger@bluetwanger.de>
2003-07-23 15:32 ` Re: slow query Markus Bertheau <twanger@bluetwanger.de>
0 siblings, 1 reply; 11+ messages in thread
From: Markus Bertheau @ 2003-07-22 17:56 UTC (permalink / raw)
To: pgsql-sql; +Cc: Horst Schwarz <schwarz@bab24.de>
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: slow query
2003-07-22 17:07 slow query Markus Bertheau <twanger@bluetwanger.de>
2003-07-22 17:56 ` Re: slow query Markus Bertheau <twanger@bluetwanger.de>
@ 2003-07-23 15:32 ` Markus Bertheau <twanger@bluetwanger.de>
0 siblings, 0 replies; 11+ messages in thread
From: Markus Bertheau @ 2003-07-23 15:32 UTC (permalink / raw)
To: pgsql-sql; +Cc: Horst Schwarz <schwarz@bab24.de>
I've forgotten to put data for the belegungen table in the test data
set, I've corrected that now, an updated data set is available at the
same URL:
http://www.bab24.de/media/testdata.sql
--
Markus Bertheau
Cenes Data GmbH
^ permalink raw reply [nested|flat] 11+ messages in thread
* slow query
@ 2007-04-05 03:17 Sumeet <asumeet@gmail.com>
2007-04-05 05:11 ` Re: slow query A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 1 reply; 11+ messages in thread
From: Sumeet @ 2007-04-05 03:17 UTC (permalink / raw)
To: pgsql-sql
Hi all
I ran a simple query like the one displayed below and it takes a lot of time
to execute on this table.
This table has 48 million records....and i worry about this table a lot coz
i need to perform join on this
table with some other table having around 13 million records....I've tried
vacuuming this table many time
but dint help me much...is there any other trick to speed up this
table....once i tried deleting and restoring a table
and it worked perfectly fine for a small table of size upto 5-6 million
records
sm=> explain analyze select * from ma limit 10;
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..0.45 rows=10 width=76) (actual
time=21985.292..22204.340rows=10 loops=1)
-> Seq Scan on ma (cost=0.00..2181956.92 rows=48235392 width=76)
(actual time=21985.285..22204.308 rows=10 loops=1)
Total runtime: 22204.476 ms
(3 rows)
--
Thanks,
Sumeet
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: slow query
2007-04-05 03:17 slow query Sumeet <asumeet@gmail.com>
@ 2007-04-05 05:11 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-04-05 05:27 ` Re: slow query Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: A. Kretschmer @ 2007-04-05 05:11 UTC (permalink / raw)
To: pgsql-sql
am Wed, dem 04.04.2007, um 23:17:54 -0400 mailte Sumeet folgendes:
> sm=> explain analyze select * from ma limit 10;
> QUERY
> PLAN
> -------------------------------------------------------------------------------------------------------------------------------------
> Limit (cost=0.00..0.45 rows=10 width=76) (actual time=21985.292..22204.340
> rows=10 loops=1)
> -> Seq Scan on ma (cost=0.00..2181956.92 rows=48235392 width=76) (actual
> time=21985.285..22204.308 rows=10 loops=1)
> Total runtime: 22204.476 ms
> (3 rows)
which version?
I have 8.1 and on a table with 1.9 million records i got:
scholl=# explain analyse select * from bde_meldungen limit 10;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..0.34 rows=10 width=157) (actual time=19.570..19.660 rows=10 loops=1)
-> Seq Scan on bde_meldungen (cost=0.00..65748.33 rows=1957933 width=157) (actual time=19.565..19.587 rows=10 loops=1)
Total runtime: 19.845 ms
(3 rows)
If I remember correctly, since 8.0 or 8.1 we have a massive improvement with
LIMIT.
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: slow query
2007-04-05 03:17 slow query Sumeet <asumeet@gmail.com>
2007-04-05 05:11 ` Re: slow query A. Kretschmer <andreas.kretschmer@schollglas.com>
@ 2007-04-05 05:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-04-05 05:36 ` Re: slow query A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2007-04-05 05:27 UTC (permalink / raw)
To: A. Kretschmer <andreas.kretschmer@schollglas.com>; +Cc: pgsql-sql
"A. Kretschmer" <andreas.kretschmer@schollglas.com> writes:
> am Wed, dem 04.04.2007, um 23:17:54 -0400 mailte Sumeet folgendes:
>> sm=> explain analyze select * from ma limit 10;
>> QUERY
>> PLAN
>> -------------------------------------------------------------------------------------------------------------------------------------
>> Limit (cost=0.00..0.45 rows=10 width=76) (actual time=21985.292..22204.340
>> rows=10 loops=1)
>> -> Seq Scan on ma (cost=0.00..2181956.92 rows=48235392 width=76) (actual
>> time=21985.285..22204.308 rows=10 loops=1)
>> Total runtime: 22204.476 ms
>> (3 rows)
> which version?
I'm betting the problem is poor vacuuming practice leading to lots of
dead space. There's no way it takes 22 sec to read 10 rows if the
table is reasonably dense.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: slow query
2007-04-05 03:17 slow query Sumeet <asumeet@gmail.com>
2007-04-05 05:11 ` Re: slow query A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-04-05 05:27 ` Re: slow query Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-04-05 05:36 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-04-05 05:47 ` Re: slow query Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: A. Kretschmer @ 2007-04-05 05:36 UTC (permalink / raw)
To: pgsql-sql
am Thu, dem 05.04.2007, um 1:27:25 -0400 mailte Tom Lane folgendes:
> "A. Kretschmer" <andreas.kretschmer@schollglas.com> writes:
> > am Wed, dem 04.04.2007, um 23:17:54 -0400 mailte Sumeet folgendes:
> >> sm=> explain analyze select * from ma limit 10;
> >> QUERY
> >> PLAN
> >> -------------------------------------------------------------------------------------------------------------------------------------
> >> Limit (cost=0.00..0.45 rows=10 width=76) (actual time=21985.292..22204.340
> >> rows=10 loops=1)
> >> -> Seq Scan on ma (cost=0.00..2181956.92 rows=48235392 width=76) (actual
> >> time=21985.285..22204.308 rows=10 loops=1)
> >> Total runtime: 22204.476 ms
> >> (3 rows)
>
> > which version?
>
> I'm betting the problem is poor vacuuming practice leading to lots of
> dead space. There's no way it takes 22 sec to read 10 rows if the
> table is reasonably dense.
This was my first thought, but:
,----[ Quote ]
| I've tried
| vacuuming this table many time
`----
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: slow query
2007-04-05 03:17 slow query Sumeet <asumeet@gmail.com>
2007-04-05 05:11 ` Re: slow query A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-04-05 05:27 ` Re: slow query Tom Lane <tgl@sss.pgh.pa.us>
2007-04-05 05:36 ` Re: slow query A. Kretschmer <andreas.kretschmer@schollglas.com>
@ 2007-04-05 05:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-04-05 13:33 ` Re: slow query Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2007-04-05 05:47 UTC (permalink / raw)
To: A. Kretschmer <andreas.kretschmer@schollglas.com>; +Cc: pgsql-sql
"A. Kretschmer" <andreas.kretschmer@schollglas.com> writes:
> am Thu, dem 05.04.2007, um 1:27:25 -0400 mailte Tom Lane folgendes:
>> I'm betting the problem is poor vacuuming practice leading to lots of
>> dead space. There's no way it takes 22 sec to read 10 rows if the
>> table is reasonably dense.
> This was my first thought, but:
> ,----[ Quote ]
> | I've tried
> | vacuuming this table many time
> `----
[ shrug... ] If the damage is already done, lazy VACUUM won't fix it.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: slow query
2007-04-05 03:17 slow query Sumeet <asumeet@gmail.com>
2007-04-05 05:11 ` Re: slow query A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-04-05 05:27 ` Re: slow query Tom Lane <tgl@sss.pgh.pa.us>
2007-04-05 05:36 ` Re: slow query A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-04-05 05:47 ` Re: slow query Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-04-05 13:33 ` Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 0 replies; 11+ messages in thread
From: Andrew Sullivan @ 2007-04-05 13:33 UTC (permalink / raw)
To: pgsql-sql
On Thu, Apr 05, 2007 at 01:47:03AM -0400, Tom Lane wrote:
> [ shrug... ] If the damage is already done, lazy VACUUM won't fix it.
>
Also, if there are enough open transactions at any one time and
sufficient churn in the table, lazy VACUUM may not be able to keep
up. (We had that experience with a table that was updated _very
very_ often. The answer turned out to be to update less often.
Aggregating queries that could use an index over a large number of
"expired" rows worked better than seqscans over large numbers of dead
tuples.)
A
--
Andrew Sullivan | ajs@crankycanuck.ca
Information security isn't a technological problem. It's an economics
problem.
--Bruce Schneier
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2007-04-05 13:33 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-23 14:16 Slow query Ricardo Javier Aranibar León <ricardo_jal@hotmail.com>
2003-01-23 14:43 ` Tomasz Myrta <jasiek@klaster.net>
2003-07-22 17:07 slow query Markus Bertheau <twanger@bluetwanger.de>
2003-07-22 17:56 ` Re: slow query Markus Bertheau <twanger@bluetwanger.de>
2003-07-23 15:32 ` Re: slow query Markus Bertheau <twanger@bluetwanger.de>
2007-04-05 03:17 slow query Sumeet <asumeet@gmail.com>
2007-04-05 05:11 ` Re: slow query A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-04-05 05:27 ` Re: slow query Tom Lane <tgl@sss.pgh.pa.us>
2007-04-05 05:36 ` Re: slow query A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-04-05 05:47 ` Re: slow query Tom Lane <tgl@sss.pgh.pa.us>
2007-04-05 13:33 ` Re: slow query Andrew Sullivan <ajs@crankycanuck.ca>
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