Date: Wed, 19 Sep 2007 07:14:51 -0400
From: Bill Moran <wmoran@collaborativefusion.com>
To: "Galantucci Giovanni" <Giovanni.Galantucci@italtel.it>
Cc: pgsql-performance@postgresql.org
Subject: Re: R: R: DELETE queries slow down
Message-Id: <20070919071451.9ba7f1b4.wmoran@collaborativefusion.com>
In-Reply-To: <AF4EB29748E178458A0067661112724B0FCF78@BESONE.corp.dom>
References: <20070918121845.19008a51.wmoran@collaborativefusion.com>
	<AF4EB29748E178458A0067661112724B0FCF78@BESONE.corp.dom>
Organization: Collaborative Fusion Inc.
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

"Galantucci Giovanni" <Giovanni.Galantucci@italtel.it> wrote:
>
> No, I perform a single DELETE for about 80000/100000 rows at a time.
>=20
> Yesterday I tried to raise the parameter default_statistics_target on the=
 file postgresql.conf, setting it to 50 (previously it was set to 10) and e=
verything went ok.
>=20
> It seems that postgres needs some time to adapt itself to sudden changes =
in the way I use the DB, maybe to adapt its planner to the new way of use. =
I think that tuning this parameter could be enough to help postgres update =
it's planner faster.
>=20
> Do you think it could be reasonable?=20

Based on the information you've given and the responses you've made,
I think you're as likely to roll a 1d6 and get the right solution as
anything else.

Good luck.

> -----Messaggio originale-----
> Da: Bill Moran [mailto:wmoran@collaborativefusion.com]=20
> Inviato: marted=EC 18 settembre 2007 18.19
> A: Galantucci Giovanni
> Cc: pgsql-performance@postgresql.org
> Oggetto: Re: [PERFORM] R: DELETE queries slow down
>=20
> =20
>=20
> In response to "Galantucci Giovanni" <Giovanni.Galantucci@italtel.it>:
>=20
> =20
>=20
> > I perform simple INSERT and simple where-clause DELETE.
>=20
> > I also force a commit after every DELETE.
>=20
> =20
>=20
> Do you mean that you delete 1 row at a time?  This is slower than
>=20
> batching your deletes.
>=20
> =20
>=20
> > My two tables are about these:
>=20
> >=20
>=20
> > TABLE_A
>=20
> > Column_1 | column2 | .......
>=20
> >=20
>=20
> > TABLE_B
>=20
> > Column_1B foreign key references TABLE_A(column_1) on delete cascade | =
.........
>=20
> >=20
>=20
> > Every row in TABLE_B is also present in TABLE_A, but the contrary is no=
t true.
>=20
> > After hours in which I insert and delete only on TABLE_A (everything ok=
), I start inserting also on TABLE_B, exploiting the constrain on column_1B=
. After the first DELETE I perform on both tables, each following DELETE la=
sts for minutes, with cpu usage on 99,9%.
>=20
> > I tried also to perform a VACUUM after each DELETE, but had no benefits.
>=20
> > Even the EXPLAIN ANALYZE of the DELETE shows no changes with respect to=
 the previous DELETEs: it uses an index on column_1 of TABLE_A.
