Received: from mail2.uniserve.com (mail2.uniserve.com [204.244.156.10]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f4252rd06274 for ; Wed, 2 May 2001 01:02:53 -0400 (EDT) (envelope-from gutz@kalador.com) Received: from van12d14.dial.uniserve.ca ([204.244.161.77] helo=gutz) by mail2.uniserve.com with smtp (Exim 3.13 #1) id 14ui9o-00018t-00 for pgsql-sql@postgresql.org; Tue, 01 May 2001 14:58:09 -0700 From: "Gerald Gutierrez" To: Subject: INSERT slowdown ... Date: Tue, 1 May 2001 15:04:13 -0700 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6700 Importance: Normal X-Archive-Number: 200105/2 X-Sequence-Number: 2799 Hi all. We have a table called DeckEJB with the follow structure and associates indices as indicated below. Our application makes use of this table by doing INSERTs into it, and then after every 741 inserts, one DELETE. We are not SELECTing from the table in our test, though in production use SELECTs will be made, infrequently, into the table. As we load test our application, the database (PostgreSQL 7.0.3 on Linux) seems to get slower and slower over several hours, until it is perhaps 1/4 of its fastest speed. When I perform a VACUUM ANALYZE on the table, everything speeds up again to its fastest speed again, but then begins to degrade again. Everytime I do a VACUUM ANALYZE, it happens over again. The number of rows in the table, however, is always between 0 and 741. The EXPLAIN of the INSERTs and DELETEs only shows that it gets a result, which is understandable. So what is the problem here? It could be because of the index updating, but why is it slowing down so much? DeckEJB Table Structure: seed, integer, not null, primary key identifier, varchar(255) card, integer Indices: deck_id_x is an index on DeckEJB( identifier ) deckejb_pkey is an index on DeckEJB( seed ) Insert Statement: INSERT INTO DeckEJB Values ( x, "Kalador", y ); -- x always increasing, y = rnd( 0, 741 ) In addition, after every 741 INSERTs, the following DELETE is performed: DELETE FROM DeckEJB WHERE identifier="Kalador";