Slow deletes

Поиск
Список
Период
Сортировка
От Edmund Dengler
Тема Slow deletes
Дата
Msg-id Pine.BSO.4.44.0208122218140.26341-100000@cyclops4.esentire.com
обсуждение исходный текст
Ответы Re: Slow deletes  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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



В списке pgsql-general по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: plpgsql question
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Slow deletes