delete_all very slow

This project is using PostgreSQL. The data model includes a Sensor object with many associated Reading records. Periodically I need to delete all the records for a specific Sensor. This could take many minutes, like more than 15 minutes for about 30,000 records. It just didnt make sense, until I looked at the SQL that was being generated.

First of all, I made sure it was safe to use .delete_all rather than .destroy_all. The latter calls the ActiveRecord callbacks (e.g. after_delete) for each record which would really slow things down. Since I'm sure there's no such callbacks in my model, nor corresponding :dependent => :destroy, its safe to use .delete_all

When I call

sensor.readings.delete_all

the SQL generated looks something like this:

DELETE FROM "readings" WHERE "readings"."id" IN (10860663, 10860662, 10860661, 10860660, 10860659, 10860658, 10860657, 10860656, 10860655, 10860654,... [thousands more...]

And this takes an inordinate amount of time, like more than 15 minutes for 30,000 records! I see this absurd performance both on my OSX development environment and on the Linux production server. Perhaps there's a database configuration setting the relieve it (e.g. give it more memory), but that seems more a bandaid than a solution.

Instead when I call

Reading.where(:sensor_id => sensor.id).delete_all

the SQL generated looks like this:

DELETE FROM "readings" WHERE "readings"."sensor_id" = 443

and takes 323.9ms. Ah, that's easy.