Re: Very slow queries

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