Delete large amount of records and INSERT (with indexes) goes VERY slow - Mailing list pgsql-general

Hi Guys

I came accross a strange (at least to me) problem last night.
(Thankfully a good nights sleep gave me the idea to fix it)

I have a table with the following structure:

CREATE TABLE StopTelephony (
  RadAcctId BIGSERIAL PRIMARY KEY,
  UserName VARCHAR(32) DEFAULT '' NOT NULL,
  NASIPAddress INET NOT NULL,
  AcctSessionTime BIGINT,
  AcctInputOctets BIGINT,
  AcctOutputOctets BIGINT,
  CalledStationId VARCHAR(50) DEFAULT '' NOT NULL,
  CallingStationId VARCHAR(50) DEFAULT '' NOT NULL,
  AcctDelayTime SMALLINT,
  CiscoNASPort varchar(16) DEFAULT '' NOT NULL,
  h323CallOrigin varchar(10) DEFAULT '' NOT NULL,
  h323SetupTime timestamp with time zone NOT NULL,
  h323ConnectTime timestamp with time zone NOT NULL,
  h323DisconnectTime timestamp with time zone NOT NULL,
  h323DisconnectCause varchar(2) DEFAULT '' NOT NULL,
  H323RemoteAddress BOOLEAN DEFAULT false,
  H323VoiceQuality NUMERIC(2),
  h323ConfID VARCHAR(35) DEFAULT '' NOT NULL
);
create UNIQUE index stoptelephonycombo on stoptelephony (h323SetupTime,
nasipaddress, h323ConfID);

This is part of the VoIP billing code (Which I maintain) at
http://www.freeradius.org

Now, I had a approx 5million records in this table and I usually get a
combination of 250-300 SELECTS + 250-300 INSERTS per second on this table
at this size (or around 500 SELECTS per second)

Now I decided to remove all the data from the table and reimport due to a
minor parsing error in my import script (No changes to the table schema
were made) so I did DELETE FROM StopTelephony; then VACUUM ANALYZE;

I then reran my import script and found that I was getting approximately 1
INSERT every 30 secconds!!! although SELECTS were working relatively
quickly.

I then tried a VACUUM FULL; a restart of postgres, a server reboot etc etc
all to no avail. INSERTS stayed stubbonly at 1 every 30+ secconds.

It wasn't until this morning that I decided to drop and recreate the index
at which point everything went back to normal.

Is this a known _feature_ of postgres indexes that they cant recover from
large amounts of records being deleted??

I am running Postgres 7.3.2 as downloaded from
ftp://ftp.suse.com/pub/people/max/8.1
on SuSE Linux 8.1 on a P4 1.8 with 1Gb of ram...

Regards

--

Peter Nixon
http://www.peternixon.net/
PGP Key: http://www.peternixon.net/public.asc