poor performance involving a small table - Mailing list pgsql-performance

From Colton A Smith
Subject poor performance involving a small table
Date
Msg-id Pine.GSO.4.62.0505261052180.25866@hydra3
Whole thread Raw
Responses Re: poor performance involving a small table
Re: poor performance involving a small table
List pgsql-performance
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 by date:

Previous
From: Tom Lane
Date:
Subject: Re: timestamp indexing
Next
From: "Brad Might"
Date:
Subject: Specific query performance problem help requested - postgresql 7.4