Thread: Very slow queries

Very slow queries

From

Sidar López Cruz

Date:

30 January 2007, 20:09:33

Check this:

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)


These query took a day to finish, how or who can improove better performance
of my PostgreSQL.

_________________________________________________________________
Charla con tus amigos en línea mediante MSN Messenger:
http://messenger.latam.msn.com/

Re: Very slow queries

From

Sidar López Cruz

Date:

30 January 2007, 20:19:51



>From: Ted Allen <tallen@blackducksoftware.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 16:14:38 -0500
>


>What indexes do those tables have?  Any?

Yes:
TABLE ceroriesgo.patronos ADD CONSTRAINT patronos_pkey PRIMARY
KEY(numero_patrono);

INDEX salarios_numero_patrono_idx  ON ceroriesgo.salarios
  USING btree (numero_patrono);



>
>Sidar López Cruz wrote:
>>Check this:
>>
>>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)
>>
>>
>>These query took a day to finish, how or who can improove better
>>performance of my PostgreSQL.
>>
>>_________________________________________________________________
>>Charla con tus amigos en línea mediante MSN Messenger:
>>http://messenger.latam.msn.com/
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 4: Have you searched our list archives?
>>
>>               http://archives.postgresql.org
>>
>
>
>--
>
>*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
>

_________________________________________________________________
Charla con tus amigos en línea mediante MSN Messenger:
http://messenger.latam.msn.com/

Re: Very slow queries

From

Ted Allen

Date:

30 January 2007, 20:46:11

What indexes do those tables have?  Any?

Sidar López Cruz wrote:
> Check this:
>
> 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)
>
>
> These query took a day to finish, how or who can improove better
> performance of my PostgreSQL.
>
> _________________________________________________________________
> Charla con tus amigos en línea mediante MSN Messenger:
> http://messenger.latam.msn.com/
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org
>


--

*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

Re: Very slow queries

From

"Chad Wagner"

Date:

30 January 2007, 21:37:29

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?

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?

http://www.postgresql.org/docs/8.2/static/ddl-constraints.html

--
Chad
http://www.postgresqlforums.com/

Re: Very slow queries

From

Sidar López Cruz

Date:

31 January 2007, 13:19:51



>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/

Re: Very slow queries

From

Ted Allen

Date:

31 January 2007, 13:32:44

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

Re: Very slow queries

From

Sidar López Cruz

Date:

31 January 2007, 13:58:11

>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@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

_________________________________________________________________
Las mejores tiendas, los precios mas bajos, entregas en todo el mundo,
YupiMSN Compras: http://latam.msn.com/compras/

Re: Very slow queries

From

Sidar López Cruz

Date:

31 January 2007, 14:28:15

>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/

Re: Very slow queries

From

"Chad Wagner"

Date:

01 February 2007, 00:30:02

On 1/31/07, Sidar López Cruz <sidarlopez@hotmail.com> wrote:

Executing these query take:
Query returned successfully: 290 rows affected, 2542387 ms execution time.
I think that's too many time

I would post the plans that you are getting, otherwise just mentioning the execution time is not very helpful. Also, yet another syntax is the UPDATE foo... FROM tab1, tab2... syntax.

http://www.postgresql.org/docs/8.2/static/sql-update.html

In any case, I thought you mentioned this was a one off query?

--
Chad
http://www.postgresqlforums.com/