System:
* VM on Openstack
* 16 core
* 32 GB of memory

PostgreSQL:
* 13 then 16
* shared_buffers = 8GB
* work_mem = 24MB
* effective_cache_size = 24GB
* random_page_cost = 1.1
* parallel costs by default

================================================================================
PGBadger before setting change, typical load

Report stats:
* from 10:00:53
* to   11:58:26
* 9 803 350 Number of queries
* 3h28m29s Total query duration
* peak of 2 785 queries/s at 11:46:25
* 12m40s Prepare/parse total duration
* 56m3s Bind total duration
* 2h19m45s Execute total duration
* 0 vacuum/analyze
* 0 temp file
* 9 828 Total number of sessions
* 12d22h8m58s Total duration of sessions
* 1m53s Average duration of sessions
* 997 Average queries per session

Database:
* oc_filecache: 1GB of HEAP, at least 7GB of indexes

Total duration  Times executed  Min duration    Max duration    Avg duration
1h2m            1,546,729       0ms             364ms           2ms

  SELECT "filecache"."fileid", "storage", "path", "path_hash", "filecache"."parent",
    "name", "mimetype", "mimepart", "size", "mtime", "storage_mtime", "encrypted",
    "etag", "permissions", "checksum", "metadata_etag", "creation_time", "upload_time"
  FROM "oc_filecache" "filecache"
  LEFT JOIN "oc_filecache_extended" "fe" on "filecache"."fileid" = "fe"."fileid"
  WHERE ("storage" = ?) and ("path_hash" = ?);


Total duration  Times executed  Min duration    Max duration    Avg duration
26m14s          27,549          17ms            179ms           57ms

  SELECT "id", "numeric_id", "available", "last_checked"
  FROM "oc_storages"
  WHERE "id" = ?;


Total duration  Times executed  Min duration    Max duration    Avg duration
10m48s          63,392          0ms             1s64ms          10ms

  UPDATE "oc_filecache"
  SET "size" = ?
  WHERE ("fileid" = ?) and (("size" <> ?) or ("size" is null));


Total duration  Times executed  Min duration    Max duration    Avg duration
7m8s            9,207           0ms             1s995ms         46ms

  COMMIT;


Total duration  Times executed  Min duration    Max duration    Avg duration
4m48s           12,852          0ms             4m20s           22ms

  SELECT count(*)
  FROM "oc_filecache"
  WHERE "storage" = ?;


Total duration  Times executed  Min duration    Max duration    Avg duration
2m42s           7,768           0ms             478ms           20ms

  INSERT INTO "oc_filecache" ("permissions", "size", "mtime", "storage_mtime",
                              "mimepart", "mimetype", "etag", "checksum", "path_hash",
                              "path", "parent", "name", "storage")
  VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); 


Total duration  Times executed  Min duration    Max duration    Avg duration
2m26s           117,796         0ms             2s775ms         1ms

  SELECT sum("size") AS "f1", min("size") AS "f2"
  FROM "oc_filecache"
  WHERE ("storage" = ?) and ("parent" = ?); 


Total duration  Times executed  Min duration    Max duration    Avg duration
2m5s            40,731          0ms             2s784ms         3ms

  UPDATE "oc_jobs"
  SET "reserved_at" = ?, "last_checked" = ?
  WHERE ("id" = ?) and ("reserved_at" = ?) and ("last_checked" = ?);


Total duration  Times executed  Min duration    Max duration    Avg duration
1m59s           3,251,667       0ms             208ms           0ms

  SELECT "storage_id", "root_id", "user_id", "mount_point", "mount_id", "f"."path"
  FROM "oc_mounts" "m"
  INNER JOIN "oc_filecache" "f" on "m"."root_id" = "f"."fileid"
  WHERE ("storage_id" = ?) and ("user_id" = ?); 


Total duration  Times executed  Min duration    Max duration    Avg duration
1m33s           5,912           0ms             283ms           15ms

  INSERT INTO "oc_filecache" ("mimepart", "mimetype", "size", "mtime", "storage_mtime",
                              "permissions", "etag", "path_hash", "path", "parent",
                              "name", "storage")
  VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);