Content
View differences
Updated by Jens Ulferts almost 2 years ago
### Steps to reproduce
The exact reproduction steps are not yet clear. This is currently based on observations on community
1. Have some projects projects (200)
2. Have a larger number of custom values projects
3. 2. Have a larger number of users
4. Have project attributes/custom fields (most effect have those of type user)
5. 3. Go to the project index page, add the custom fields as column and sort by one or many of them
OR
1. Have a larger body of work packages
2. Have a larger number of custom values
3. Have a larger number of users
4. Have custom fields (most effect have those of type user)
5. Go to the work package index page and sort by one or many of the custom fields
### What is the buggy behavior?
* Very poor performance
### What is the expected behavior?
1. Ok performance
### Additional notes
* Appsignal: [https://appsignal.com/openproject-gmbh/sites/66b224a4d30d867bed8a1772/performance/incidents/46/samples/66b224a4d30d867bed8a1772-53228314355367163951723811820](https://appsignal.com/openproject-gmbh/sites/66b224a4d30d867bed8a1772/performance/incidents/46/samples/66b224a4d30d867bed8a1772-53228314355367163951723811820)
* As the problem occurred when robots tried to index the page, the links on the table headers should receive "nofollow" attributes (Done in [https://github.com/opf/openproject/pull/16458)](https://github.com/opf/openproject/pull/16458))
The problem seems to stem from the order being carried out by individual subselects. In the case of user custom fields, 3 subselects are used for sorting by each custom field. And to satisfy postgresql, the same 3 subselects are used in the selects again. So when sorting by 3 custom fields, 18 subselects are in place. Those seem to be executed individually by posgresql leading to the poor performance. The SQL looks similar to this:
```SQL
SELECT (SELECT lastname user_cv_lastname FROM users cv_user
WHERE cv_user.id = (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
WHERE cv_sort.customized_type='Project'
AND cv_sort.customized_id=projects.id
AND cv_sort.custom_field_id=353
AND cv_sort.value <> ''
AND cv_sort.value IS NOT NULL
LIMIT 1)
LIMIT 1)
, (SELECT firstname user_cv_firstname FROM users cv_user
WHERE cv_user.id = (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
WHERE cv_sort.customized_type='Project'
AND cv_sort.customized_id=projects.id
AND cv_sort.custom_field_id=353
AND cv_sort.value <> ''
AND cv_sort.value IS NOT NULL
LIMIT 1)
LIMIT 1)
, (SELECT id user_cv_id FROM users cv_user
WHERE cv_user.id = (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
WHERE cv_sort.customized_type='Project'
AND cv_sort.customized_id=projects.id
AND cv_sort.custom_field_id=353
AND cv_sort.value <> ''
AND cv_sort.value IS NOT NULL
LIMIT 1)
LIMIT 1)
, (SELECT lastname user_cv_lastname FROM users cv_user
WHERE cv_user.id = (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
WHERE cv_sort.customized_type='Project'
AND cv_sort.customized_id=projects.id
AND cv_sort.custom_field_id=389
AND cv_sort.value <> ''
AND cv_sort.value IS NOT NULL
LIMIT 1)
LIMIT 1)
, (SELECT firstname user_cv_firstname FROM users cv_user
WHERE cv_user.id = (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
WHERE cv_sort.customized_type='Project'
AND cv_sort.customized_id=projects.id
AND cv_sort.custom_field_id=389
AND cv_sort.value <> ''
AND cv_sort.value IS NOT NULL
LIMIT 1)
LIMIT 1)
, (SELECT id user_cv_id FROM users cv_user
WHERE cv_user.id = (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
WHERE cv_sort.customized_type='Project'
AND cv_sort.customized_id=projects.id
AND cv_sort.custom_field_id=389
AND cv_sort.value <> ''
AND cv_sort.value IS NOT NULL
LIMIT 1)
LIMIT 1)
, (SELECT lastname user_cv_lastname FROM users cv_user
WHERE cv_user.id = (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
WHERE cv_sort.customized_type='Project'
AND cv_sort.customized_id=projects.id
AND cv_sort.custom_field_id=388
AND cv_sort.value <> ''
AND cv_sort.value IS NOT NULL
LIMIT 1)
LIMIT 1)
, (SELECT firstname user_cv_firstname FROM users cv_user
WHERE cv_user.id = (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
WHERE cv_sort.customized_type='Project'
AND cv_sort.customized_id=projects.id
AND cv_sort.custom_field_id=388
AND cv_sort.value <> ''
AND cv_sort.value IS NOT NULL
LIMIT 1)
LIMIT 1)
, (SELECT id user_cv_id FROM users cv_user
WHERE cv_user.id = (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
WHERE cv_sort.customized_type='Project'
AND cv_sort.customized_id=projects.id
AND cv_sort.custom_field_id=388
AND cv_sort.value <> ''
AND cv_sort.value IS NOT NULL
LIMIT 1)
LIMIT 1)
, projects.*, wiki.filesize AS wiki_required_space, wp.filesize AS work_package_required_space, repos.required_storage_bytes AS repositories_required_space, (COALESCE(wiki.filesize, 0) + COALESCE(wp.filesize, 0) + COALESCE(repos.required_storage_bytes, 0)) AS required_disk_space, "activity"."latest_activity_at" FROM projects projects LEFT JOIN (SELECT wiki.project_id, SUM(wiki_attached.filesize) AS filesize FROM wikis wiki JOIN wiki_pages pages ON pages.wiki_id = wiki.id JOIN attachments wiki_attached ON (wiki_attached.container_id = pages.id AND wiki_attached.container_type = 'WikiPage') GROUP BY wiki.project_id) wiki ON projects.id = wiki.project_id LEFT JOIN (SELECT wp.project_id, SUM(wp_attached.filesize) AS filesize FROM work_packages wp JOIN attachments wp_attached ON (wp_attached.container_id = wp.id AND wp_attached.container_type = 'WorkPackage') GROUP BY wp.project_id) wp ON projects.id = wp.project_id LEFT JOIN repositories repos ON repos.project_id = projects.id LEFT JOIN ( SELECT project_id, MAX(updated_at) latest_activity_at
FROM ( SELECT project_id project_id, MAX(work_packages.updated_at) updated_at
FROM work_packages
WHERE work_packages.updated_at IS NOT NULL
GROUP BY project_id
UNION ALL SELECT id project_id, MAX(projects.updated_at) updated_at
FROM projects
WHERE projects.updated_at IS NOT NULL
GROUP BY project_id
UNION ALL SELECT project_id project_id, MAX(changesets.committed_on) updated_at
FROM repositories
LEFT OUTER JOIN changesets
ON repositories.id =
changesets.repository_id
WHERE changesets.committed_on IS NOT NULL
GROUP BY project_id
UNION ALL SELECT project_id project_id, MAX(news.updated_at) updated_at
FROM news
WHERE news.updated_at IS NOT NULL
GROUP BY project_id
UNION ALL SELECT project_id project_id, MAX(wiki_pages.updated_at) updated_at
FROM wikis
LEFT OUTER JOIN wiki_pages
ON wikis.id =
wiki_pages.wiki_id
WHERE wiki_pages.updated_at IS NOT NULL
GROUP BY project_id
UNION ALL SELECT project_id project_id, MAX(messages.updated_at) updated_at
FROM forums
LEFT OUTER JOIN messages
ON forums.id =
messages.forum_id
WHERE messages.updated_at IS NOT NULL
GROUP BY project_id
UNION ALL SELECT project_id project_id, MAX(time_entries.updated_at) updated_at
FROM time_entries
WHERE time_entries.updated_at IS NOT NULL
GROUP BY project_id
UNION ALL SELECT project_id project_id, MAX(meetings.updated_at) updated_at
FROM meetings
WHERE meetings.updated_at IS NOT NULL
GROUP BY project_id
UNION ALL SELECT project_id project_id, MAX(budgets.updated_at) updated_at
FROM budgets
WHERE budgets.updated_at IS NOT NULL
GROUP BY project_id
) activity
GROUP BY project_id
) activity ON projects.id = activity.project_id WHERE (projects.active IN ('t'))
ORDER BY (SELECT lastname user_cv_lastname FROM users cv_user
WHERE cv_user.id = (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
WHERE cv_sort.customized_type='Project'
AND cv_sort.customized_id=projects.id
AND cv_sort.custom_field_id=353
AND cv_sort.value <> ''
AND cv_sort.value IS NOT NULL
LIMIT 1)
LIMIT 1)
asc, (SELECT firstname user_cv_firstname FROM users cv_user
WHERE cv_user.id = (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
WHERE cv_sort.customized_type='Project'
AND cv_sort.customized_id=projects.id
AND cv_sort.custom_field_id=353
AND cv_sort.value <> ''
AND cv_sort.value IS NOT NULL
LIMIT 1)
LIMIT 1)
asc, (SELECT id user_cv_id FROM users cv_user
WHERE cv_user.id = (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
WHERE cv_sort.customized_type='Project'
AND cv_sort.customized_id=projects.id
AND cv_sort.custom_field_id=353
AND cv_sort.value <> ''
AND cv_sort.value IS NOT NULL
LIMIT 1)
LIMIT 1)
asc, (SELECT lastname user_cv_lastname FROM users cv_user
WHERE cv_user.id = (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
WHERE cv_sort.customized_type='Project'
AND cv_sort.customized_id=projects.id
AND cv_sort.custom_field_id=389
AND cv_sort.value <> ''
AND cv_sort.value IS NOT NULL
LIMIT 1)
LIMIT 1)
asc, (SELECT firstname user_cv_firstname FROM users cv_user
WHERE cv_user.id = (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
WHERE cv_sort.customized_type='Project'
AND cv_sort.customized_id=projects.id
AND cv_sort.custom_field_id=389
AND cv_sort.value <> ''
AND cv_sort.value IS NOT NULL
LIMIT 1)
LIMIT 1)
asc, (SELECT id user_cv_id FROM users cv_user
WHERE cv_user.id = (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
WHERE cv_sort.customized_type='Project'
AND cv_sort.customized_id=projects.id
AND cv_sort.custom_field_id=389
AND cv_sort.value <> ''
AND cv_sort.value IS NOT NULL
LIMIT 1)
LIMIT 1)
asc, (SELECT lastname user_cv_lastname FROM users cv_user
WHERE cv_user.id = (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
WHERE cv_sort.customized_type='Project'
AND cv_sort.customized_id=projects.id
AND cv_sort.custom_field_id=388
AND cv_sort.value <> ''
AND cv_sort.value IS NOT NULL
LIMIT 1)
LIMIT 1)
asc, (SELECT firstname user_cv_firstname FROM users cv_user
WHERE cv_user.id = (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
WHERE cv_sort.customized_type='Project'
AND cv_sort.customized_id=projects.id
AND cv_sort.custom_field_id=388
AND cv_sort.value <> ''
AND cv_sort.value IS NOT NULL
LIMIT 1)
LIMIT 1)
asc, (SELECT id user_cv_id FROM users cv_user
WHERE cv_user.id = (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
WHERE cv_sort.customized_type='Project'
AND cv_sort.customized_id=projects.id
AND cv_sort.custom_field_id=388
AND cv_sort.value <> ''
AND cv_sort.value IS NOT NULL
LIMIT 1)
LIMIT 1)
asc, "projects"."id" DESC LIMIT 500 OFFSET 1
```
This can be reworked with CTEs to e.g.
```SQL
WITH cv_353 AS (
(SELECT cv_user.*, custom_values.customized_id FROM users cv_user
JOIN custom_values ON
CAST(custom_values.value AS decimal(60,3)) = cv_user.id
AND custom_values.customized_type='Project'
AND custom_values.custom_field_id=353)
),
cv_389 AS (
(SELECT cv_user.*, custom_values.customized_id FROM users cv_user
JOIN custom_values ON
CAST(custom_values.value AS decimal(60,3)) = cv_user.id
AND custom_values.customized_type='Project'
AND custom_values.custom_field_id=389)
),
cv_388 AS (
(SELECT cv_user.*, custom_values.customized_id FROM users cv_user
JOIN custom_values ON
CAST(custom_values.value AS decimal(60,3)) = cv_user.id
AND custom_values.customized_type='Project'
AND custom_values.custom_field_id=388)
)
SELECT (SELECT lastname user_cv_lastname FROM cv_353
WHERE cv_353.customized_id=projects.id
LIMIT 1)
, (SELECT firstname user_cv_firstname FROM cv_353
WHERE cv_353.customized_id=projects.id
LIMIT 1)
, (SELECT id user_cv_id FROM cv_353
WHERE cv_353.customized_id=projects.id
LIMIT 1)
, (SELECT lastname user_cv_lastname FROM cv_389
WHERE cv_389.customized_id=projects.id
LIMIT 1)
, (SELECT firstname user_cv_firstname FROM cv_389
WHERE cv_389.customized_id=projects.id
LIMIT 1)
, (SELECT id user_cv_id FROM cv_389
WHERE cv_389.customized_id=projects.id
LIMIT 1)
, (SELECT lastname user_cv_lastname FROM cv_388
WHERE cv_388.customized_id=projects.id
LIMIT 1)
, (SELECT firstname user_cv_firstname FROM cv_388
WHERE cv_388.customized_id=projects.id
LIMIT 1)
, (SELECT id user_cv_id FROM cv_388
WHERE cv_388.customized_id=projects.id
LIMIT 1)
, projects.*, wiki.filesize AS wiki_required_space, wp.filesize AS work_package_required_space, repos.required_storage_bytes AS repositories_required_space, (COALESCE(wiki.filesize, 0) + COALESCE(wp.filesize, 0) + COALESCE(repos.required_storage_bytes, 0)) AS required_disk_space, "activity"."latest_activity_at" FROM projects projects LEFT JOIN (SELECT wiki.project_id, SUM(wiki_attached.filesize) AS filesize FROM wikis wiki JOIN wiki_pages pages ON pages.wiki_id = wiki.id JOIN attachments wiki_attached ON (wiki_attached.container_id = pages.id AND wiki_attached.container_type = 'WikiPage') GROUP BY wiki.project_id) wiki ON projects.id = wiki.project_id LEFT JOIN (SELECT wp.project_id, SUM(wp_attached.filesize) AS filesize FROM work_packages wp JOIN attachments wp_attached ON (wp_attached.container_id = wp.id AND wp_attached.container_type = 'WorkPackage') GROUP BY wp.project_id) wp ON projects.id = wp.project_id LEFT JOIN repositories repos ON repos.project_id = projects.id LEFT JOIN ( SELECT project_id, MAX(updated_at) latest_activity_at
FROM ( SELECT project_id project_id, MAX(work_packages.updated_at) updated_at
FROM work_packages
WHERE work_packages.updated_at IS NOT NULL
GROUP BY project_id
UNION ALL SELECT id project_id, MAX(projects.updated_at) updated_at
FROM projects
WHERE projects.updated_at IS NOT NULL
GROUP BY project_id
UNION ALL SELECT project_id project_id, MAX(changesets.committed_on) updated_at
FROM repositories
LEFT OUTER JOIN changesets
ON repositories.id =
changesets.repository_id
WHERE changesets.committed_on IS NOT NULL
GROUP BY project_id
UNION ALL SELECT project_id project_id, MAX(news.updated_at) updated_at
FROM news
WHERE news.updated_at IS NOT NULL
GROUP BY project_id
UNION ALL SELECT project_id project_id, MAX(wiki_pages.updated_at) updated_at
FROM wikis
LEFT OUTER JOIN wiki_pages
ON wikis.id =
wiki_pages.wiki_id
WHERE wiki_pages.updated_at IS NOT NULL
GROUP BY project_id
UNION ALL SELECT project_id project_id, MAX(messages.updated_at) updated_at
FROM forums
LEFT OUTER JOIN messages
ON forums.id =
messages.forum_id
WHERE messages.updated_at IS NOT NULL
GROUP BY project_id
UNION ALL SELECT project_id project_id, MAX(time_entries.updated_at) updated_at
FROM time_entries
WHERE time_entries.updated_at IS NOT NULL
GROUP BY project_id
UNION ALL SELECT project_id project_id, MAX(meetings.updated_at) updated_at
FROM meetings
WHERE meetings.updated_at IS NOT NULL
GROUP BY project_id
UNION ALL SELECT project_id project_id, MAX(budgets.updated_at) updated_at
FROM budgets
WHERE budgets.updated_at IS NOT NULL
GROUP BY project_id
) activity
GROUP BY project_id
) activity ON projects.id = activity.project_id WHERE (projects.active IN ('t'))
ORDER BY (SELECT lastname user_cv_lastname FROM cv_353
WHERE cv_353.customized_id=projects.id
LIMIT 1)
asc, (SELECT firstname user_cv_firstname FROM cv_353
WHERE cv_353.customized_id=projects.id
LIMIT 1)
asc, (SELECT id user_cv_id FROM cv_353
WHERE cv_353.customized_id=projects.id
LIMIT 1)
asc, (SELECT lastname user_cv_lastname FROM cv_389
WHERE cv_389.customized_id=projects.id
LIMIT 1)
asc, (SELECT firstname user_cv_firstname FROM cv_389
WHERE cv_389.customized_id=projects.id
LIMIT 1)
asc, (SELECT id user_cv_id FROM cv_389
WHERE cv_389.customized_id=projects.id
LIMIT 1)
asc, (SELECT lastname user_cv_lastname FROM cv_388
WHERE cv_388.customized_id=projects.id
LIMIT 1)
asc, (SELECT firstname user_cv_firstname FROM cv_388
WHERE cv_388.customized_id=projects.id
LIMIT 1)
asc, (SELECT id user_cv_id FROM cv_388
WHERE cv_388.customized_id=projects.id
LIMIT 1)
asc, "projects"."id" DESC LIMIT 250 OFFSET 1
```
Or with joins like this:
```SQL
SELECT cv_353.lastname
, cv_353.firstname
, cv_353.id
, cv_389.lastname
, cv_389.firstname
, cv_389.id
, cv_388.lastname
, cv_388.firstname
, cv_388.id
, projects.*, wiki.filesize AS wiki_required_space, wp.filesize AS work_package_required_space, repos.required_storage_bytes AS repositories_required_space, (COALESCE(wiki.filesize, 0) + COALESCE(wp.filesize, 0) + COALESCE(repos.required_storage_bytes, 0)) AS required_disk_space, "activity"."latest_activity_at"
FROM projects projects
LEFT JOIN (
(SELECT cv_user.*, custom_values.customized_id FROM users cv_user
JOIN custom_values ON
CAST(custom_values.value AS decimal(60,3)) = cv_user.id
AND custom_values.customized_type='Project'
AND custom_values.custom_field_id=389)
) cv_389 ON cv_389.customized_id=projects.id
LEFT JOIN (
(SELECT cv_user.*, custom_values.customized_id FROM users cv_user
JOIN custom_values ON
CAST(custom_values.value AS decimal(60,3)) = cv_user.id
AND custom_values.customized_type='Project'
AND custom_values.custom_field_id=388)
) cv_388 ON cv_388.customized_id=projects.id
LEFT JOIN (
(SELECT cv_user.*, custom_values.customized_id FROM users cv_user
JOIN custom_values ON
CAST(custom_values.value AS decimal(60,3)) = cv_user.id
AND custom_values.customized_type='Project'
AND custom_values.custom_field_id=353)
) cv_353 ON cv_353.customized_id=projects.id
LEFT JOIN (SELECT wiki.project_id, SUM(wiki_attached.filesize) AS filesize FROM wikis wiki JOIN wiki_pages pages ON pages.wiki_id = wiki.id JOIN attachments wiki_attached ON (wiki_attached.container_id = pages.id AND wiki_attached.container_type = 'WikiPage') GROUP BY wiki.project_id) wiki ON projects.id = wiki.project_id LEFT JOIN (SELECT wp.project_id, SUM(wp_attached.filesize) AS filesize FROM work_packages wp JOIN attachments wp_attached ON (wp_attached.container_id = wp.id AND wp_attached.container_type = 'WorkPackage') GROUP BY wp.project_id) wp ON projects.id = wp.project_id LEFT JOIN repositories repos ON repos.project_id = projects.id LEFT JOIN ( SELECT project_id, MAX(updated_at) latest_activity_at
FROM ( SELECT project_id project_id, MAX(work_packages.updated_at) updated_at
FROM work_packages
WHERE work_packages.updated_at IS NOT NULL
GROUP BY project_id
UNION ALL SELECT id project_id, MAX(projects.updated_at) updated_at
FROM projects
WHERE projects.updated_at IS NOT NULL
GROUP BY project_id
UNION ALL SELECT project_id project_id, MAX(changesets.committed_on) updated_at
FROM repositories
LEFT OUTER JOIN changesets
ON repositories.id =
changesets.repository_id
WHERE changesets.committed_on IS NOT NULL
GROUP BY project_id
UNION ALL SELECT project_id project_id, MAX(news.updated_at) updated_at
FROM news
WHERE news.updated_at IS NOT NULL
GROUP BY project_id
UNION ALL SELECT project_id project_id, MAX(wiki_pages.updated_at) updated_at
FROM wikis
LEFT OUTER JOIN wiki_pages
ON wikis.id =
wiki_pages.wiki_id
WHERE wiki_pages.updated_at IS NOT NULL
GROUP BY project_id
UNION ALL SELECT project_id project_id, MAX(messages.updated_at) updated_at
FROM forums
LEFT OUTER JOIN messages
ON forums.id =
messages.forum_id
WHERE messages.updated_at IS NOT NULL
GROUP BY project_id
UNION ALL SELECT project_id project_id, MAX(time_entries.updated_at) updated_at
FROM time_entries
WHERE time_entries.updated_at IS NOT NULL
GROUP BY project_id
UNION ALL SELECT project_id project_id, MAX(meetings.updated_at) updated_at
FROM meetings
WHERE meetings.updated_at IS NOT NULL
GROUP BY project_id
UNION ALL SELECT project_id project_id, MAX(budgets.updated_at) updated_at
FROM budgets
WHERE budgets.updated_at IS NOT NULL
GROUP BY project_id
) activity
GROUP BY project_id
) activity ON projects.id = activity.project_id WHERE (projects.active IN ('t'))
ORDER BY
cv_353.lastname asc
, cv_353.firstname asc
, cv_353.id asc
, cv_389.lastname asc
, cv_389.firstname asc
, cv_389.id asc
, cv_388.lastname asc
, cv_388.firstname asc
, cv_388.id asc
,
"projects"."id" DESC LIMIT 250 OFFSET 1
```
The main blocker currently is the structure of the `CustomFieldOrder` of the `ProjectQuery` and the `Query` (for work package) class. There is one of this for each and they both rely on the `CustomField::OrderStatements` module. The structure would have to be reworked for all custom fields and ordering as well as grouping will have to looked after.
Work packages are affected in the same way (at least on community). At least the solution of using a `LEFT JOIN` as done in the last projects example works to remove the problem same as for projects.
The exact reproduction steps are not yet clear. This is currently based on observations on community
1. Have some projects projects (200)
2. Have a larger number of custom values
3.
4. Have project attributes/custom fields (most effect have those of type user)
5.
OR
1. Have a larger body of work packages
2. Have a larger number of custom values
3. Have a larger number of users
4. Have custom fields (most effect have those of type user)
5. Go to the work package index page and sort by one or many of the custom fields
### What is the buggy behavior?
* Very poor performance
### What is the expected behavior?
1. Ok performance
### Additional notes
* Appsignal: [https://appsignal.com/openproject-gmbh/sites/66b224a4d30d867bed8a1772/performance/incidents/46/samples/66b224a4d30d867bed8a1772-53228314355367163951723811820](https://appsignal.com/openproject-gmbh/sites/66b224a4d30d867bed8a1772/performance/incidents/46/samples/66b224a4d30d867bed8a1772-53228314355367163951723811820)
* As the problem occurred when robots tried to index the page, the links on the table headers should receive "nofollow" attributes (Done in [https://github.com/opf/openproject/pull/16458)](https://github.com/opf/openproject/pull/16458))
The problem seems to stem from the order being carried out by individual subselects. In the case of user custom fields, 3 subselects are used for sorting by each custom field. And to satisfy postgresql, the same 3 subselects are used in the selects again. So when sorting by 3 custom fields, 18 subselects are in place. Those seem to be executed individually by posgresql leading to the poor performance. The SQL looks similar to this:
```SQL
SELECT (SELECT lastname user_cv_lastname FROM users cv_user
WHERE cv_user.id = (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
WHERE cv_sort.customized_type='Project'
AND cv_sort.customized_id=projects.id
AND cv_sort.custom_field_id=353
AND cv_sort.value <> ''
AND cv_sort.value IS NOT NULL
LIMIT 1)
LIMIT 1)
, (SELECT firstname user_cv_firstname FROM users cv_user
WHERE cv_user.id = (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
WHERE cv_sort.customized_type='Project'
AND cv_sort.customized_id=projects.id
AND cv_sort.custom_field_id=353
AND cv_sort.value <> ''
AND cv_sort.value IS NOT NULL
LIMIT 1)
LIMIT 1)
, (SELECT id user_cv_id FROM users cv_user
WHERE cv_user.id = (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
WHERE cv_sort.customized_type='Project'
AND cv_sort.customized_id=projects.id
AND cv_sort.custom_field_id=353
AND cv_sort.value <> ''
AND cv_sort.value IS NOT NULL
LIMIT 1)
LIMIT 1)
, (SELECT lastname user_cv_lastname FROM users cv_user
WHERE cv_user.id = (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
WHERE cv_sort.customized_type='Project'
AND cv_sort.customized_id=projects.id
AND cv_sort.custom_field_id=389
AND cv_sort.value <> ''
AND cv_sort.value IS NOT NULL
LIMIT 1)
LIMIT 1)
, (SELECT firstname user_cv_firstname FROM users cv_user
WHERE cv_user.id = (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
WHERE cv_sort.customized_type='Project'
AND cv_sort.customized_id=projects.id
AND cv_sort.custom_field_id=389
AND cv_sort.value <> ''
AND cv_sort.value IS NOT NULL
LIMIT 1)
LIMIT 1)
, (SELECT id user_cv_id FROM users cv_user
WHERE cv_user.id = (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
WHERE cv_sort.customized_type='Project'
AND cv_sort.customized_id=projects.id
AND cv_sort.custom_field_id=389
AND cv_sort.value <> ''
AND cv_sort.value IS NOT NULL
LIMIT 1)
LIMIT 1)
, (SELECT lastname user_cv_lastname FROM users cv_user
WHERE cv_user.id = (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
WHERE cv_sort.customized_type='Project'
AND cv_sort.customized_id=projects.id
AND cv_sort.custom_field_id=388
AND cv_sort.value <> ''
AND cv_sort.value IS NOT NULL
LIMIT 1)
LIMIT 1)
, (SELECT firstname user_cv_firstname FROM users cv_user
WHERE cv_user.id = (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
WHERE cv_sort.customized_type='Project'
AND cv_sort.customized_id=projects.id
AND cv_sort.custom_field_id=388
AND cv_sort.value <> ''
AND cv_sort.value IS NOT NULL
LIMIT 1)
LIMIT 1)
, (SELECT id user_cv_id FROM users cv_user
WHERE cv_user.id = (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
WHERE cv_sort.customized_type='Project'
AND cv_sort.customized_id=projects.id
AND cv_sort.custom_field_id=388
AND cv_sort.value <> ''
AND cv_sort.value IS NOT NULL
LIMIT 1)
LIMIT 1)
, projects.*, wiki.filesize AS wiki_required_space, wp.filesize AS work_package_required_space, repos.required_storage_bytes AS repositories_required_space, (COALESCE(wiki.filesize, 0) + COALESCE(wp.filesize, 0) + COALESCE(repos.required_storage_bytes, 0)) AS required_disk_space, "activity"."latest_activity_at" FROM projects projects LEFT JOIN (SELECT wiki.project_id, SUM(wiki_attached.filesize) AS filesize FROM wikis wiki JOIN wiki_pages pages ON pages.wiki_id = wiki.id JOIN attachments wiki_attached ON (wiki_attached.container_id = pages.id AND wiki_attached.container_type = 'WikiPage') GROUP BY wiki.project_id) wiki ON projects.id = wiki.project_id LEFT JOIN (SELECT wp.project_id, SUM(wp_attached.filesize) AS filesize FROM work_packages wp JOIN attachments wp_attached ON (wp_attached.container_id = wp.id AND wp_attached.container_type = 'WorkPackage') GROUP BY wp.project_id) wp ON projects.id = wp.project_id LEFT JOIN repositories repos ON repos.project_id = projects.id LEFT JOIN ( SELECT project_id, MAX(updated_at) latest_activity_at
FROM ( SELECT project_id project_id, MAX(work_packages.updated_at) updated_at
FROM work_packages
WHERE work_packages.updated_at IS NOT NULL
GROUP BY project_id
UNION ALL SELECT id project_id, MAX(projects.updated_at) updated_at
FROM projects
WHERE projects.updated_at IS NOT NULL
GROUP BY project_id
UNION ALL SELECT project_id project_id, MAX(changesets.committed_on) updated_at
FROM repositories
LEFT OUTER JOIN changesets
ON repositories.id =
changesets.repository_id
WHERE changesets.committed_on IS NOT NULL
GROUP BY project_id
UNION ALL SELECT project_id project_id, MAX(news.updated_at) updated_at
FROM news
WHERE news.updated_at IS NOT NULL
GROUP BY project_id
UNION ALL SELECT project_id project_id, MAX(wiki_pages.updated_at) updated_at
FROM wikis
LEFT OUTER JOIN wiki_pages
ON wikis.id =
wiki_pages.wiki_id
WHERE wiki_pages.updated_at IS NOT NULL
GROUP BY project_id
UNION ALL SELECT project_id project_id, MAX(messages.updated_at) updated_at
FROM forums
LEFT OUTER JOIN messages
ON forums.id =
messages.forum_id
WHERE messages.updated_at IS NOT NULL
GROUP BY project_id
UNION ALL SELECT project_id project_id, MAX(time_entries.updated_at) updated_at
FROM time_entries
WHERE time_entries.updated_at IS NOT NULL
GROUP BY project_id
UNION ALL SELECT project_id project_id, MAX(meetings.updated_at) updated_at
FROM meetings
WHERE meetings.updated_at IS NOT NULL
GROUP BY project_id
UNION ALL SELECT project_id project_id, MAX(budgets.updated_at) updated_at
FROM budgets
WHERE budgets.updated_at IS NOT NULL
GROUP BY project_id
) activity
GROUP BY project_id
) activity ON projects.id = activity.project_id WHERE (projects.active IN ('t'))
ORDER BY (SELECT lastname user_cv_lastname FROM users cv_user
WHERE cv_user.id = (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
WHERE cv_sort.customized_type='Project'
AND cv_sort.customized_id=projects.id
AND cv_sort.custom_field_id=353
AND cv_sort.value <> ''
AND cv_sort.value IS NOT NULL
LIMIT 1)
LIMIT 1)
asc, (SELECT firstname user_cv_firstname FROM users cv_user
WHERE cv_user.id = (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
WHERE cv_sort.customized_type='Project'
AND cv_sort.customized_id=projects.id
AND cv_sort.custom_field_id=353
AND cv_sort.value <> ''
AND cv_sort.value IS NOT NULL
LIMIT 1)
LIMIT 1)
asc, (SELECT id user_cv_id FROM users cv_user
WHERE cv_user.id = (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
WHERE cv_sort.customized_type='Project'
AND cv_sort.customized_id=projects.id
AND cv_sort.custom_field_id=353
AND cv_sort.value <> ''
AND cv_sort.value IS NOT NULL
LIMIT 1)
LIMIT 1)
asc, (SELECT lastname user_cv_lastname FROM users cv_user
WHERE cv_user.id = (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
WHERE cv_sort.customized_type='Project'
AND cv_sort.customized_id=projects.id
AND cv_sort.custom_field_id=389
AND cv_sort.value <> ''
AND cv_sort.value IS NOT NULL
LIMIT 1)
LIMIT 1)
asc, (SELECT firstname user_cv_firstname FROM users cv_user
WHERE cv_user.id = (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
WHERE cv_sort.customized_type='Project'
AND cv_sort.customized_id=projects.id
AND cv_sort.custom_field_id=389
AND cv_sort.value <> ''
AND cv_sort.value IS NOT NULL
LIMIT 1)
LIMIT 1)
asc, (SELECT id user_cv_id FROM users cv_user
WHERE cv_user.id = (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
WHERE cv_sort.customized_type='Project'
AND cv_sort.customized_id=projects.id
AND cv_sort.custom_field_id=389
AND cv_sort.value <> ''
AND cv_sort.value IS NOT NULL
LIMIT 1)
LIMIT 1)
asc, (SELECT lastname user_cv_lastname FROM users cv_user
WHERE cv_user.id = (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
WHERE cv_sort.customized_type='Project'
AND cv_sort.customized_id=projects.id
AND cv_sort.custom_field_id=388
AND cv_sort.value <> ''
AND cv_sort.value IS NOT NULL
LIMIT 1)
LIMIT 1)
asc, (SELECT firstname user_cv_firstname FROM users cv_user
WHERE cv_user.id = (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
WHERE cv_sort.customized_type='Project'
AND cv_sort.customized_id=projects.id
AND cv_sort.custom_field_id=388
AND cv_sort.value <> ''
AND cv_sort.value IS NOT NULL
LIMIT 1)
LIMIT 1)
asc, (SELECT id user_cv_id FROM users cv_user
WHERE cv_user.id = (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
WHERE cv_sort.customized_type='Project'
AND cv_sort.customized_id=projects.id
AND cv_sort.custom_field_id=388
AND cv_sort.value <> ''
AND cv_sort.value IS NOT NULL
LIMIT 1)
LIMIT 1)
asc, "projects"."id" DESC LIMIT 500 OFFSET 1
```
This can be reworked with CTEs to e.g.
```SQL
WITH cv_353 AS (
(SELECT cv_user.*, custom_values.customized_id FROM users cv_user
JOIN custom_values ON
CAST(custom_values.value AS decimal(60,3)) = cv_user.id
AND custom_values.customized_type='Project'
AND custom_values.custom_field_id=353)
),
cv_389 AS (
(SELECT cv_user.*, custom_values.customized_id FROM users cv_user
JOIN custom_values ON
CAST(custom_values.value AS decimal(60,3)) = cv_user.id
AND custom_values.customized_type='Project'
AND custom_values.custom_field_id=389)
),
cv_388 AS (
(SELECT cv_user.*, custom_values.customized_id FROM users cv_user
JOIN custom_values ON
CAST(custom_values.value AS decimal(60,3)) = cv_user.id
AND custom_values.customized_type='Project'
AND custom_values.custom_field_id=388)
)
SELECT (SELECT lastname user_cv_lastname FROM cv_353
WHERE cv_353.customized_id=projects.id
LIMIT 1)
, (SELECT firstname user_cv_firstname FROM cv_353
WHERE cv_353.customized_id=projects.id
LIMIT 1)
, (SELECT id user_cv_id FROM cv_353
WHERE cv_353.customized_id=projects.id
LIMIT 1)
, (SELECT lastname user_cv_lastname FROM cv_389
WHERE cv_389.customized_id=projects.id
LIMIT 1)
, (SELECT firstname user_cv_firstname FROM cv_389
WHERE cv_389.customized_id=projects.id
LIMIT 1)
, (SELECT id user_cv_id FROM cv_389
WHERE cv_389.customized_id=projects.id
LIMIT 1)
, (SELECT lastname user_cv_lastname FROM cv_388
WHERE cv_388.customized_id=projects.id
LIMIT 1)
, (SELECT firstname user_cv_firstname FROM cv_388
WHERE cv_388.customized_id=projects.id
LIMIT 1)
, (SELECT id user_cv_id FROM cv_388
WHERE cv_388.customized_id=projects.id
LIMIT 1)
, projects.*, wiki.filesize AS wiki_required_space, wp.filesize AS work_package_required_space, repos.required_storage_bytes AS repositories_required_space, (COALESCE(wiki.filesize, 0) + COALESCE(wp.filesize, 0) + COALESCE(repos.required_storage_bytes, 0)) AS required_disk_space, "activity"."latest_activity_at" FROM projects projects LEFT JOIN (SELECT wiki.project_id, SUM(wiki_attached.filesize) AS filesize FROM wikis wiki JOIN wiki_pages pages ON pages.wiki_id = wiki.id JOIN attachments wiki_attached ON (wiki_attached.container_id = pages.id AND wiki_attached.container_type = 'WikiPage') GROUP BY wiki.project_id) wiki ON projects.id = wiki.project_id LEFT JOIN (SELECT wp.project_id, SUM(wp_attached.filesize) AS filesize FROM work_packages wp JOIN attachments wp_attached ON (wp_attached.container_id = wp.id AND wp_attached.container_type = 'WorkPackage') GROUP BY wp.project_id) wp ON projects.id = wp.project_id LEFT JOIN repositories repos ON repos.project_id = projects.id LEFT JOIN ( SELECT project_id, MAX(updated_at) latest_activity_at
FROM ( SELECT project_id project_id, MAX(work_packages.updated_at) updated_at
FROM work_packages
WHERE work_packages.updated_at IS NOT NULL
GROUP BY project_id
UNION ALL SELECT id project_id, MAX(projects.updated_at) updated_at
FROM projects
WHERE projects.updated_at IS NOT NULL
GROUP BY project_id
UNION ALL SELECT project_id project_id, MAX(changesets.committed_on) updated_at
FROM repositories
LEFT OUTER JOIN changesets
ON repositories.id =
changesets.repository_id
WHERE changesets.committed_on IS NOT NULL
GROUP BY project_id
UNION ALL SELECT project_id project_id, MAX(news.updated_at) updated_at
FROM news
WHERE news.updated_at IS NOT NULL
GROUP BY project_id
UNION ALL SELECT project_id project_id, MAX(wiki_pages.updated_at) updated_at
FROM wikis
LEFT OUTER JOIN wiki_pages
ON wikis.id =
wiki_pages.wiki_id
WHERE wiki_pages.updated_at IS NOT NULL
GROUP BY project_id
UNION ALL SELECT project_id project_id, MAX(messages.updated_at) updated_at
FROM forums
LEFT OUTER JOIN messages
ON forums.id =
messages.forum_id
WHERE messages.updated_at IS NOT NULL
GROUP BY project_id
UNION ALL SELECT project_id project_id, MAX(time_entries.updated_at) updated_at
FROM time_entries
WHERE time_entries.updated_at IS NOT NULL
GROUP BY project_id
UNION ALL SELECT project_id project_id, MAX(meetings.updated_at) updated_at
FROM meetings
WHERE meetings.updated_at IS NOT NULL
GROUP BY project_id
UNION ALL SELECT project_id project_id, MAX(budgets.updated_at) updated_at
FROM budgets
WHERE budgets.updated_at IS NOT NULL
GROUP BY project_id
) activity
GROUP BY project_id
) activity ON projects.id = activity.project_id WHERE (projects.active IN ('t'))
ORDER BY (SELECT lastname user_cv_lastname FROM cv_353
WHERE cv_353.customized_id=projects.id
LIMIT 1)
asc, (SELECT firstname user_cv_firstname FROM cv_353
WHERE cv_353.customized_id=projects.id
LIMIT 1)
asc, (SELECT id user_cv_id FROM cv_353
WHERE cv_353.customized_id=projects.id
LIMIT 1)
asc, (SELECT lastname user_cv_lastname FROM cv_389
WHERE cv_389.customized_id=projects.id
LIMIT 1)
asc, (SELECT firstname user_cv_firstname FROM cv_389
WHERE cv_389.customized_id=projects.id
LIMIT 1)
asc, (SELECT id user_cv_id FROM cv_389
WHERE cv_389.customized_id=projects.id
LIMIT 1)
asc, (SELECT lastname user_cv_lastname FROM cv_388
WHERE cv_388.customized_id=projects.id
LIMIT 1)
asc, (SELECT firstname user_cv_firstname FROM cv_388
WHERE cv_388.customized_id=projects.id
LIMIT 1)
asc, (SELECT id user_cv_id FROM cv_388
WHERE cv_388.customized_id=projects.id
LIMIT 1)
asc, "projects"."id" DESC LIMIT 250 OFFSET 1
```
Or with joins like this:
```SQL
SELECT cv_353.lastname
, cv_353.firstname
, cv_353.id
, cv_389.lastname
, cv_389.firstname
, cv_389.id
, cv_388.lastname
, cv_388.firstname
, cv_388.id
, projects.*, wiki.filesize AS wiki_required_space, wp.filesize AS work_package_required_space, repos.required_storage_bytes AS repositories_required_space, (COALESCE(wiki.filesize, 0) + COALESCE(wp.filesize, 0) + COALESCE(repos.required_storage_bytes, 0)) AS required_disk_space, "activity"."latest_activity_at"
FROM projects projects
LEFT JOIN (
(SELECT cv_user.*, custom_values.customized_id FROM users cv_user
JOIN custom_values ON
CAST(custom_values.value AS decimal(60,3)) = cv_user.id
AND custom_values.customized_type='Project'
AND custom_values.custom_field_id=389)
) cv_389 ON cv_389.customized_id=projects.id
LEFT JOIN (
(SELECT cv_user.*, custom_values.customized_id FROM users cv_user
JOIN custom_values ON
CAST(custom_values.value AS decimal(60,3)) = cv_user.id
AND custom_values.customized_type='Project'
AND custom_values.custom_field_id=388)
) cv_388 ON cv_388.customized_id=projects.id
LEFT JOIN (
(SELECT cv_user.*, custom_values.customized_id FROM users cv_user
JOIN custom_values ON
CAST(custom_values.value AS decimal(60,3)) = cv_user.id
AND custom_values.customized_type='Project'
AND custom_values.custom_field_id=353)
) cv_353 ON cv_353.customized_id=projects.id
LEFT JOIN (SELECT wiki.project_id, SUM(wiki_attached.filesize) AS filesize FROM wikis wiki JOIN wiki_pages pages ON pages.wiki_id = wiki.id JOIN attachments wiki_attached ON (wiki_attached.container_id = pages.id AND wiki_attached.container_type = 'WikiPage') GROUP BY wiki.project_id) wiki ON projects.id = wiki.project_id LEFT JOIN (SELECT wp.project_id, SUM(wp_attached.filesize) AS filesize FROM work_packages wp JOIN attachments wp_attached ON (wp_attached.container_id = wp.id AND wp_attached.container_type = 'WorkPackage') GROUP BY wp.project_id) wp ON projects.id = wp.project_id LEFT JOIN repositories repos ON repos.project_id = projects.id LEFT JOIN ( SELECT project_id, MAX(updated_at) latest_activity_at
FROM ( SELECT project_id project_id, MAX(work_packages.updated_at) updated_at
FROM work_packages
WHERE work_packages.updated_at IS NOT NULL
GROUP BY project_id
UNION ALL SELECT id project_id, MAX(projects.updated_at) updated_at
FROM projects
WHERE projects.updated_at IS NOT NULL
GROUP BY project_id
UNION ALL SELECT project_id project_id, MAX(changesets.committed_on) updated_at
FROM repositories
LEFT OUTER JOIN changesets
ON repositories.id =
changesets.repository_id
WHERE changesets.committed_on IS NOT NULL
GROUP BY project_id
UNION ALL SELECT project_id project_id, MAX(news.updated_at) updated_at
FROM news
WHERE news.updated_at IS NOT NULL
GROUP BY project_id
UNION ALL SELECT project_id project_id, MAX(wiki_pages.updated_at) updated_at
FROM wikis
LEFT OUTER JOIN wiki_pages
ON wikis.id =
wiki_pages.wiki_id
WHERE wiki_pages.updated_at IS NOT NULL
GROUP BY project_id
UNION ALL SELECT project_id project_id, MAX(messages.updated_at) updated_at
FROM forums
LEFT OUTER JOIN messages
ON forums.id =
messages.forum_id
WHERE messages.updated_at IS NOT NULL
GROUP BY project_id
UNION ALL SELECT project_id project_id, MAX(time_entries.updated_at) updated_at
FROM time_entries
WHERE time_entries.updated_at IS NOT NULL
GROUP BY project_id
UNION ALL SELECT project_id project_id, MAX(meetings.updated_at) updated_at
FROM meetings
WHERE meetings.updated_at IS NOT NULL
GROUP BY project_id
UNION ALL SELECT project_id project_id, MAX(budgets.updated_at) updated_at
FROM budgets
WHERE budgets.updated_at IS NOT NULL
GROUP BY project_id
) activity
GROUP BY project_id
) activity ON projects.id = activity.project_id WHERE (projects.active IN ('t'))
ORDER BY
cv_353.lastname asc
, cv_353.firstname asc
, cv_353.id asc
, cv_389.lastname asc
, cv_389.firstname asc
, cv_389.id asc
, cv_388.lastname asc
, cv_388.firstname asc
, cv_388.id asc
,
"projects"."id" DESC LIMIT 250 OFFSET 1
```
The main blocker currently is the structure of the `CustomFieldOrder` of the `ProjectQuery` and the `Query` (for work package) class. There is one of this for each and they both rely on the `CustomField::OrderStatements` module. The structure would have to be reworked for all custom fields and ordering as well as grouping will have to looked after.
Work packages are affected in the same way (at least on community). At least the solution of using a `LEFT JOIN` as done in the last projects example works to remove the problem same as for projects.