pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Joshua D. Drake <jd@commandprompt.com>
To: alvarezp@alvarezp.ods.org
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow delete times??
Date: Fri, 23 Jan 2004 16:55:28 -0800
Message-ID: <4011C280.3000503@commandprompt.com> (raw)
In-Reply-To: <3110.63.84.67.25.1074904710.squirrel@alvarezp.ods.org>
References: <3110.63.84.67.25.1074904710.squirrel@alvarezp.ods.org>

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




view thread (4+ messages)  latest in thread

Message-ID: <4011C280.3000503@commandprompt.com>
Permalink:  ../4011C280.3000503@commandprompt.com/
Also on:    postgresql.org/message-id/4011C280.3000503@commandprompt.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: jd@commandprompt.com, alvarezp@alvarezp.ods.org
  Subject: Re: Slow delete times??
  In-Reply-To: <4011C280.3000503@commandprompt.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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