Delete query takes exorbitant amount of time

From: Karim Nassar <Karim(dot)Nassar(at)acm(dot)org>
To: pgsql-performance(at)postgresql(dot)org
Subject: Delete query takes exorbitant amount of time
Date: 2005-03-25 00:10:57
Message-ID: 1111709457.9085.127.camel@k2.cet.nau.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2005-03-25 00:52:07 Re: Delete query takes exorbitant amount of time
Previous Message Matthew T. O'Connor 2005-03-24 23:58:00 Re: pg_autovacuum not having enough suction ?