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
