Missing index on filecache for storage and path_hash #47901
Description
⚠️ 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
- Activate query log in your PostgreSQL instance
- Open a folder with many pictures to request the thumbnails
- 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