[PERFORM] poor performance involving a small table

2005-05-30 Thread Colton A Smith


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!

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings


Re: [PERFORM] poor performance involving a small table

2005-05-30 Thread Bricklen Anderson

Colton A Smith wrote:


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!



I'd say the obvious issue would be your foreign keys slowing things down. Have 
you analyzed the referenced tables, and indexed the columns on the referenced 
tables?


--
___

This e-mail may be privileged and/or confidential, and the sender does
not waive any related rights and obligations. Any distribution, use or
copying of this e-mail or the information it contains by other than an
intended recipient is unauthorized. If you received this e-mail in
error, please advise me (by return e-mail or otherwise) immediately.
___

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings


Re: [PERFORM] poor performance involving a small table

2005-05-30 Thread andrew
Colton A Smith [EMAIL PROTECTED] wrote ..


   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)


Do you have some foreign keys pointing in the other direction? In other words, 
is there another table such that a delete on sensors causing a delete (or a 
check of some key) in another table? EXPLAIN doesn't show these. And that might 
be a big table missing an index.

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match