Gateway Timeout error when moving a document in a large team site

[vviers]

Describe the current behavior

The issue is very similar to #1005 and #824

When trying to move a document from one workspace to another, the SQL query can timeout if the parent org of the workspaces has a large number of members.

The suboptimal SQL query is here :

grist-core/app/gen-server/lib/homedb/HomeDBManager.ts

Lines 2311 to 2323 in 8990cd2

	const docQuery = this._doc(scope, {
	manager,
	markPermissions: Permissions.OWNER
	})
	.leftJoinAndSelect('docs.aclRules', 'acl_rules')
	.leftJoinAndSelect('acl_rules.group', 'doc_groups')
	.leftJoinAndSelect('doc_groups.memberUsers', 'doc_users')
	.leftJoinAndSelect('workspaces.aclRules', 'workspace_acl_rules')
	.leftJoinAndSelect('workspace_acl_rules.group', 'workspace_groups')
	.leftJoinAndSelect('workspace_groups.memberUsers', 'workspace_users')
	.leftJoinAndSelect('orgs.aclRules', 'org_acl_rules')
	.leftJoinAndSelect('org_acl_rules.group', 'org_groups')
	.leftJoinAndSelect('org_groups.memberUsers', 'org_users');

Steps to reproduce

1. Have a large team site with lots of users (several hundreds)
2. Try to move a document from one workspace to another

Describe the expected behavior

No response

Where have you encountered this bug?

• On docs.getgrist.com
• On a self-hosted instance

Instance information (when self-hosting only)

• Grist instance:
  • Version: 1.2.0
  • Installation mode: kubernetes.
  • Architecture: multi-workers

[fflorent]

The suboptimal SQL query is here :

I am more prudent about this assertion. I agree that this query (but also this one) are currently our best guesses.

This still should be reproduced in an appropriate environment and measured properly.