"Galantucci Giovanni" <[EMAIL PROTECTED]> wrote: > > No, I perform a single DELETE for about 80000/100000 rows at a time. > > 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 > everything went ok. > > 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. > > Do you think it could be reasonable?
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:[EMAIL PROTECTED] > Inviato: martedì 18 settembre 2007 18.19 > A: Galantucci Giovanni > Cc: pgsql-performance@postgresql.org > Oggetto: Re: [PERFORM] R: DELETE queries slow down > > > > In response to "Galantucci Giovanni" <[EMAIL PROTECTED]>: > > > > > I perform simple INSERT and simple where-clause DELETE. > > > I also force a commit after every DELETE. > > > > Do you mean that you delete 1 row at a time? This is slower than > > batching your deletes. > > > > > My two tables are about these: > > > > > > TABLE_A > > > Column_1 | column2 | ....... > > > > > > TABLE_B > > > Column_1B foreign key references TABLE_A(column_1) on delete cascade | > > ......... > > > > > > Every row in TABLE_B is also present in TABLE_A, but the contrary is not > > true. > > > 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 > > lasts for minutes, with cpu usage on 99,9%. > > > I tried also to perform a VACUUM after each DELETE, but had no benefits. > > > 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. > > > > Are you unable to provide these details? (i.e. output of explain, the > > actual table schema, actual queries) Without them, the question is > > very vague and difficult to give advice on. > > > > If the planner comes up with the same plan whether running fast or slow, > > the question is what part of that plan is no longer valid (what part's > > actual time no longer matches it's predicted time) > > > > > 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 > > adjust its statistics and its query planner more quickly? > > > > See: > > http://www.postgresql.org/docs/8.2/static/sql-analyze.html > > which also has links to other information on this topic. > > > > If you can demonstrate that the statistics are stale, you might benefit > > from manual analyze after large operations. > > > > > My other doubt is that the foreign key on TABLE_B is a problem when I try > > to delete from TABLE_A, and postgres tries to find nonexistent constrained > > rows on TABLE_B. > > > > It's quite possible, considering the fact that you seem to be CPU bound. > > > > > > > > -----Messaggio originale----- > > > Da: Gregory Stark [mailto:[EMAIL PROTECTED] > > > Inviato: lunedì 17 settembre 2007 12.22 > > > A: Heikki Linnakangas > > > Cc: Galantucci Giovanni; pgsql-performance@postgresql.org > > > Oggetto: Re: DELETE queries slow down > > > > > > "Heikki Linnakangas" <[EMAIL PROTECTED]> writes: > > > > > > > Galantucci Giovanni wrote: > > > > > > > >> For 1 or 2 hours we update only one table, and everything goes ok, where > > > >> DELETE last at most 6 or 7 seconds. > > > >> > > > >> Then for a minute we do INSERT on both table, and everything continue > > > >> going ok, with DELETE that last about 10 seconds. > > > >> > > > >> From that moment on, DELETES become timeless, and last for 240 and more > > > >> seconds! > > > > > > What do the inserts and deletes actually look like? Are there subqueries or > > > joins or are they just inserting values and deleting simple where clauses? > > > > > > And are these in autocommit mode or are you running multiple commands in a > > > single transaction? > > > > > > Generally it's faster to run more commands in a single transaction but what > > > I'm worried about is that you may have a transaction open which you aren't > > > committing for a long time. This can stop vacuum from being able to clean up > > > dead space and if it's in the middle of a query can actually cause vacuum to > > > get stuck waiting for the query to finish using the page it's using. > > > > -- > > Bill Moran > > Collaborative Fusion Inc. > > http://people.collaborativefusion.com/~wmoran/ > > > > [EMAIL PROTECTED] > > Phone: 412-422-3463x4023 > > > Internet Email Confidentiality Footer > ----------------------------------------------------------------------------------------------------- > La presente comunicazione, con le informazioni in essa contenute e ogni > documento o file allegato, e' rivolta unicamente alla/e persona/e cui e' > indirizzata ed alle altre da questa autorizzata/e a riceverla. Se non siete i > destinatari/autorizzati siete avvisati che qualsiasi azione, copia, > comunicazione, 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 questa comunicazione per errore, vi preghiamo di darne > immediata notizia al mittente e di distruggere il messaggio originale e ogni > file allegato senza farne copia alcuna o riprodurne in alcun modo il > contenuto. > > This e-mail and its attachments are intended for the addressee(s) only and > are confidential and/or may contain legally privileged information. If you > have received this message by mistake or are not one of the addressees above, > 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 > occurred. > ----------------------------------------------------------------------------------------------------- > > > > > > > > -- Bill Moran Collaborative Fusion Inc. [EMAIL PROTECTED] Phone: 412-422-3463x4023 ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
