pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedVery slow queries
9+ messages / 3 participants
[nested] [flat]
* Very slow queries
@ 2007-01-30 21:09 Sidar López Cruz <sidarlopez@hotmail.com>
2007-01-30 21:14 ` Re: Very slow queries Ted Allen <tallen@blackducksoftware.com>
2007-01-30 22:37 ` Re: Very slow queries Chad Wagner <chad.wagner@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Sidar López Cruz @ 2007-01-30 21:09 UTC (permalink / raw)
To: pgsql-performance
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/
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Very slow queries
2007-01-30 21:09 Very slow queries Sidar López Cruz <sidarlopez@hotmail.com>
@ 2007-01-30 21:14 ` Ted Allen <tallen@blackducksoftware.com>
2007-01-30 21:19 ` Re: Very slow queries Sidar López Cruz <sidarlopez@hotmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Ted Allen @ 2007-01-30 21:14 UTC (permalink / raw)
To: Sidar López Cruz <sidarlopez@hotmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Very slow queries
2007-01-30 21:09 Very slow queries Sidar López Cruz <sidarlopez@hotmail.com>
2007-01-30 21:14 ` Re: Very slow queries Ted Allen <tallen@blackducksoftware.com>
@ 2007-01-30 21:19 ` Sidar López Cruz <sidarlopez@hotmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Sidar López Cruz @ 2007-01-30 21:19 UTC (permalink / raw)
To: pgsql-performance; +Cc: tallen@blackducksoftware.com
>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/
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Very slow queries
2007-01-30 21:09 Very slow queries Sidar López Cruz <sidarlopez@hotmail.com>
@ 2007-01-30 22:37 ` Chad Wagner <chad.wagner@gmail.com>
2007-01-31 14:19 ` Re: Very slow queries Sidar López Cruz <sidarlopez@hotmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Chad Wagner @ 2007-01-30 22:37 UTC (permalink / raw)
To: Sidar López Cruz <sidarlopez@hotmail.com>; +Cc: pgsql-performance
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/
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Very slow queries
2007-01-30 21:09 Very slow queries Sidar López Cruz <sidarlopez@hotmail.com>
2007-01-30 22:37 ` Re: Very slow queries Chad Wagner <chad.wagner@gmail.com>
@ 2007-01-31 14:19 ` Sidar López Cruz <sidarlopez@hotmail.com>
2007-01-31 14:32 ` Re: Very slow queries Ted Allen <tallen@blackducksoftware.com>
0 siblings, 1 reply; 9+ messages in thread
From: Sidar López Cruz @ 2007-01-31 14:19 UTC (permalink / raw)
To: pgsql-performance; +Cc: chad.wagner@gmail.com
>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/
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Very slow queries
2007-01-30 21:09 Very slow queries Sidar López Cruz <sidarlopez@hotmail.com>
2007-01-30 22:37 ` Re: Very slow queries Chad Wagner <chad.wagner@gmail.com>
2007-01-31 14:19 ` Re: Very slow queries Sidar López Cruz <sidarlopez@hotmail.com>
@ 2007-01-31 14:32 ` Ted Allen <tallen@blackducksoftware.com>
2007-01-31 14:58 ` Re: Very slow queries Sidar López Cruz <sidarlopez@hotmail.com>
2007-01-31 15:28 ` Re: Very slow queries Sidar López Cruz <sidarlopez@hotmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Ted Allen @ 2007-01-31 14:32 UTC (permalink / raw)
To: Sidar López Cruz <sidarlopez@hotmail.com>; +Cc: pgsql-performance; chad.wagner@gmail.com
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Very slow queries
2007-01-30 21:09 Very slow queries Sidar López Cruz <sidarlopez@hotmail.com>
2007-01-30 22:37 ` Re: Very slow queries Chad Wagner <chad.wagner@gmail.com>
2007-01-31 14:19 ` Re: Very slow queries Sidar López Cruz <sidarlopez@hotmail.com>
2007-01-31 14:32 ` Re: Very slow queries Ted Allen <tallen@blackducksoftware.com>
@ 2007-01-31 14:58 ` Sidar López Cruz <sidarlopez@hotmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Sidar López Cruz @ 2007-01-31 14:58 UTC (permalink / raw)
To: pgsql-performance; +Cc: tallen@blackducksoftware.com
>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/
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Very slow queries
2007-01-30 21:09 Very slow queries Sidar López Cruz <sidarlopez@hotmail.com>
2007-01-30 22:37 ` Re: Very slow queries Chad Wagner <chad.wagner@gmail.com>
2007-01-31 14:19 ` Re: Very slow queries Sidar López Cruz <sidarlopez@hotmail.com>
2007-01-31 14:32 ` Re: Very slow queries Ted Allen <tallen@blackducksoftware.com>
@ 2007-01-31 15:28 ` Sidar López Cruz <sidarlopez@hotmail.com>
2007-02-01 01:29 ` Re: Very slow queries Chad Wagner <chad.wagner@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Sidar López Cruz @ 2007-01-31 15:28 UTC (permalink / raw)
To: pgsql-performance; +Cc: tallen@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/
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Very slow queries
2007-01-30 21:09 Very slow queries Sidar López Cruz <sidarlopez@hotmail.com>
2007-01-30 22:37 ` Re: Very slow queries Chad Wagner <chad.wagner@gmail.com>
2007-01-31 14:19 ` Re: Very slow queries Sidar López Cruz <sidarlopez@hotmail.com>
2007-01-31 14:32 ` Re: Very slow queries Ted Allen <tallen@blackducksoftware.com>
2007-01-31 15:28 ` Re: Very slow queries Sidar López Cruz <sidarlopez@hotmail.com>
@ 2007-02-01 01:29 ` Chad Wagner <chad.wagner@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Chad Wagner @ 2007-02-01 01:29 UTC (permalink / raw)
To: Sidar López Cruz <sidarlopez@hotmail.com>; +Cc: pgsql-performance; tallen@blackducksoftware.com
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/
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2007-02-01 01:29 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-01-30 21:09 Very slow queries Sidar López Cruz <sidarlopez@hotmail.com>
2007-01-30 21:14 ` Ted Allen <tallen@blackducksoftware.com>
2007-01-30 21:19 ` Sidar López Cruz <sidarlopez@hotmail.com>
2007-01-30 22:37 ` Chad Wagner <chad.wagner@gmail.com>
2007-01-31 14:19 ` Sidar López Cruz <sidarlopez@hotmail.com>
2007-01-31 14:32 ` Ted Allen <tallen@blackducksoftware.com>
2007-01-31 14:58 ` Sidar López Cruz <sidarlopez@hotmail.com>
2007-01-31 15:28 ` Sidar López Cruz <sidarlopez@hotmail.com>
2007-02-01 01:29 ` Chad Wagner <chad.wagner@gmail.com>
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