To: pgsql-sql@postgresql.org
From: Thomas Mueller <news-exp-jul05@tmueller.com>
Subject: Simple delete takes hours
Date: Thu, 03 Mar 2005 22:44:58 +0100
Lines: 48
Message-ID: <d0807h$vuu$1@sea.gmane.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla Thunderbird 1.0 (X11/20050116)

Hi there,

I have a simple database:

CREATE TABLE pwd_description (
   id SERIAL                NOT NULL UNIQUE PRIMARY KEY,
   name varchar(50)         NOT NULL
);

CREATE TABLE pwd_name (
   id SERIAL                NOT NULL UNIQUE PRIMARY KEY,
   description integer      NOT NULL REFERENCES pwd_description(id),
   name varchar(50)         NOT NULL,
   added timestamp          DEFAULT now()
);

CREATE TABLE pwd_name_rev (
   id SERIAL                NOT NULL UNIQUE PRIMARY KEY,
   description integer      NOT NULL REFERENCES pwd_description(id),
   rev_of integer           NOT NULL REFERENCES pwd_name(id) ON DELETE 
CASCADE,
   name varchar(50)         NOT NULL
);

The indexes shouldn't matter I think.

pwd_name_rev is filled by a stored procedure and a trigger (ON INSERT) 
when something is inserted to pwd_name. Both tables contain about 
4.500.000 emtries each.

I stopped 'delete from pwd_name where description=1' after about 8 hours 
(!). The query should delete about 500.000 records.
Then I tried 'delete from pwd_name_rev where description=1' - this took 
23 seconds (!).
Then I retried the delete on pwd_name but it's running for 6 hours now.

I use PostgreSQL 7.4.7 on Linux 2.6.10. The machine is a Celeron 2 GHz 
with 512 MB RAM.

PostgreSQL should do a full table scan I think, get all records with 
description=1 and remove them - I don't understand what's happening for 
 >8 hours.


Any help is appreciated.


Thomas