Re: [PERFORM] poor performance involving a small table

[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

Re: [PERFORM] poor performance involving a small table

[Christopher Kings-Lynne]

 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!


Does your table have millions of dead rows?  Do you vacuum once an hour? 
 Run VACUUM FULL ANALYE sensor;


Chris


---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [PERFORM] poor performance involving a small table

[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

[PERFORM] poor performance involving a small table

[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