Possibly sub-ideal index design for filecache.storage column

[ChristophWurst]

How to use GitHub

• Please use the 👍 reaction to show that you are interested into the same feature.
• Please don't comment if you have no relevant information to add. It's just extra noise for everyone subscribed to this issue.
• Subscribe to receive notifications on status change and new comments.

Is your feature request related to a problem? Please describe.

As a admin/DBA of Nextcloud I monitor queries and notice a relatively simple query that is slow:

SELECT COUNT(*) FROM oc_filecache WHERE storage = ?

ANALYZE shows that the index fs_storage_mimetype is used and it takes the query around 27 seconds when scanning ~7M entries. The index has storage as first column, followed by mimetype.

create index fs_storage_mimetype
    on nextclouddev.oc_filecache (storage, mimetype);

Describe the solution you'd like

Consider an index on just storage. From simple tests, such index can retrieve the same result in around 13s. That is roughly twice as fast.

Describe alternatives you've considered

No ideas at this point.

Additional context

N/a