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 AD3C9D1E868 for ; Sat, 24 Jan 2004 00:56:22 +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 13220-10 for ; Fri, 23 Jan 2004 20:55:53 -0400 (AST) Received: from hosting.commandprompt.com (215.commandprompt.com [207.173.200.215]) by svr1.postgresql.org (Postfix) with ESMTP id E20D4D1E863 for ; Fri, 23 Jan 2004 20:55:49 -0400 (AST) Received: from commandprompt.com (dsl093-038-087.pdx1.dsl.speakeasy.net [66.93.38.87]) (authenticated) by hosting.commandprompt.com (8.11.6/8.11.6) with ESMTP id i0O0tYm30618; Fri, 23 Jan 2004 16:55:34 -0800 Message-ID: <4011C280.3000503@commandprompt.com> Date: Fri, 23 Jan 2004 16:55:28 -0800 From: "Joshua D. Drake" Organization: Command Prompt, Inc. User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.6b) Gecko/20031205 Thunderbird/0.4 X-Accept-Language: en-us, en MIME-Version: 1.0 To: alvarezp@alvarezp.ods.org Cc: pgsql-performance@postgresql.org Subject: Re: Slow delete times?? References: <3110.63.84.67.25.1074904710.squirrel@alvarezp.ods.org> In-Reply-To: <3110.63.84.67.25.1074904710.squirrel@alvarezp.ods.org> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/249 X-Sequence-Number: 5423 Octavio Alvarez wrote: >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. > > Well delete is generally slow. If you want to delete the entire table (and your really sure) use truncate. J >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 > >---------------------------(end of broadcast)--------------------------- >TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > > -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL