Обсуждение: Very slow queries
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/
>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/
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
On 1/30/07, Sidar López Cruz <sidarlopez@hotmail.com> wrote:
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/
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/
>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/
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
>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/
>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/
On 1/31/07, Sidar López Cruz <sidarlopez@hotmail.com> wrote:
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/
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/