pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Octavio Alvarez <alvarezp@alvarezp.ods.org>
To: pgsql-performance@postgresql.org
Subject: Slow delete times??
Date: Fri, 23 Jan 2004 16:38:30 -0800 (PST)
Message-ID: <3110.63.84.67.25.1074904710.squirrel@alvarezp.ods.org> (raw)

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



view thread (4+ messages)  latest in thread

Message-ID: <3110.63.84.67.25.1074904710.squirrel@alvarezp.ods.org>
Permalink:  ../3110.63.84.67.25.1074904710.squirrel@alvarezp.ods.org/
Also on:    postgresql.org/message-id/3110.63.84.67.25.1074904710.squirrel@alvarezp.ods.org

 · 

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: alvarezp@alvarezp.ods.org
  Subject: Re: Slow delete times??
  In-Reply-To: <3110.63.84.67.25.1074904710.squirrel@alvarezp.ods.org>

* 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