Galantucci Giovanni wrote: > I have a problem with DELETE performance with postgres 7.4. You should consider upgrading. While I don't recall any particular enhancements that would directly help with this problem, 8.2 is generally faster. > I have a database with 2 great tables (about 150,000 rows) continuously > updated, with 1000 - 1200 INSERT per second and 2 or 3 huge DELETE per > minute, in which we delete almost all the rows inserted in the 2 tables > during the previous minute. > > I have a single, indexed foreign key between the 2 tables. > > > > In this scenario we have always a problem with the delete: > > 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! > > Then I can't recover from this state because INSERT continue with the > same rate and DELETE become more and more slow. I suspect that at first the tables fit in memory, and operations are therefore fast. But after they grow beyond a certain point, they no longer fit in memory, and you start doing I/O which is slow. > I do a vacuum analyze every minute. I'd suggest doing a VACUUM (no analyze) after every DELETE. Have you checked the EXPLAIN ANALYZE output of the DELETE? It might be choosing a bad plan after the table grows. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your message can get through to the mailing list cleanly
