Re: Simple delete takes hours

Every time a row is removed from pwd_name, the ON DELETE CASCADE trigger
will look in pwd_name_rev if there is a row to delete... Does it have an
index on pwd_name_rev( rev_of ) ? If not you'll get a full table scan for
every row deleted in pwd_name...

On Thu, 03 Mar 2005 22:44:58 +0100, Thomas Mueller
<news-exp-jul05(at)tmueller(dot)com> 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.
>
>
> Any help is appreciated.
>
>
> Thomas
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>