Re: Slow delete times??

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(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
>
>---------------------------(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(at)commandprompt(dot)com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL