From: | Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> |
---|---|
To: | Thomas Mueller <news-exp-jul05(at)tmueller(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Simple delete takes hours |
Date: | 2005-03-04 22:27:15 |
Message-ID: | 20050304141859.X74108@megazone.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | PFC | 2005-03-04 23:20:54 | Re: Postgres performance |
Previous Message | Bruno Wolff III | 2005-03-04 21:54:10 | Re: date - date returns integer? |