pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Gerald Gutierrez <gutz@kalador.com>
To: pgsql-sql@postgresql.org
Subject: INSERT slowdown ...
Date: Tue, 1 May 2001 15:04:13 -0700
Message-ID: <IIEOKIIOJMELMIFMMEBFAEJJCCAA.gutz@kalador.com> (raw)


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";






Message-ID: <IIEOKIIOJMELMIFMMEBFAEJJCCAA.gutz@kalador.com>
Permalink:  ../IIEOKIIOJMELMIFMMEBFAEJJCCAA.gutz@kalador.com/
Also on:    postgresql.org/message-id/IIEOKIIOJMELMIFMMEBFAEJJCCAA.gutz@kalador.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: gutz@kalador.com
  Subject: Re: INSERT slowdown ...
  In-Reply-To: <IIEOKIIOJMELMIFMMEBFAEJJCCAA.gutz@kalador.com>
  References: <IIEOKIIOJMELMIFMMEBFAEJJCCAA.gutz@kalador.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox