Thread: Slow delete times??
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
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
"Octavio Alvarez" <alvarezp@alvarezp.ods.org> writes:
> 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.
I'll bet you have foreign keys referencing this table, and the
referencing columns do not have indexes. PG will let you do that
... but it makes updates and deletes horribly slow. You generally
want to add those indexes.
If they *are* indexed, check for datatype mismatches. That's
another thing that kills performance ...
regards, tom lane
First of, thanks, Tom. Although I've been very careful on this kind of things, looks like I missed one index on a referencing column. Still, I don't allow an entire delete of a table if it has referencing columns with values, so at the moment of the deletion, it has no rows at all. I checked datatype mismatches, and there are none. All my FKs are integers, like the referenced column of the referenced table. I was thinking on dropping the indexes before doing the deletes, but Joshua suggested using TRUNCATE instead. Thanks. Octavio. Tom Lane said: > "Octavio Alvarez" <alvarezp@alvarezp.ods.org> writes: >> 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. > > I'll bet you have foreign keys referencing this table, and the > referencing columns do not have indexes. PG will let you do that ... but it makes updates and deletes horribly slow. You generally want to add those indexes. > > If they *are* indexed, check for datatype mismatches. That's > another thing that kills performance ... > > regards, tom lane > -- Octavio Alvarez Piza. E-mail: alvarezp@alvarezp.ods.org -- Octavio Alvarez Piza. E-mail: alvarezp@alvarezp.ods.org