X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 8868952818 for ; Thu, 26 May 2005 11:57:55 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 48794-09 for ; Thu, 26 May 2005 14:57:51 +0000 (GMT) Received: from klutz.cs.utk.edu (klutz.cs.utk.edu [160.36.56.50]) by svr1.postgresql.org (Postfix) with ESMTP id 7DC2052813 for ; Thu, 26 May 2005 11:57:50 -0300 (ADT) Received: from localhost (klutz [127.0.0.1]) by klutz.cs.utk.edu (Postfix) with ESMTP id 9747F400E3 for ; Thu, 26 May 2005 10:57:55 -0400 (EDT) Received: from klutz.cs.utk.edu ([127.0.0.1]) by localhost (klutz [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 12626-05 for ; Thu, 26 May 2005 10:57:54 -0400 (EDT) Received: from hydra3.cs.utk.edu (hydra3.cs.utk.edu [160.36.56.165]) by klutz.cs.utk.edu (Postfix) with ESMTP id 3A77E400B6 for ; Thu, 26 May 2005 10:57:54 -0400 (EDT) Received: by hydra3.cs.utk.edu (Postfix, from userid 2805) id A5BEE8F9E; Thu, 26 May 2005 10:57:53 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by hydra3.cs.utk.edu (Postfix) with ESMTP id 71CA68673 for ; Thu, 26 May 2005 10:57:53 -0400 (EDT) Date: Thu, 26 May 2005 10:57:53 -0400 (EDT) From: Colton A Smith X-X-Sender: smith@hydra3 To: pgsql-performance@postgresql.org Subject: poor performance involving a small table Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Virus-Scanned: by amavisd-new at cs.utk.edu by ClamAV and McAfee X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200505/443 X-Sequence-Number: 12607 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!