How many rows were delete last time you ran the query?
I never delete any rows, the tables was inserted with copy command, then I
create index and I need to delete these records on ceroriesgo.salarios to
create the foreign key restriction on it.
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)
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
_________________________________________________________________
Las mejores tiendas, los precios mas bajos, entregas en todo el mundo,
YupiMSN Compras: http://latam.msn.com/compras/
