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)
[EMAIL PROTECTED] 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: [EMAIL PROTECTED]
---------------------------(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 - [EMAIL PROTECTED] - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
