pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSimple delete takes hours
5+ messages / 4 participants
[nested] [flat]
* Simple delete takes hours
@ 2005-03-03 21:44 Thomas Mueller <news-exp-jul05@tmueller.com>
2005-03-04 22:27 ` Re: Simple delete takes hours Stephan Szabo <sszabo@megazone.bigpanda.com>
2005-03-04 23:24 ` Re: Simple delete takes hours PFC <lists@boutiquenumerique.com>
2005-03-04 23:35 ` Re: Simple delete takes hours Lynwood Stewart <lynwood@nwlink.com>
0 siblings, 3 replies; 5+ messages in thread
From: Thomas Mueller @ 2005-03-03 21:44 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Simple delete takes hours
2005-03-03 21:44 Simple delete takes hours Thomas Mueller <news-exp-jul05@tmueller.com>
@ 2005-03-04 22:27 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2 siblings, 0 replies; 5+ messages in thread
From: Stephan Szabo @ 2005-03-04 22:27 UTC (permalink / raw)
To: Thomas Mueller <news-exp-jul05@tmueller.com>; +Cc: 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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Simple delete takes hours
2005-03-03 21:44 Simple delete takes hours Thomas Mueller <news-exp-jul05@tmueller.com>
@ 2005-03-04 23:24 ` PFC <lists@boutiquenumerique.com>
2005-03-05 14:00 ` Re: Simple delete takes hours Thomas Mueller <news-exp-jul05@tmueller.com>
2 siblings, 1 reply; 5+ messages in thread
From: PFC @ 2005-03-04 23:24 UTC (permalink / raw)
To: Thomas Mueller <news-exp-jul05@tmueller.com>; pgsql-sql
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@tmueller.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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Simple delete takes hours
2005-03-03 21:44 Simple delete takes hours Thomas Mueller <news-exp-jul05@tmueller.com>
2005-03-04 23:24 ` Re: Simple delete takes hours PFC <lists@boutiquenumerique.com>
@ 2005-03-05 14:00 ` Thomas Mueller <news-exp-jul05@tmueller.com>
0 siblings, 0 replies; 5+ messages in thread
From: Thomas Mueller @ 2005-03-05 14:00 UTC (permalink / raw)
To: pgsql-sql
On 05.03.2005 00:24 PFC wrote:
> 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...
Yes that's it, thanks a lot!
pwdcheck=# explain analyze delete from pwd_name where description=1;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Seq Scan on pwd_name (cost=0.00..116571.15 rows=1774250 width=6)
(actual time=9526.671..21957.920 rows=543348 loops=1)
Filter: (description = 1)
Total runtime: 35555.749 ms
(3 rows)
Is it possible to get fired triggers/called stored procedures and things
like that in an 'explain' ?
To find out why the delete is that slow I did:
pwdcheck=# explain analyze delete from pwd_name where id in
pwdcheck-# (select id from pwd_name where description=1 limit 10);
There was no hint that every deleted row leads to a full table scan.
Thomas
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Simple delete takes hours
2005-03-03 21:44 Simple delete takes hours Thomas Mueller <news-exp-jul05@tmueller.com>
@ 2005-03-04 23:35 ` Lynwood Stewart <lynwood@nwlink.com>
2 siblings, 0 replies; 5+ messages in thread
From: Lynwood Stewart @ 2005-03-04 23:35 UTC (permalink / raw)
To: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-03-05 14:00 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-03 21:44 Simple delete takes hours Thomas Mueller <news-exp-jul05@tmueller.com>
2005-03-04 22:27 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2005-03-04 23:24 ` PFC <lists@boutiquenumerique.com>
2005-03-05 14:00 ` Thomas Mueller <news-exp-jul05@tmueller.com>
2005-03-04 23:35 ` Lynwood Stewart <lynwood@nwlink.com>
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