From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Thomas Mueller <news-exp-jul05@tmueller.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Simple delete takes hours
Date: Fri, 4 Mar 2005 14:27:15 -0800 (PST)
Message-ID: <20050304141859.X74108@megazone.bigpanda.com> (raw)
In-Reply-To: <d0807h$vuu$1@sea.gmane.org>
References: <d0807h$vuu$1@sea.gmane.org>
On Thu, 3 Mar 2005, Thomas Mueller wrote:
> 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.
It's going to remove rows in pwd_name_rev based on the rev_of not
description (and you really should make sure to have an index on rev_of).
Without being able to see triggers and rules on the tables, I can't tell
if it's even legal to remove the rows with description=1 from
pwd_name_rev, but it isn't with just the constraints defined above.
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sszabo@megazone.bigpanda.com, news-exp-jul05@tmueller.com
Subject: Re: Simple delete takes hours
In-Reply-To: <20050304141859.X74108@megazone.bigpanda.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