Slow delete times??

From: "Octavio Alvarez" <alvarezp(at)alvarezp(dot)ods(dot)org>
To: pgsql-performance(at)postgresql(dot)org
Subject: Slow delete times??
Date: 2004-01-24 00:38:30
Message-ID: 3110.63.84.67.25.1074904710.squirrel@alvarezp.ods.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Please tell me if this timing makes sense to you for a Celeron 433 w/
RAM=256MB dedicated testing server. I expected some slowness, but not this
high.

db_epsilon=# \d t_active_subjects
Table "public.t_active_subjects"
Column | Type | Modifiers
------------------------+--------------+--------------------------------------------------------------------
id | integer | not null default
nextval('public.t_active_subjects_id_seq'::text)
old_id | integer |
ext_subject | integer | not null
ext_group | integer |
final_grade | integer |
type | character(1) |
ree | date |
borrado | boolean |
ext_active_student | integer |
sum_presences | integer |
sum_hours | integer |
Indexes: t_active_subjects_pkey primary key btree (id),
i_t_active_subjects__ext_active_student btree (ext_active_student),
i_t_active_subjects__ext_group btree (ext_group),
i_t_active_subjects__ext_subject btree (ext_subject),
i_t_active_subjects__old_id btree (old_id)
Foreign Key constraints: $4 FOREIGN KEY (ext_group) REFERENCES
t_groups(id) ON UPDATE NO ACTION ON DELETE NO ACTION,
$3 FOREIGN KEY (ext_subject) REFERENCES
t_subjects(id) ON UPDATE NO ACTION ON DELETE NO
ACTION

db_epsilon=# EXPLAIN DELETE FROM t_active_subjects;
QUERY PLAN
-------------------------------------------------------------------------
Seq Scan on t_active_subjects (cost=0.00..3391.73 rows=52373 width=6)
(1 row)

db_epsilon=# EXPLAIN ANALYZE DELETE FROM t_active_subjects;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Seq Scan on t_active_subjects (cost=0.00..3391.73 rows=52373 width=6)
(actual time=0.11..4651.82 rows=73700 loops=1)
Total runtime: 3504528.15 msec
(2 rows)

db_epsilon=# SELECT version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2
20020903 (Red Hat Linux 8.0 3.2-7)
(1 row)

[root(at)pgsql data]# cat postgresql.conf | grep -v \# | grep \=
tcpip_socket = true
fsync = false
LC_MESSAGES = 'en_US.UTF-8'
LC_MONETARY = 'en_US.UTF-8'
LC_NUMERIC = 'en_US.UTF-8'
LC_TIME = 'en_US.UTF-8'

Okay, some details:
* The query takes to run about 3,504.52815 sec for 52,373 rows, which
averages about 15 deletes per second.
* Each ext_* field is a foreign key to another table's pk.
* This is a dedicated testing server with 256 MB RAM, and is a Celeron
433 MHz. It still has enough disk space, I think: about 200 MB.
* Disk is 4 MB. I guess it must be about what, 4500 RPM?
* fsync is disabled.

I don't know what other info to provide...

Thanks in advance.

--
Octavio Alvarez Piza.
E-mail: alvarezp(at)alvarezp(dot)ods(dot)org

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Joshua D. Drake 2004-01-24 00:55:28 Re: Slow delete times??
Previous Message Christopher Browne 2004-01-23 23:30:38 Re: High Performance/High Reliability File system on SuSE64