X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id EBBA6D1E85D for ; Sat, 24 Jan 2004 00:39:13 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 14744-06 for ; Fri, 23 Jan 2004 20:38:45 -0400 (AST) Received: from alvarezp.ods.org (unknown [200.77.201.28]) by svr1.postgresql.org (Postfix) with ESMTP id ADD51D1E868 for ; Fri, 23 Jan 2004 20:38:26 -0400 (AST) Received: (from apache@localhost) by alvarezp.ods.org (8.11.6/8.11.6) id i0O0cW632590; Fri, 23 Jan 2004 16:38:32 -0800 X-Authentication-Warning: alvarezp.ods.org: apache set sender to alvarezp@alvarezp.ods.org using -f Received: from 63.84.67.25 (SquirrelMail authenticated user alvarezp) by alvarezp.ods.org with HTTP; Fri, 23 Jan 2004 16:38:30 -0800 (PST) Message-ID: <3110.63.84.67.25.1074904710.squirrel@alvarezp.ods.org> Date: Fri, 23 Jan 2004 16:38:30 -0800 (PST) Subject: Slow delete times?? From: "Octavio Alvarez" To: pgsql-performance@postgresql.org Reply-To: alvarezp@alvarezp.ods.org User-Agent: SquirrelMail/1.4.0-1.7.x MIME-Version: 1.0 Content-Type: text/plain;charset=iso-8859-1 X-Priority: 3 Importance: Normal X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/248 X-Sequence-Number: 5422 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@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@alvarezp.ods.org