delete_all_using_limit_in_batches generates slow SQL query

[smily-ivan]

This strategy generates following SQL query:

DELETE FROM "tasks" WHERE "tasks"."id" IN (SELECT "tasks"."id" FROM "tasks" WHERE (end_at < '2023-05-13 08:28:12.447024') ORDER BY "tasks"."end_at" ASC LIMIT 500)

For big tables sorting by custom field could cause:

ActiveRecord::QueryCanceled: PG::QueryCanceled: ERROR: canceling statement due to statement timeout

So, I think it's better to rewrite the something like:

      def call(collection)
        while true
          keys = collection.limit(batch_size).pluck(collection.primary_key)
          break if keys.blank?
          collection.where(collection.primary_key => keys).delete_all
        end
      end

[Azdaroth]

@smily-ivan Are you sure that this is caused by ORDER and not WHERE statement? ordering by whatever you have in filters is the most optimal solution here, so it looks like the issue is not caused by ordering statement but rather a lack of index

[smily-ivan]

Yes, I checked.

Here is explain with order

And same query without order:

We switched to delete_all strategy, it sorts by id (using index).