>=20
> =20
>=20
> Are you unable to provide these details?  (i.e. output of explain, the
>=20
> actual table schema, actual queries)  Without them, the question is
>=20
> very vague and difficult to give advice on.
>=20
> =20
>=20
> If the planner comes up with the same plan whether running fast or slow,
>=20
> the question is what part of that plan is no longer valid (what part's
>=20
> actual time no longer matches it's predicted time)
>=20
> =20
>=20
> > My doubt is that the query planner is not enough fast to follow sudden =
changes in the way I use the DB, is there a way in which I can help it to a=
djust its statistics and its query planner more quickly?
>=20
> =20
>=20
> See:
>=20
> http://www.postgresql.org/docs/8.2/static/sql-analyze.html
>=20
> which also has links to other information on this topic.
>=20
> =20
>=20
> If you can demonstrate that the statistics are stale, you might benefit
>=20
> from manual analyze after large operations.
>=20
> =20
>=20
> > My other doubt is that the foreign key on TABLE_B is a problem when I t=
ry to delete from TABLE_A, and postgres tries to find nonexistent constrain=
ed rows on TABLE_B.
>=20
> =20
>=20
> It's quite possible, considering the fact that you seem to be CPU bound.
>=20
> =20
>=20
> >=20
>=20
> > -----Messaggio originale-----
>=20
> > Da: Gregory Stark [mailto:stark@enterprisedb.com]=20
>=20
> > Inviato: luned=EC 17 settembre 2007 12.22
>=20
> > A: Heikki Linnakangas
>=20
> > Cc: Galantucci Giovanni; pgsql-performance@postgresql.org
>=20
> > Oggetto: Re: DELETE queries slow down
>=20
> >=20
>=20
> > "Heikki Linnakangas" <heikki@enterprisedb.com> writes:
>=20
> >=20
>=20
> > > Galantucci Giovanni wrote:
>=20
> > >
>=20
> > >> For 1 or 2 hours we update only one table, and everything goes ok, w=
here
>=20
> > >> DELETE last at most 6 or 7 seconds.
>=20
> > >>=20
>=20
> > >> Then for a minute we do INSERT on both table, and everything continue
>=20
> > >> going ok, with DELETE that last about 10 seconds.
>=20
> > >>=20
>=20
> > >> From that moment on, DELETES become timeless, and last for 240 and m=
ore
>=20
> > >> seconds!=20
>=20
> >=20
>=20
> > What do the inserts and deletes actually look like? Are there subquerie=
s or
>=20
> > joins or are they just inserting values and deleting simple where claus=
es?
>=20
> >=20
>=20
> > And are these in autocommit mode or are you running multiple commands i=
n a
>=20
> > single transaction?
>=20
> >=20
>=20
> > Generally it's faster to run more commands in a single transaction but =
what
>=20
> > I'm worried about is that you may have a transaction open which you are=
n't
>=20
> > committing for a long time. This can stop vacuum from being able to cle=
an up
>=20
> > dead space and if it's in the middle of a query can actually cause vacu=
um to
>=20
> > get stuck waiting for the query to finish using the page it's using.
>=20
> =20
>=20
> --=20
>=20
> Bill Moran
>=20
> Collaborative Fusion Inc.
>=20
> http://people.collaborativefusion.com/~wmoran/
>=20
> =20
>=20
> wmoran@collaborativefusion.com
>=20
> Phone: 412-422-3463x4023
>=20
>=20
> Internet Email Confidentiality Footer
> -------------------------------------------------------------------------=
----------------------------
> La presente comunicazione, con le informazioni in essa contenute e ogni d=
ocumento o file allegato, e' rivolta unicamente alla/e persona/e cui e' ind=
irizzata ed alle altre da questa autorizzata/e a riceverla. Se non siete i =
destinatari/autorizzati siete avvisati che qualsiasi azione, copia, comunic=
azione, divulgazione o simili basate sul contenuto di tali informazioni e' =
vietata e potrebbe essere contro la legge (art. 616 C.P., D.Lgs n. 196/2003=
 Codice in materia di protezione dei dati personali). Se avete ricevuto que=
sta comunicazione per errore, vi preghiamo di darne immediata notizia al mi=
ttente e di distruggere il messaggio originale e ogni file allegato senza f=
arne copia alcuna o riprodurne in alcun modo il contenuto.=20
>=20
> This e-mail and its attachments are intended for the addressee(s) only an=
d are confidential and/or may contain legally privileged information. If yo=
u have received this message by mistake or are not one of the addressees ab=
ove, you may take no action based on it, and you may not copy or show it to=
 anyone; please reply to this e-mail and point out the error which has occu=
rred.=20
> -------------------------------------------------------------------------=
----------------------------
>=20
>=20
>=20
>=20
>=20
>=20
>=20
>=20


--=20
Bill Moran
Collaborative Fusion Inc.

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023