From: Ted Allen <tallen@xxxxxxxxxxxxxxxxxxxxx>
To: Sidar López Cruz <sidarlopez@xxxxxxxxxxx>
CC: pgsql-performance@xxxxxxxxxxxxxx, chad.wagner@xxxxxxxxx
Subject: Re: [PERFORM] Very slow queries
Date: Wed, 31 Jan 2007 09:32:43 -0500
How many rows were delete last time you ran the query?
Chad's query looks good but here is another variation that may help.
Delete From ceroriesgo.salarios Where numero_patrono In (Select
ceroriesgo.salarios.numero_patrono From ceroriesgo.salarios Left Join
ceroriesgo.patronos Using (numero_patrono) Where
ceroriesgo.patronos.numero_patrono Is Null)
Executing these query take:
Query returned successfully: 290 rows affected, 2542387 ms execution time.
I think that's too many time
Hope that Helps,
Ted
Sidar López Cruz wrote:
From: "Chad Wagner" <chad.wagner@xxxxxxxxx>
To: "Sidar López Cruz" <sidarlopez@xxxxxxxxxxx>
CC: pgsql-performance@xxxxxxxxxxxxxx
Subject: Re: [PERFORM] Very slow queries
Date: Tue, 30 Jan 2007 17:37:17 -0500
On 1/30/07, Sidar López Cruz <sidarlopez@xxxxxxxxxxx> wrote:
query: Delete From ceroriesgo.salarios Where numero_patrono Not In
(Select
numero_patrono From ceroriesgo.patronos)
Seq Scan on salarios (cost=51021.78..298803854359.95 rows=14240077
width=6)
Filter: (NOT (subplan))
SubPlan
-> Materialize (cost=51021.78..69422.58 rows=1032980 width=25)
-> Seq Scan on patronos (cost=0.00..41917.80 rows=1032980
width=25)
How many rows exist in salarios, but not in patronos? How many rows are
there in salarios?
Rows:
Patronos: 1032980
Salarios: 28480200
What does the explain look like for:
delete
from ceroriesgo.salarios s
where not exists (select 1
from ceroriesgo.patronos
where numero_patrono = s.numero_patrono);
Also, is this not a case for a foreign key with a cascade delete?
No, this is not cascade delete case because I need to delete from salarios
not from patronos.
http://www.postgresql.org/docs/8.2/static/ddl-constraints.html
--
Chad
http://www.postgresqlforums.com/
_________________________________________________________________
Charla con tus amigos en línea mediante MSN Messenger:
http://messenger.latam.msn.com/
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
--
*Edward Allen*
Software Engineer
Black Duck Software, Inc.
tallen@xxxxxxxxxxxxxxxxxxxxx <mailto:tallen@xxxxxxxxxxxxxxxxxxxxx>
T +1.781.891.5100 x133
F +1.781.891.5145
http://www.blackducksoftware.com
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
_________________________________________________________________
MSN Amor: busca tu ½ naranja http://latam.msn.com/amor/
