From: Sidar López Cruz <sidarlopez@hotmail.com>
To: pgsql-performance@postgresql.org
Cc: tallen@blackducksoftware.com
Subject: Re: Very slow queries
Date: Wed, 31 Jan 2007 09:28:05 -0600
Message-ID: <BAY132-F566B5199508AF96BDBFB3CEA50@phx.gbl> (raw)
In-Reply-To: <45C0A88B.2060409@blackducksoftware.com>
>From: Ted Allen <tallen@blackducksoftware.com>
>To: Sidar López Cruz <sidarlopez@hotmail.com>
>CC: pgsql-performance@postgresql.org, chad.wagner@gmail.com
>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@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
>
>
>---------------------------(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/
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: sidarlopez@hotmail.com, tallen@blackducksoftware.com
Subject: Re: Very slow queries
In-Reply-To: <BAY132-F566B5199508AF96BDBFB3CEA50@phx.gbl>
* 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