Re: Slow delete times?? - Mailing list pgsql-performance

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