Slow deletes

From: Edmund Dengler <edmundd(at)eSentire(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Slow deletes
Date: 2002-08-13 02:21:48
Message-ID: Pine.BSO.4.44.0208122218140.26341-100000@cyclops4.esentire.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Can anyone explain why these deletes are extremely slow?

====================================

=> select version();
version
---------------------------------------------------------------------
PostgreSQL 7.2.1 on i386-unknown-openbsd3.0, compiled by GCC 2.95.3
(1 row)

====================================

=> \d syslog_event
Table "syslog_event"
Column | Type | Modifiers
-----------------+--------------------------+-------------------------------------------------------
event_id | bigint | not null default nextval('syslog_event_id_seq'::text)
signature_name | text |
facility | character(10) |
priority | character(10) |
timestamp | timestamp with time zone |
host_id | bigint | not null
raw_message | text |
clean_message | text |
marked_message | text |
remote_event_id | bigint | not null
Indexes: syslog_event_event_id_idx,
syslog_event_idx_tmp,
syslog_event_timestamp_idx
Primary key: syslog_event_pkey
Unique keys: syslog_event_host_id_key
Triggers: RI_ConstraintTrigger_13220921,
RI_ConstraintTrigger_13220965,
RI_ConstraintTrigger_13220967,
syslog_event_after_insert_trg

====================================

=> \d syslog_event_pkey
Index "syslog_event_pkey"
Column | Type
----------+--------
event_id | bigint
unique btree (primary key)

====================================

=> explain delete from syslog_event where event_id = 1001;
NOTICE: QUERY PLAN:

Seq Scan on syslog_event (cost=0.00..342277.67 rows=1 width=6)

EXPLAIN

====================================

There are over 5,000,000 rows in the table. The triggers are only for
inserts or for existence constraints. There are other tables, but none
have existence constraints to this table.

I don't quite understand why, when there exists an (unique) index,
this would use a sequential scan.

(Note: I am actually trying to delete many rows, but this cost is
for a single row).

Regards,
Ed

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2002-08-13 02:34:23 Re: Slow deletes
Previous Message Tom Lane 2002-08-13 01:58:47 Re: plpgsql question