Delete query takes exorbitant amount of time - Mailing list pgsql-performance

v8.0.1 on a Sun v20Z running gentoo linux, 1 cpu, 1GB Ram, 1 10k scsi
disk

I have a (fairly) newly rebuilt database. In the last month it has
undergone extensive testing, hence thousands of inserts and deletes in
the table in question. After each mass unload/load cycle, I vacuum full
analyze verbose.

I tried to build a test case to isolate the issue, but the problem does
not manifest itself, so I think I have somehow made postgresql angry. I
could drop the whole db and start over, but I am interested in not
reproducing this issue.

Here is the statement:

orfs=# explain analyze DELETE FROM int_sensor_meas_type WHERE
id_meas_type IN (SELECT * FROM meas_type_ids);
                                                         QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=11.53..42.06 rows=200 width=6) (actual
time=1.564..2.840 rows=552 loops=1)
   Hash Cond: ("outer".id_meas_type = "inner".id_meas_type)
   ->  Seq Scan on int_sensor_meas_type  (cost=0.00..25.36 rows=636
width=10) (actual time=0.005..0.828 rows=748 loops=1)
   ->  Hash  (cost=11.03..11.03 rows=200 width=4) (actual
time=1.131..1.131 rows=0 loops=1)
         ->  HashAggregate  (cost=11.03..11.03 rows=200 width=4) (actual
time=0.584..0.826 rows=552 loops=1)
               ->  Seq Scan on meas_type_ids  (cost=0.00..9.42 rows=642
width=4) (actual time=0.002..0.231 rows=552 loops=1)
 Total runtime: 2499616.216 ms
(7 rows)

Yes, that's *40 minutes*. It drives cpu (as viewed in top) to 99%+ for
the entire duration of the query, but %mem hangs at 1% or lower.

meas_type_ids is a temp table with the id's I want to nuke. Here is a
similar query behaving as expected:

orfs=# explain analyze DELETE FROM int_station_sensor   WHERE id_sensor
IN (SELECT * FROM sensor_ids);
                                                        QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=4.18..21.13 rows=272 width=6) (actual
time=0.479..0.847 rows=169 loops=1)
   Hash Cond: ("outer".id_sensor = "inner".id_sensor)
   ->  Seq Scan on int_station_sensor  (cost=0.00..11.49 rows=549
width=10) (actual time=0.007..0.265 rows=267 loops=1)
   ->  Hash  (cost=3.68..3.68 rows=200 width=4) (actual
time=0.325..0.325 rows=0 loops=1)
         ->  HashAggregate  (cost=3.68..3.68 rows=200 width=4) (actual
time=0.177..0.256 rows=169 loops=1)
               ->  Seq Scan on sensor_ids  (cost=0.00..3.14 rows=214
width=4) (actual time=0.003..0.057 rows=169 loops=1)
 Total runtime: 1.340 ms
(7 rows)


I have posted my tables, data and test cases here:
http://ccl.cens.nau.edu/~kan4/testing/long-delete


Where do I go from here?


Thanks in advance,
--
Karim Nassar
Department of Computer Science
Box 15600, College of Engineering and Natural Sciences
Northern Arizona University,  Flagstaff, Arizona 86011
Office: (928) 523-5868 -=- Mobile: (928) 699-9221