The scenario in which this index would be needed is far from being normal. The patch D26027 that would benefit for it, is able to avoid that query in 99% of cases (it only needs such query when you want to destroy a profile picture, AND that picture was manually un-attached... why was it manually un-attached? by a spam click? by a faulty mouse?). So, the normal scenario is too small and unclear to justify a new index.

Again, expanding a PhabricatorFileAttachment to support a destruction engine to post-pone its destruction does NOT work as intended (as it's still destroyed very after the file) since the destroyObjectPermanently() is always supposed to be executed BEFORE the extensions.

OK I've explored the proposal n. 1 about expanding PhabricatorFileAttachment to support PhabricatorDestructibleInterface, but I'm just moving the problem there. The attachment is still immediately nuclearized BEFORE the extensions can do things.

When you destroy a PhabricatorFile, it seems this happens:

It seems it's necessary to be able to "get the user from a profile picture". This is not easy, since the user seems not mentioned in any obvious way from the file object, as already stated in T15407.

Plus, making the file editable by the author is the cure of T15814, so, moving that as parent task.

If we allow authors to destroy their images, we should also avoid 404 errors on them. So, the new subtask T16074.

Do you know for sure (did Fabio explicitly say so)

seems manually uploaded by an user, but it's not

This is different than T15814: Files: reduce number of orphan transformed files