Flows logs panel takes too long the first time we open with large number of logs

[gkielwasser]

Describe the Bug

The first time we open the logs panel in Flows there are 2 requests done:

• one with the 10 latest rows (really fast)
• one which seems to do internally a left join on "directus_activity" with a sort (terribly slow)

Request payload
GET https://api.mimelis.ch/revisions

filter[collection][_eq]: directus_flows
filter[item][_eq]: 6c952e81-186f-49c8-9a6b-55859a2697dd
filter[version][_null]: true
filter[activity][action][_eq]: create
sort: -id
limit: 1
fields[]: id
fields[]: data
fields[]: delta
fields[]: collection
fields[]: item
fields[]: activity.action
fields[]: activity.timestamp
fields[]: activity.user.id
fields[]: activity.user.email
fields[]: activity.user.first_name
fields[]: activity.user.last_name
fields[]: activity.ip
fields[]: activity.user_agent
fields[]: activity.origin

SELECT
  "directus_revisions"."id",
  "directus_revisions"."data",
  "directus_revisions"."delta",
  "directus_revisions"."collection",
  "directus_revisions"."item",
  "directus_revisions"."activity"
FROM
  "directus_revisions"
LEFT JOIN
  "directus_activity" AS "dwjuh"
ON
  "directus_revisions"."activity" = "dwjuh"."id"
WHERE
  ("directus_revisions"."collection" = "directus_flows"
    AND "directus_revisions"."item" = "$directus_flows_id"
    AND "directus_revisions"."version" IS NULL
    AND "dwjuh"."action" = "create")
ORDER BY
  "directus_revisions"."id" DESC
LIMIT
  1

To Reproduce

It is required to have lots of logs for the flow.
Ex: 28K lines will take 2 minutes to solve the request described upper.

We cannot interract with the first page before this long request is finished.

It is fine with next paginated page interraction (ex page 2).

Reason: the sorting with the left join is terribly slow.

Directus Version

10.10.5

Hosting Strategy

Self-Hosted (Docker Image)

Database

Postgre