From: Ted Allen <tallen@blackducksoftware.com>
To: Sidar López Cruz <sidarlopez@hotmail.com>
Cc: pgsql-performance@postgresql.org
Cc: chad.wagner@gmail.com
Subject: Re: Very slow queries
Date: Wed, 31 Jan 2007 09:32:43 -0500
Message-ID: <45C0A88B.2060409@blackducksoftware.com> (raw)
In-Reply-To: <BAY132-F1209F4D7B2DF92759FA080CEA50@phx.gbl>
References: <BAY132-F1209F4D7B2DF92759FA080CEA50@phx.gbl>
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)
Hope that Helps,
Ted
Sidar López Cruz wrote:
>
>
>
>> From: "Chad Wagner" <chad.wagner@gmail.com>
>> To: "Sidar López Cruz" <sidarlopez@hotmail.com>
>> CC: pgsql-performance@postgresql.org
>> Subject: Re: [PERFORM] Very slow queries
>> Date: Tue, 30 Jan 2007 17:37:17 -0500
>>
>> On 1/30/07, Sidar López Cruz <sidarlopez@hotmail.com> 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@blackducksoftware.com <mailto:tallen@blackducksoftware.com>
T +1.781.891.5100 x133
F +1.781.891.5145
http://www.blackducksoftware.com
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-performance@postgresql.org
Cc: tallen@blackducksoftware.com, sidarlopez@hotmail.com, chad.wagner@gmail.com
Subject: Re: Very slow queries
In-Reply-To: <45C0A88B.2060409@blackducksoftware.com>
* 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