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

Search Discussions

  • Joshua D. Drake at Jan 24, 2004 at 12:56 am

    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
  • Tom Lane at Jan 24, 2004 at 1:45 am

    "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 at Jan 25, 2004 at 2:13 am
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-performance @
categoriespostgresql
postedJan 24, '04 at 12:39a
activeJan 25, '04 at 2:13a
posts4
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase