From: "Lynwood Stewart" <lynwood@nwlink.com>
Subject: Re: Simple delete takes hours
Date: Fri, 4 Mar 2005 15:35:58 -0800
Organization: Posted via Supernews, http://www.supernews.com
Message-ID: <112hrvt6fiqsg74@corp.supernews.com>
References: <d0807h$vuu$1@sea.gmane.org>
Lines: 64
To: pgsql-sql@postgresql.org

Another way to speed it up is to use bind variables.  It sped my deletes up 
by a factor of 280/1.

-- 
Lynwood
"Thomas Mueller" <news-exp-jul05@tmueller.com> wrote in message 
news:d0807h$vuu$1@sea.gmane.org...
> 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
>