Missing index on `filecache` for `storage` and `path_hash`

⚠️ This issue respects the following points: ⚠️

• This is a bug, not a question or a configuration/webserver/proxy issue.
• This issue is not already reported on Github OR Nextcloud Community Forum (I've searched it).
• Nextcloud Server is up to date. See Maintenance and Release Schedule for supported versions.
• I agree to follow Nextcloud's Code of Conduct.

Bug description

I'm using Nextcloud with PostgreSQL. When I open a folder with many pictures the thumbnail requests kill the server. I tracked this down with slow query log to the following query which is executed for every thumbnail:

SELECT "filecache"."fileid", "storage", "path", "path_hash", "filecache"."parent", "filecache"."name", "mimetype"
, "mimepart", "size", "mtime", "storage_mtime", "encrypted", "etag", "filecache"."permissions", "checksum", "unencrypted_size", "metadata_etag", "creation_time", "upload_time", "meta"."json" AS "meta_json", "meta"."sync_token" AS "meta_sync_token" FROM "oc
_filecache" "filecache" LEFT JOIN "oc_filecache_extended" "fe" ON "filecache"."fileid" = "fe"."fileid" LEFT JOIN "oc_files_metadata" "meta" ON "filecache"."fileid" = "meta"."file_id" WHERE ("storage" = $1) AND ("path_hash" = $2);

https://explain.dalibo.com/plan/88f49gg93c8h0986

Adding the missing index fixes the issue for me:

CREATE INDEX fs_storage_path_hash ON public.oc_filecache USING btree (storage, path_hash);

I don't know if this is an issue with other database systems, but I suspect so.

Steps to reproduce

1. Activate query log in your PostgreSQL instance
2. Open a folder with many pictures to request the thumbnails
3. See high system load and the above query in the slow log

Expected behavior

Loading the thumbnails of a folder is basically instant and shouldn't show too much load on the server.

Nextcloud Server version

29

Operating system

Other

PHP engine version

PHP 8.2

Web server

Nginx

Database engine version

PostgreSQL

Is this bug present after an update or on a fresh install?

Upgraded to a MAJOR version (ex. 28 to 29)

Are you using the Nextcloud Server Encryption module?

Encryption is Disabled

What user-backends are you using?

• Default user-backend (database)
• LDAP/ Active Directory
• SSO - SAML
• Other

Configuration report

{
    "system": {
        "datadirectory": "***REMOVED SENSITIVE VALUE***",
        "logfile": "\/var\/log\/nextcloud\/nextcloud.log",
        "apps_paths": [
            {
                "path": "\/usr\/share\/webapps\/nextcloud\/apps",
                "url": "\/apps",
                "writable": false
            },
            {
                "path": "\/var\/lib\/nextcloud\/apps",
                "url": "\/wapps",
                "writable": true
            }
        ],
        "instanceid": "***REMOVED SENSITIVE VALUE***",
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "secret": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [
            "redacted.com"
        ],
        "dbtype": "pgsql",
        "version": "29.0.4.1",
        "overwrite.cli.url": "https:\/\/redacted.com",
        "overwritehost": "redacted.com",
        "overwriteprotocol": "https",
        "dbname": "***REMOVED SENSITIVE VALUE***",
        "dbhost": "***REMOVED SENSITIVE VALUE***",
        "dbtableprefix": "oc_",
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "dbpersistent": true,
        "installed": true,
        "memcache.local": "\\OC\\Memcache\\APCu",
        "memcache.distributed": "\\OC\\Memcache\\Redis",
        "memcache.locking": "\\OC\\Memcache\\Redis",
        "redis": {
            "host": "***REMOVED SENSITIVE VALUE***",
            "port": 6385
        },
        "ldapIgnoreNamingRules": false,
        "has_rebuilt_cache": true,
        "maintenance": false,
        "mail_smtpmode": "smtp",
        "mail_sendmailmode": "smtp",
        "mail_from_address": "***REMOVED SENSITIVE VALUE***",
        "mail_domain": "***REMOVED SENSITIVE VALUE***",
        "mail_smtphost": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpport": "587",
        "loglevel": 2,
        "theme": "",
        "app_install_overwrite": [
            "checksum",
            "dropit",
            "passman",
            "fulltextsearch",
            "fulltextsearch_elasticsearch",
            "files_fulltextsearch",
            "files_fulltextsearch_tesseract",
            "documentserver_community",
            "calendar",
            "extract",
            "flowupload",
            "ocdownloader",
            "quicknotes",
            "weather",
            "previewgenerator",
            "files_texteditor",
            "bruteforcesettings",
            "apporder",
            "richdocuments",
            "twofactor_u2f",
            "integration_whiteboard",
            "files_markdown",
            "richdocumentscode",
            "metadata",
            "forms"
        ],
        "trusted_proxies": "***REMOVED SENSITIVE VALUE***",
        "default_phone_region": "DE",
        "localstorage.allowsymlinks": true,
        "filesystem_check_changes": 1,
        "enable_previews": true,
        "preview_max_x": 4096,
        "preview_max_y": 4096,
        "enabledPreviewProviders": [
            "OC\\Preview\\MP3",
            "OC\\Preview\\TXT",
            "OC\\Preview\\MarkDown",
            "OC\\Preview\\OpenDocument",
            "OC\\Preview\\Krita",
            "OC\\Preview\\Movie",
            "OC\\Preview\\PDF",
            "OC\\Preview\\Imaginary"
        ],
        "preview_max_filesize_image": 100,
        "preview_max_memory": 1024,
        "preview_imaginary_url": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpauth": 1,
        "mail_smtpname": "***REMOVED SENSITIVE VALUE***",
        "mail_smtppassword": "***REMOVED SENSITIVE VALUE***",
        "maintenance_window_start": 0
    }
}

List of activated Apps

Enabled:
  - bookmarks: 14.2.4
  - bruteforcesettings: 2.9.0
  - calendar: 4.7.15
  - cloud_federation_api: 1.12.0
  - contacts: 6.0.0
  - contactsinteraction: 1.10.0
  - dav: 1.30.1
  - federatedfilesharing: 1.19.0
  - federation: 1.19.0
  - files: 2.1.0
  - files_downloadlimit: 2.0.0
  - files_external: 1.21.0
  - files_pdfviewer: 2.10.0
  - files_sharing: 1.21.0
  - files_trashbin: 1.19.0
  - files_versions: 1.22.0
  - fileslibreofficeedit: 1.1.0
  - forms: 4.2.4
  - lookup_server_connector: 1.17.0
  - notes: 4.10.1
  - notify_push: 0.7.0
  - oauth2: 1.17.0
  - photos: 2.5.0
  - polls: 7.2.0
  - previewgenerator: 5.6.0
  - provisioning_api: 1.19.0
  - recognize: 7.1.0
  - related_resources: 1.4.0
  - richdocuments: 8.4.4
  - richdocumentscode: 24.4.502
  - settings: 1.12.0
  - sharebymail: 1.19.0
  - spreed: 19.0.7
  - tasks: 0.16.0
  - theming: 2.4.0
  - twofactor_backupcodes: 1.18.0
  - twofactor_totp: 11.0.0-dev
  - twofactor_webauthn: 1.4.0
  - viewer: 2.3.0
  - workflowengine: 2.11.0
Disabled:
  - activity: 2.21.1 (installed 2.14.3)
  - admin_audit: 1.19.0
  - circles: 29.0.0-dev (installed 22.0.0)
  - comments: 1.19.0 (installed 1.15.0)
  - dashboard: 7.9.0 (installed 7.1.0)
  - encryption: 2.17.0
  - extract: 1.3.6 (installed 1.3.6)
  - files_reminders: 1.2.0 (installed 1.1.0)
  - files_texteditor: 2.15.1 (installed 2.15.1)
  - firstrunwizard: 2.18.0 (installed 2.10.0)
  - flowupload: 1.1.3 (installed 1.1.3)
  - logreader: 2.14.0 (installed 2.6.0)
  - metadata: 0.19.0 (installed 0.19.0)
  - nextcloud_announcements: 1.18.0 (installed 1.10.0)
  - notifications: 2.17.0 (installed 2.9.0)
  - password_policy: 1.19.0 (installed 1.11.0)
  - privacy: 1.13.0 (installed 1.5.0)
  - recommendations: 2.1.0 (installed 1.0.0)
  - serverinfo: 1.19.0 (installed 1.11.0)
  - support: 1.12.0 (installed 1.4.0)
  - survey_client: 1.17.0 (installed 1.9.0)
  - suspicious_login: 7.0.0
  - systemtags: 1.19.0 (installed 1.15.0)
  - text: 3.10.1 (installed 3.2.0)
  - updatenotification: 1.19.1 (installed 1.11.0)
  - user_ldap: 1.20.0 (installed 1.15.0)
  - user_status: 1.9.0 (installed 1.1.1)
  - weather_status: 1.9.0 (installed 1.1.0)

Nextcloud Signing status

No errors have been found.

Nextcloud Logs

No response

Additional info

No response

No repository file or test is named. Start by locating the filecache schema or database migration definitions and the code issuing the filecache lookup, then compare index support across the database systems Nextcloud supports. Done means the lookup is indexed appropriately, migrations remain compatible, and the thumbnail query no longer causes the reported PostgreSQL load.