R: DELETE queries slow down - Mailing list pgsql-performance

I perform simple INSERT and simple where-clause DELETE.
I also force a commit after every DELETE.
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
theconstrain on column_1B. After the first DELETE I perform on both tables, each following DELETE lasts for minutes,
withcpu 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_1of TABLE_A. 
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
inwhich I can help it to adjust its statistics and its query planner more quickly? 
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
findnonexistent constrained rows on TABLE_B. 

Thank you for our help

Gianluca Galantucci

-----Messaggio originale-----
Da: Gregory Stark [mailto:stark@enterprisedb.com]
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" <heikki@enterprisedb.com> 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.

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com


Internet Email Confidentiality Footer
-----------------------------------------------------------------------------------------------------
La presente comunicazione, con le informazioni in essa contenute e ogni documento o file allegato, e' rivolta
unicamentealla/e persona/e cui e' indirizzata ed alle altre da questa autorizzata/e a riceverla. Se non siete i
destinatari/autorizzatisiete avvisati che qualsiasi azione, copia, comunicazione, divulgazione o simili basate sul
contenutodi tali informazioni e' vietata e potrebbe essere contro la legge (art. 616 C.P., D.Lgs n. 196/2003 Codice in
materiadi protezione dei dati personali). Se avete ricevuto questa comunicazione per errore, vi preghiamo di darne
immediatanotizia al mittente e di distruggere il messaggio originale e ogni file allegato senza farne copia alcuna o
riprodurnein 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
privilegedinformation. If you have received this message by mistake or are not one of the addressees above, you may
takeno action based on it, and you may not copy or show it to anyone; please reply to this e-mail and point out the
errorwhich has occurred.  
-----------------------------------------------------------------------------------------------------