poor performance involving a small table

От: Colton A Smith
Тема: poor performance involving a small table
Дата: ,
Msg-id: Pine.GSO.4.62.0505261052180.25866@hydra3
(см: обсуждение, исходный текст)
Ответы: Re: poor performance involving a small table  (Bricklen Anderson)
Re: poor performance involving a small table  (Christopher Kings-Lynne)
Список: pgsql-performance

Скрыть дерево обсуждения

poor performance involving a small table  (Colton A Smith, )
 Re: poor performance involving a small table  (Bricklen Anderson, )
 Re: poor performance involving a small table  (Christopher Kings-Lynne, )
 Re: poor performance involving a small table  (, )

Hi:

  I have a table called sensors:

                                     Table "public.sensor"
      Column      |           Type           |                    Modifiers
-----------------+--------------------------+-------------------------------------------------
  sensor_id       | integer                  | not null default
nextval('sensor_id_seq'::text)
  sensor_model_id | integer                  | not null
  serial_number   | character varying(50)    | not null
  purchase_date   | timestamp with time zone | not null
  variable_id     | integer                  | not null
  datalink_id     | integer                  | not null
  commentary      | text                     |
Indexes:
     "sensor_pkey" PRIMARY KEY, btree (sensor_id)
Foreign-key constraints:
     "datalink_id_exists" FOREIGN KEY (datalink_id) REFERENCES
datalink(datalink_id) ON DELETE RESTRICT
     "sensor_model_id_exists" FOREIGN KEY (sensor_model_id) REFERENCES
sensor_model(sensor_model_id) ON DELETE RESTRICT
     "variable_id_exists" FOREIGN KEY (variable_id) REFERENCES
variable(variable_id) ON DELETE RESTRICT


Currently, it has only 19 rows.  But when I try to delete a row, it takes
forever.  I tried restarting the server.  I tried a full vacuum to no
avail.  I tried the following:

explain analyze delete from sensor where sensor_id = 12;
                                            QUERY PLAN
------------------------------------------------------------------------------------------------
  Seq Scan on sensor  (cost=0.00..1.25 rows=1 width=6) (actual
time=0.055..0.068 rows=1 loops=1)
    Filter: (sensor_id = 12)
  Total runtime: 801641.333 ms
(3 rows)

Can anybody help me out?  Thanks so much!


В списке pgsql-performance по дате сообщения:

От: Tobias Brox
Дата:
Сообщение: Re: Index on a NULL-value
От: Bruno Wolff III
Дата:
Сообщение: Re: Index on a NULL-value