poor performance involving a small table

From: Colton A Smith <smith(at)cs(dot)utk(dot)edu>
To: pgsql-performance(at)postgresql(dot)org
Subject: poor performance involving a small table
Date: 2005-05-26 14:57:53
Message-ID: Pine.GSO.4.62.0505261052180.25866@hydra3
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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!

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Sam Mason 2005-05-26 15:22:03 Optimising queries involving unions
Previous Message Christopher Kings-Lynne 2005-05-26 01:40:08 Re: [PORTS] Which library has these symbols? -- Eureka