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)
[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