grist-core
grist-core copied to clipboard
Published
20 hours ago •
gristlabs
gristlabs
Everything slow (especially PostgreSQL)
I am touching one organization with one document, 5 columns and about 30 rows of raw data, and every operation is very slow. Always "Still Working..." I am using a Raspberry Pi 4 and I understand that the performance is poor, but each operation is taking more than 5 seconds. there were times when Node.js was using 100% of the time, but when I ran a slow query, I found that PostgreSQL was a major factor. Is there something wrong with my setup?
My docker-compose.yml PostgreSQL 16.1
grist log
warn: TypeORM transaction slow: [async (manager) => {
const docQuery = this._doc(scope, { manager, markPermissions })
// Join the doc's ACL rules and groups/users so we can edit them.
.leftJoinAndSelect('docs.aclRules', 'acl_rules')
.leftJoinAndSelect('acl_rules.group', 'doc_groups')
.leftJoinAndSelect('doc_groups.memberUsers', 'doc_group_users')
.leftJoinAndSelect('doc_groups.memberGroups', 'doc_group_groups')
.leftJoinAndSelect('doc_group_users.logins', 'doc_user_logins')
// Join the workspace so we know what should be inherited. We will join
// the workspace member groups/users as a separate query, since
// SQL results are flattened, and multiplying the number of rows we have already
// by the number of workspace users could get excessive.
.leftJoinAndSelect('docs.workspace', 'workspace');
const queryResult = await verifyIsPermitted(docQuery);
const doc = this.unwrapQueryResult(queryResult);
// Load the workspace's member groups/users.
const workspaceQuery = this._workspace(scope, doc.workspace.id, { manager })
.leftJoinAndSelect('workspaces.aclRules', 'workspace_acl_rules')
.leftJoinAndSelect('workspace_acl_rules.group', 'workspace_groups')
.leftJoinAndSelect('workspace_groups.memberUsers', 'workspace_group_users')
.leftJoinAndSelect('workspace_groups.memberGroups', 'workspace_group_groups')
.leftJoinAndSelect('workspace_group_users.logins', 'workspace_user_logins')
// We'll need the org as well. We will join its members as a separate query, since
// SQL results are flattened, and multiplying the number of rows we have already
// by the number of org users could get excessive.
.leftJoinAndSelect('workspaces.org', 'org');
doc.workspace = (await workspaceQuery.getOne());
// Load the org's member groups/users.
let orgQuery = this.org(scope, doc.workspace.org.id, { manager })
.leftJoinAndSelect('orgs.aclRules', 'org_acl_rules')
.leftJoinAndSelect('org_acl_rules.group', 'org_groups')
.leftJoinAndSelect('org_groups.memberUsers', 'org_group_users')
.leftJoinAndSelect('org_group_users.logins', 'org_user_logins');
orgQuery = this._addFeatures(orgQuery);
doc.workspace.org = (await orgQuery.getOne());
return doc;
} undefined] { timeMs: 10001 }
PostgreSQL auto_explain
grist@grist LOG: duration: 10135.402 ms plan:
Query Text: SELECT "docs"."name" AS "docs_name", "docs"."created_at" AS "docs_created_at", "docs"."updated_at" AS "docs_updated_at", "docs"."id" AS "docs_id", "docs"."is_pinned" AS "docs_is_pinned", "docs"."url_id" AS "docs_url_id", "docs"."removed_at" AS "docs_removed_at", "docs"."grace_period_start" AS "docs_grace_period_start", "docs"."options" AS "docs_options", "docs"."usage" AS "docs_usage", "docs"."created_by" AS "docs_created_by", "docs"."trunk_id" AS "docs_trunk_id", "docs"."type" AS "docs_type", "docs"."workspace_id" AS "docs_workspace_id", "workspaces"."name" AS "workspaces_name", "workspaces"."created_at" AS "workspaces_created_at", "workspaces"."updated_at" AS "workspaces_updated_at", "workspaces"."id" AS "workspaces_id", "workspaces"."removed_at" AS "workspaces_removed_at", "workspaces"."org_id" AS "workspaces_org_id", "orgs"."name" AS "orgs_name", "orgs"."created_at" AS "orgs_created_at", "orgs"."updated_at" AS "orgs_updated_at", "orgs"."id" AS "orgs_id", "orgs"."domain" AS "orgs_domain", "orgs"."billing_account_id" AS "orgs_billing_account_id", "orgs"."host" AS "orgs_host", "orgs"."owner_id" AS "orgs_owner_id", "aliases"."org_id" AS "aliases_org_id", "aliases"."url_id" AS "aliases_url_id", "aliases"."doc_id" AS "aliases_doc_id", "aliases"."created_at" AS "aliases_created_at", "acl_rules"."id" AS "acl_rules_id", "acl_rules"."permissions" AS "acl_rules_permissions", "acl_rules"."type" AS "acl_rules_type", "acl_rules"."group_id" AS "acl_rules_group_id", "acl_rules"."workspace_id" AS "acl_rules_workspace_id", "acl_rules"."org_id" AS "acl_rules_org_id", "acl_rules"."doc_id" AS "acl_rules_doc_id", "doc_groups"."id" AS "doc_groups_id", "doc_groups"."name" AS "doc_groups_name", "doc_group_users"."id" AS "doc_group_users_id", "doc_group_users"."name" AS "doc_group_users_name", "doc_group_users"."api_key" AS "doc_group_users_api_key", "doc_group_users"."picture" AS "doc_group_users_picture", "doc_group_users"."first_login_at" AS "doc_group_users_first_login_at", "doc_group_users"."is_first_time_user" AS "doc_group_users_is_first_time_user", "doc_group_users"."options" AS "doc_group_users_options", "doc_group_users"."connect_id" AS "doc_group_users_connect_id", "doc_group_users"."ref" AS "doc_group_users_ref", "doc_group_groups"."id" AS "doc_group_groups_id", "doc_group_groups"."name" AS "doc_group_groups_name", "doc_user_logins"."id" AS "doc_user_logins_id", "doc_user_logins"."email" AS "doc_user_logins_email", "doc_user_logins"."display_email" AS "doc_user_logins_display_email", "doc_user_logins"."user_id" AS "doc_user_logins_user_id", "workspace"."name" AS "workspace_name", "workspace"."created_at" AS "workspace_created_at", "workspace"."updated_at" AS "workspace_updated_at", "workspace"."id" AS "workspace_id", "workspace"."removed_at" AS "workspace_removed_at", "workspace"."org_id" AS "workspace_org_id", (SELECT bit_or((case when array[3,1] && array[gu0.user_id,gu1.user_id,gu2.user_id,gu3.user_id] then 128 else 0 end | "acl_rules"."permissions" | case when coalesce("gu0"."user_id",gu1.user_id,gu2.user_id,gu3.user_id) is null then "acl_rules"."permissions" else 0 end)) AS "permissions" FROM "acl_rules" "acl_rules" LEFT JOIN "group_groups" "gg1" ON "gg1"."group_id" = "acl_rules"."group_id" LEFT JOIN "group_groups" "gg2" ON "gg2"."group_id" = "gg1"."subgroup_id" LEFT JOIN "group_groups" "gg3" ON "gg3"."group_id" = "gg2"."subgroup_id" LEFT JOIN "group_users" "gu3" ON "gg3"."subgroup_id" = "gu3"."group_id" LEFT JOIN "group_users" "gu2" ON "gg2"."subgroup_id" = "gu2"."group_id" LEFT JOIN "group_users" "gu1" ON "gg1"."subgroup_id" = "gu1"."group_id" LEFT JOIN "group_users" "gu0" ON "acl_rules"."group_id" = "gu0"."group_id" WHERE ("gu0"."user_id" = 5 OR "gu1"."user_id" = 5 OR "gu2"."user_id" = 5 OR "gu3"."user_id" = 5 OR "gu0"."user_id" = 3 OR "gu1"."user_id" = 3 OR "gu2"."user_id" = 3 OR "gu3"."user_id" = 3) AND "acl_rules"."doc_id" = "docs"."id") AS "docs_permissions", (SELECT bit_or((case when array[3,1] && array[gu0.user_id,gu1.user_id,gu2.user_id,gu3.user_id] then 128 else 0 end | "acl_rules"."permissions" | case when coalesce("gu0"."user_id",gu1.user_id,gu2.user_id,gu3.user_id) is null then "acl_rules"."permissions" else 0 end)) AS "permissions" FROM "acl_rules" "acl_rules" LEFT JOIN "group_groups" "gg1" ON "gg1"."group_id" = "acl_rules"."group_id" LEFT JOIN "group_groups" "gg2" ON "gg2"."group_id" = "gg1"."subgroup_id" LEFT JOIN "group_groups" "gg3" ON "gg3"."group_id" = "gg2"."subgroup_id" LEFT JOIN "group_users" "gu3" ON "gg3"."subgroup_id" = "gu3"."group_id" LEFT JOIN "group_users" "gu2" ON "gg2"."subgroup_id" = "gu2"."group_id" LEFT JOIN "group_users" "gu1" ON "gg1"."subgroup_id" = "gu1"."group_id" LEFT JOIN "group_users" "gu0" ON "acl_rules"."group_id" = "gu0"."group_id" WHERE ("gu0"."user_id" = 5 OR "gu1"."user_id" = 5 OR "gu2"."user_id" = 5 OR "gu3"."user_id" = 5 OR "gu0"."user_id" = 3 OR "gu1"."user_id" = 3 OR "gu2"."user_id" = 3 OR "gu3"."user_id" = 3) AND "acl_rules"."workspace_id" = "workspaces"."id") AS "workspaces_permissions", (SELECT bit_or((case when array[3,1] && array[gu0.user_id,gu1.user_id,gu2.user_id,gu3.user_id] then 128 else 0 end | "acl_rules"."permissions" | case when coalesce("gu0"."user_id",gu1.user_id,gu2.user_id,gu3.user_id) is null then "acl_rules"."permissions" else 0 end)) AS "permissions" FROM "acl_rules" "acl_rules" LEFT JOIN "group_groups" "gg1" ON "gg1"."group_id" = "acl_rules"."group_id" LEFT JOIN "group_groups" "gg2" ON "gg2"."group_id" = "gg1"."subgroup_id" LEFT JOIN "group_groups" "gg3" ON "gg3"."group_id" = "gg2"."subgroup_id" LEFT JOIN "group_users" "gu3" ON "gg3"."subgroup_id" = "gu3"."group_id" LEFT JOIN "group_users" "gu2" ON "gg2"."subgroup_id" = "gu2"."group_id" LEFT JOIN "group_users" "gu1" ON "gg1"."subgroup_id" = "gu1"."group_id" LEFT JOIN "group_users" "gu0" ON "acl_rules"."group_id" = "gu0"."group_id" WHERE ("gu0"."user_id" = 5 OR "gu1"."user_id" = 5 OR "gu2"."user_id" = 5 OR "gu3"."user_id" = 5 OR "gu0"."user_id" = 3 OR "gu1"."user_id" = 3 OR "gu2"."user_id" = 3 OR "gu3"."user_id" = 3) AND "acl_rules"."org_id" = "orgs"."id") AS "orgs_permissions", "docs"."id" IS NULL AND "workspaces"."removed_at" IS NOT NULL AS "workspaces_filtered_out", (SELECT "acl_rules"."permissions" AS "acl_rules_permissions" FROM "acl_rules" "acl_rules" LEFT JOIN "group_groups" "gg1" ON "gg1"."group_id" = "acl_rules"."group_id" LEFT JOIN "group_groups" "gg2" ON "gg2"."group_id" = "gg1"."subgroup_id" LEFT JOIN "group_groups" "gg3" ON "gg3"."group_id" = "gg2"."subgroup_id" LEFT JOIN "group_users" "gu3" ON "gg3"."subgroup_id" = "gu3"."group_id" LEFT JOIN "group_users" "gu2" ON "gg2"."subgroup_id" = "gu2"."group_id" LEFT JOIN "group_users" "gu1" ON "gg1"."subgroup_id" = "gu1"."group_id" LEFT JOIN "group_users" "gu0" ON "acl_rules"."group_id" = "gu0"."group_id" WHERE ("gu0"."user_id" = 5 OR "gu1"."user_id" = 5 OR "gu2"."user_id" = 5 OR "gu3"."user_id" = 5 OR "gu0"."user_id" = 3 OR "gu1"."user_id" = 3 OR "gu2"."user_id" = 3 OR "gu3"."user_id" = 3) AND "acl_rules"."doc_id" = "docs"."id" AND ("acl_rules"."permissions" & 1) = 1 LIMIT 1) AS "is_permitted" FROM "docs" "docs" LEFT JOIN "workspaces" "workspaces" ON "workspaces"."id"="docs"."workspace_id" LEFT JOIN "orgs" "orgs" ON "orgs"."id"="workspaces"."org_id" LEFT JOIN "aliases" "aliases" ON "aliases"."doc_id"="docs"."id" LEFT JOIN "acl_rules" "acl_rules" ON "acl_rules"."doc_id"="docs"."id" AND "acl_rules"."type"='AclRuleDoc' LEFT JOIN "groups" "doc_groups" ON "doc_groups"."id"="acl_rules"."group_id" LEFT JOIN "group_users" "doc_groups_doc_group_users" ON "doc_groups_doc_group_users"."group_id"="doc_groups"."id" LEFT JOIN "users" "doc_group_users" ON "doc_group_users"."id"="doc_groups_doc_group_users"."user_id" LEFT JOIN "group_groups" "doc_groups_doc_group_groups" ON "doc_groups_doc_group_groups"."group_id"="doc_groups"."id" LEFT JOIN "groups" "doc_group_groups" ON "doc_group_groups"."id"="doc_groups_doc_group_groups"."subgroup_id" LEFT JOIN "logins" "doc_user_logins" ON "doc_user_logins"."user_id"="doc_group_users"."id" LEFT JOIN "workspaces" "workspace" ON "workspace"."id"="docs"."workspace_id" WHERE ("docs"."id" = $1 OR ("aliases"."url_id" = $2 AND "aliases"."org_id" = "orgs"."id"))
Query Parameters: $1 = 'xxxxx', $2 = 'xxxxx'
Hash Right Join (cost=266.72..57132406459.05 rows=5716 width=1050)
Hash Cond: (doc_user_logins.user_id = doc_group_users.id)
-> Seq Scan on logins doc_user_logins (cost=0.00..18.10 rows=810 width=72)
-> Hash (cost=235.85..235.85 rows=2470 width=961)
-> Hash Right Join (cost=164.12..235.85 rows=2470 width=961)
Hash Cond: (doc_groups_doc_group_groups.group_id = doc_groups.id)
-> Hash Left Join (cost=38.58..77.13 rows=2260 width=40)
Hash Cond: (doc_groups_doc_group_groups.subgroup_id = doc_group_groups.id)
-> Seq Scan on group_groups doc_groups_doc_group_groups (cost=0.00..32.60 rows=2260 width=8)
-> Hash (cost=22.70..22.70 rows=1270 width=36)
-> Seq Scan on groups doc_group_groups (cost=0.00..22.70 rows=1270 width=36)
-> Hash (cost=108.19..108.19 rows=1388 width=925)
-> Hash Left Join (cost=80.40..108.19 rows=1388 width=925)
Hash Cond: (docs.workspace_id = workspace.id)
-> Hash Right Join (cost=50.60..74.73 rows=1388 width=861)
Hash Cond: ((aliases.doc_id)::text = (docs.id)::text)
Filter: (((docs.id)::text = '5q61J1afMZRVRkoYcqNMsf'::text) OR (((aliases.url_id)::text = '5q61J1afMZRVRkoYcqNMsf'::text) AND (aliases.org_id = orgs.id)))
-> Seq Scan on aliases (cost=0.00..17.80 rows=780 width=76)
-> Hash (cost=50.52..50.52 rows=7 width=785)
-> Nested Loop Left Join (cost=4.95..50.52 rows=7 width=785)
-> Nested Loop Left Join (cost=0.31..13.76 rows=2 width=460)
-> Nested Loop Left Join (cost=0.16..13.32 rows=2 width=336)
-> Seq Scan on docs (cost=0.00..1.02 rows=2 width=272)
-> Memoize (cost=0.16..8.18 rows=1 width=64)
Cache Key: docs.workspace_id
Cache Mode: logical
-> Index Scan using "PK_098656ae401f3e1a4586f47fd8e" on workspaces (cost=0.15..8.17 rows=1 width=64)
Index Cond: (id = docs.workspace_id)
-> Index Scan using "PK_9eed8bfad4c9e0dc8648e090efe" on orgs (cost=0.15..0.22 rows=1 width=124)
Index Cond: (id = workspaces.org_id)
-> Nested Loop Left Join (cost=4.64..18.36 rows=2 width=325)
-> Nested Loop Left Join (cost=4.49..17.99 rows=2 width=124)
-> Nested Loop Left Join (cost=4.33..17.49 rows=1 width=120)
-> Bitmap Heap Scan on acl_rules (cost=4.18..11.31 rows=1 width=84)
Recheck Cond: ((doc_id)::text = (docs.id)::text)
Filter: ((type)::text = 'AclRuleDoc'::text)
-> Bitmap Index Scan on acl_rules__doc_id (cost=0.00..4.18 rows=4 width=0)
Index Cond: ((doc_id)::text = (docs.id)::text)
-> Index Scan using "PK_659d1483316afb28afd3a90646e" on groups doc_groups (cost=0.15..6.17 rows=1 width=36)
Index Cond: (id = acl_rules.group_id)
-> Index Scan using group_users__group_id on group_users doc_groups_doc_group_users (cost=0.15..0.39 rows=11 width=8)
Index Cond: (group_id = doc_groups.id)
-> Index Scan using "PK_a3ffb1c0c8416b9fc6f907b7433" on users doc_group_users (cost=0.15..0.19 rows=1 width=205)
Index Cond: (id = doc_groups_doc_group_users.user_id)
-> Hash (cost=18.80..18.80 rows=880 width=64)
-> Seq Scan on workspaces workspace (cost=0.00..18.80 rows=880 width=64)
SubPlan 1
-> Aggregate (cost=3331723.60..3331723.61 rows=1 width=4)
-> Merge Right Join (cost=6767.30..3294936.52 rows=3678708 width=20)
Merge Cond: (gg3.group_id = gg2.subgroup_id)
Filter: ((gu0.user_id = 5) OR (gu1.user_id = 5) OR (gu2.user_id = 5) OR (gu3.user_id = 5) OR (gu0.user_id = 3) OR (gu1.user_id = 3) OR (gu2.user_id = 3) OR (gu3.user_id = 3))
-> Nested Loop Left Join (cost=0.31..1164.00 rows=25538 width=8)
-> Index Only Scan using "PK_22a9d9794c2e2353fd9b10cf37d" on group_groups gg3 (cost=0.15..82.06 rows=2260 width=8)
-> Index Scan using group_users__group_id on group_users gu3 (cost=0.15..0.37 rows=11 width=8)
Index Cond: (group_id = gg3.subgroup_id)
-> Materialize (cost=6766.99..19917.77 rows=732941 width=20)
-> Merge Left Join (cost=6766.99..18085.41 rows=732941 width=20)
Merge Cond: (gg2.subgroup_id = gu2.group_id)
-> Sort (cost=6608.48..6770.64 rows=64862 width=16)
Sort Key: gg2.subgroup_id
-> Merge Right Join (cost=2.86..1424.36 rows=64862 width=16)
Merge Cond: (gu0.group_id = acl_rules_1.group_id)
-> Index Scan using group_users__group_id on group_users gu0 (cost=0.15..82.06 rows=2260 width=8)
-> Materialize (cost=2.70..378.08 rows=5740 width=16)
-> Nested Loop Left Join (cost=2.70..363.73 rows=5740 width=16)
-> Nested Loop Left Join (cost=2.55..120.53 rows=508 width=16)
-> Nested Loop Left Join (cost=2.39..98.98 rows=45 width=12)
-> Index Scan using acl_rules__group_id on acl_rules acl_rules_1 (cost=0.15..60.75 rows=4 width=8)
Filter: ((doc_id)::text = (docs.id)::text)
-> Bitmap Heap Scan on group_groups gg1 (cost=2.24..9.45 rows=11 width=8)
Recheck Cond: (group_id = acl_rules_1.group_id)
-> Bitmap Index Scan on group_groups__group_id (cost=0.00..2.24 rows=11 width=0)
Index Cond: (group_id = acl_rules_1.group_id)
-> Index Scan using group_groups__group_id on group_groups gg2 (cost=0.15..0.37 rows=11 width=8)
Index Cond: (group_id = gg1.subgroup_id)
-> Index Scan using group_users__group_id on group_users gu1 (cost=0.15..0.37 rows=11 width=8)
Index Cond: (group_id = gg1.subgroup_id)
-> Sort (cost=158.51..164.16 rows=2260 width=8)
Sort Key: gu2.group_id
-> Seq Scan on group_users gu2 (cost=0.00..32.60 rows=2260 width=8)
SubPlan 2
-> Aggregate (cost=3331723.60..3331723.61 rows=1 width=4)
-> Merge Right Join (cost=6767.30..3294936.52 rows=3678708 width=20)
Merge Cond: (gg3_1.group_id = gg2_1.subgroup_id)
Filter: ((gu0_1.user_id = 5) OR (gu1_1.user_id = 5) OR (gu2_1.user_id = 5) OR (gu3_1.user_id = 5) OR (gu0_1.user_id = 3) OR (gu1_1.user_id = 3) OR (gu2_1.user_id = 3) OR (gu3_1.user_id = 3))
-> Nested Loop Left Join (cost=0.31..1164.00 rows=25538 width=8)
-> Index Only Scan using "PK_22a9d9794c2e2353fd9b10cf37d" on group_groups gg3_1 (cost=0.15..82.06 rows=2260 width=8)
-> Index Scan using group_users__group_id on group_users gu3_1 (cost=0.15..0.37 rows=11 width=8)
Index Cond: (group_id = gg3_1.subgroup_id)
-> Materialize (cost=6766.99..19917.77 rows=732941 width=20)
-> Merge Left Join (cost=6766.99..18085.41 rows=732941 width=20)
Merge Cond: (gg2_1.subgroup_id = gu2_1.group_id)
-> Sort (cost=6608.48..6770.64 rows=64862 width=16)
Sort Key: gg2_1.subgroup_id
-> Merge Right Join (cost=2.86..1424.36 rows=64862 width=16)
Merge Cond: (gu0_1.group_id = acl_rules_2.group_id)
-> Index Scan using group_users__group_id on group_users gu0_1 (cost=0.15..82.06 rows=2260 width=8)
-> Materialize (cost=2.70..378.08 rows=5740 width=16)
-> Nested Loop Left Join (cost=2.70..363.73 rows=5740 width=16)
-> Nested Loop Left Join (cost=2.55..120.53 rows=508 width=16)
-> Nested Loop Left Join (cost=2.39..98.98 rows=45 width=12)
-> Index Scan using acl_rules__group_id on acl_rules acl_rules_2 (cost=0.15..60.75 rows=4 width=8)
Filter: (workspace_id = workspaces.id)
-> Bitmap Heap Scan on group_groups gg1_1 (cost=2.24..9.45 rows=11 width=8)
Recheck Cond: (group_id = acl_rules_2.group_id)
-> Bitmap Index Scan on group_groups__group_id (cost=0.00..2.24 rows=11 width=0)
Index Cond: (group_id = acl_rules_2.group_id)
-> Index Scan using group_groups__group_id on group_groups gg2_1 (cost=0.15..0.37 rows=11 width=8)
Index Cond: (group_id = gg1_1.subgroup_id)
-> Index Scan using group_users__group_id on group_users gu1_1 (cost=0.15..0.37 rows=11 width=8)
Index Cond: (group_id = gg1_1.subgroup_id)
-> Sort (cost=158.51..164.16 rows=2260 width=8)
Sort Key: gu2_1.group_id
-> Seq Scan on group_users gu2_1 (cost=0.00..32.60 rows=2260 width=8)
SubPlan 3
-> Aggregate (cost=3331723.60..3331723.61 rows=1 width=4)
-> Merge Right Join (cost=6767.30..3294936.52 rows=3678708 width=20)
Merge Cond: (gg3_2.group_id = gg2_2.subgroup_id)
Filter: ((gu0_2.user_id = 5) OR (gu1_2.user_id = 5) OR (gu2_2.user_id = 5) OR (gu3_2.user_id = 5) OR (gu0_2.user_id = 3) OR (gu1_2.user_id = 3) OR (gu2_2.user_id = 3) OR (gu3_2.user_id = 3))
-> Nested Loop Left Join (cost=0.31..1164.00 rows=25538 width=8)
-> Index Only Scan using "PK_22a9d9794c2e2353fd9b10cf37d" on group_groups gg3_2 (cost=0.15..82.06 rows=2260 width=8)
-> Index Scan using group_users__group_id on group_users gu3_2 (cost=0.15..0.37 rows=11 width=8)
Index Cond: (group_id = gg3_2.subgroup_id)
-> Materialize (cost=6766.99..19917.77 rows=732941 width=20)
-> Merge Left Join (cost=6766.99..18085.41 rows=732941 width=20)
Merge Cond: (gg2_2.subgroup_id = gu2_2.group_id)
-> Sort (cost=6608.48..6770.64 rows=64862 width=16)
Sort Key: gg2_2.subgroup_id
-> Merge Right Join (cost=2.86..1424.36 rows=64862 width=16)
Merge Cond: (gu0_2.group_id = acl_rules_3.group_id)
-> Index Scan using group_users__group_id on group_users gu0_2 (cost=0.15..82.06 rows=2260 width=8)
-> Materialize (cost=2.70..378.08 rows=5740 width=16)
-> Nested Loop Left Join (cost=2.70..363.73 rows=5740 width=16)
-> Nested Loop Left Join (cost=2.55..120.53 rows=508 width=16)
-> Nested Loop Left Join (cost=2.39..98.98 rows=45 width=12)
-> Index Scan using acl_rules__group_id on acl_rules acl_rules_3 (cost=0.15..60.75 rows=4 width=8)
Filter: (org_id = orgs.id)
-> Bitmap Heap Scan on group_groups gg1_2 (cost=2.24..9.45 rows=11 width=8)
Recheck Cond: (group_id = acl_rules_3.group_id)
-> Bitmap Index Scan on group_groups__group_id (cost=0.00..2.24 rows=11 width=0)
Index Cond: (group_id = acl_rules_3.group_id)
-> Index Scan using group_groups__group_id on group_groups gg2_2 (cost=0.15..0.37 rows=11 width=8)
Index Cond: (group_id = gg1_2.subgroup_id)
-> Index Scan using group_users__group_id on group_users gu1_2 (cost=0.15..0.37 rows=11 width=8)
Index Cond: (group_id = gg1_2.subgroup_id)
-> Sort (cost=158.51..164.16 rows=2260 width=8)
Sort Key: gu2_2.group_id
-> Seq Scan on group_users gu2_2 (cost=0.00..32.60 rows=2260 width=8)
SubPlan 4
-> Limit (cost=0.61..1.67 rows=1 width=4)
-> Merge Left Join (cost=0.61..950152.81 rows=897868 width=4)
Merge Cond: (acl_rules_4.group_id = gu0_3.group_id)
Filter: ((gu0_3.user_id = 5) OR (gu1_3.user_id = 5) OR (gu2_3.user_id = 5) OR (gu3_3.user_id = 5) OR (gu0_3.user_id = 3) OR (gu1_3.user_id = 3) OR (gu2_3.user_id = 3) OR (gu3_3.user_id = 3))
-> Nested Loop Left Join (cost=0.46..145530.87 rows=2021457 width=20)
-> Nested Loop Left Join (cost=0.30..59889.27 rows=178890 width=20)
-> Nested Loop Left Join (cost=0.15..52310.35 rows=15831 width=20)
Join Filter: (gg3_3.subgroup_id = gu3_3.group_id)
-> Nested Loop Left Join (cost=0.15..4778.20 rows=1401 width=20)
Join Filter: (gg3_3.group_id = gg2_3.subgroup_id)
-> Nested Loop Left Join (cost=0.15..536.35 rows=124 width=16)
Join Filter: (gg2_3.group_id = gg1_3.subgroup_id)
-> Nested Loop Left Join (cost=0.15..125.20 rows=11 width=12)
Join Filter: (gg1_3.group_id = acl_rules_4.group_id)
-> Index Scan using acl_rules__group_id on acl_rules acl_rules_4 (cost=0.15..64.35 rows=1 width=8)
Filter: (((doc_id)::text = (docs.id)::text) AND ((permissions & 1) = 1))
-> Seq Scan on group_groups gg1_3 (cost=0.00..32.60 rows=2260 width=8)
-> Materialize (cost=0.00..43.90 rows=2260 width=8)
-> Seq Scan on group_groups gg2_3 (cost=0.00..32.60 rows=2260 width=8)
-> Materialize (cost=0.00..43.90 rows=2260 width=8)
-> Seq Scan on group_groups gg3_3 (cost=0.00..32.60 rows=2260 width=8)
-> Materialize (cost=0.00..43.90 rows=2260 width=8)
-> Seq Scan on group_users gu3_3 (cost=0.00..32.60 rows=2260 width=8)
-> Index Scan using group_users__group_id on group_users gu2_3 (cost=0.15..0.37 rows=11 width=8)
Index Cond: (group_id = gg2_3.subgroup_id)
-> Index Scan using group_users__group_id on group_users gu1_3 (cost=0.15..0.37 rows=11 width=8)
Index Cond: (group_id = gg1_3.subgroup_id)
-> Materialize (cost=0.15..87.71 rows=2260 width=8)
-> Index Scan using group_users__group_id on group_users gu0_3 (cost=0.15..82.06 rows=2260 width=8)
JIT:
Functions: 276
Options: Inlining true, Optimization true, Expressions true, Deforming true
Hey @u0reo, that seems unusually slow to me. Are the row counts on the tables in your database reasonable? Have you enough memory for the app and the db? Can you confirm that changes to the db are successfully being written?
Hi! I have same issue. My grist installation on Postgre work wery slow, but if I swith it to sqllite - it work wery fast. I use: grist 1.1.12 in docker + PostgreSQL 16.2 in dedicated host (very slow) grist 1.1.12 in docker + SQLlite-file mapped to container aand stor at GlusterFS share (very fast) compose:
grist:
restart: unless-stopped
container_name: grist
image: gristlabs/grist:latest
ports:
- 8484:8484
environment:
GRIST_SESSION_SECRET: 'sessionsecret'
GRIST_SUPPORT_EMAIL: "help@mydomain"
APP_HOME_URL: "https://grist.mydomain"
REDIS_URL: "redis://redis_ip:redis_port/base_number"
GRIST_ANON_PLAYGROUND: false
GRIST_LIST_PUBLIC_SITES: false
GRIST_FORCE_LOGIN: true
GRIST_OIDC_SP_HOST: "https://grist.mydomain"
GRIST_OIDC_IDP_ISSUER: "https://sso.mydomain/realms/myrealm"
GRIST_OIDC_IDP_SCOPES: "openid profile email"
GRIST_OIDC_IDP_CLIENT_ID: "client_name"
GRIST_OIDC_IDP_CLIENT_SECRET: "client_secret"
TYPEORM_TYPE: 'postgres'
TYPEORM_DATABASE: 'db_name'
TYPEORM_USERNAME: "username"
TYPEORM_PASSWORD: "password"
TYPEORM_HOST: "servername"
TYPEORM_PORT: 5432
TYPEORM_LOGGING: false
GRIST_DOMAIN: "mydomain"
GRIST_EXPERIMENTAL_PLUGINS: true
GRIST_SERVE_SAME_ORIGIN: true
APP_DOC_URL: "https://grist.mydomain"
GRIST_BACKUP_DELAY_SECS: 30
volumes:
- /gluster/docker/grist:/persist
Not sure what the slowness could be. We use PostgreSQL ourselves in production without problems. Is it possible the database is somehow underpowered? Any knobs you can turn such as number of cores it has access to? Is PostgreSQL working fine for serving other apps under the same conditions?
I have configuration Postgre 16.2 with 4 CPU core (Xeon E5-2697 v2), 8Gb RAM and SSD. It works on default configuration, I have only Outline application at this Postgres-server and Outline works fine. It is very low load server.
Do you use Postgre 16 too? May be you have any configuration optimisations?
When I chech developmetn tools, I see slow grist-database load, but it is not associated with Postgres, grasi-db files stored in mapped folder
When I swithc to SQLlite this load time wery fast
For both screnshoots I used one instance. I make only one change in compose - comment database settins:
TYPEORM_TYPE: 'postgres'
TYPEORM_DATABASE: 'db_name'
TYPEORM_USERNAME: "username"
TYPEORM_PASSWORD: "password"
TYPEORM_HOST: "servername"
TYPEORM_PORT: 5432
TYPEORM_LOGGING: false
I installed postgres and it has the same problem. Every time I add something to the cell, it says "still working"
Configs 2 CPUs 8 GB RAM
@crelocks do you ever get beyond the "still working" message? Trying to determine if the setup is slow or broken.
I do get it beyond the "still working" message. There is a 4-5 seconds delay for every interaction like writing something to the cell takes 4-5 seconds. See the screen recorder
https://github.com/gristlabs/grist-core/assets/141933932/de2b5d09-9229-4055-8bfe-672eec744939
Thanks for the follow-up @crelocks.
@jordigh @Spoffy there is an issue some users have reported of Grist running slowly, right out of the box. The common factor seems to be PostgreSQL usage. I've never managed to replicated the slowness though. We have some very complicated queries, but they should be efficient, and in any case they'd be pretty trivial on a fresh empty-ish install. We use PostgreSQL ourselves in staging and production for our SaaS without trouble.
I'm wondering if @crelocks situation is clean enough to replicate. @crelocks would you be willing to give us a fuller description of your configuration? OS, version of PostgreSQL and how you installed it, version of Grist and how you run it?
This is my docker-compose.yaml
version: '3.4'
services:
grist:
image: gristlabs/grist
ports:
- "9008:8484"
volumes:
- /data/ss/grist:/persist
environment:
APP_HOME_URL: "https://XXX.YYY.com"
# GRIST_OIDC_SP_HOST: "https://XXX.YYY.com"
GRIST_OIDC_IDP_ISSUER: "https://accounts.google.com"
GRIST_OIDC_IDP_SCOPES: "openid profile email"
GRIST_OIDC_IDP_CLIENT_ID: "SECRET"
GRIST_OIDC_IDP_CLIENT_SECRET: "SECRET"
GRIST_OIDC_IDP_SKIP_END_SESSION_ENDPOINT: "true"
GRIST_PAGE_TITLE_SUFFIX: " - TEST"
APP_STATIC_INCLUDE_CUSTOM_CSS: "true"
GRIST_WIDGET_LIST_URL: "https://github.com/gristlabs/grist-widget/releases/download/latest/manifest.json"
REDIS_URL: "redis://redis"
TYPEORM_TYPE: "postgres"
TYPEORM_LOGGING: "true"
TYPEORM_DATABASE: "grist"
TYPEORM_HOST: "postgres"
TYPEORM_USERNAME: "grist"
TYPEORM_PASSWORD: "SECRET"
GRIST_ANON_PLAYGROUND: "false"
GRIST_SINGLE_ORG: "data"
GRIST_ORG_IN_PATH: "true"
GRIST_TELEMETRY_LEVEL: "off"
GRIST_SUPPORT_ANON: "false"
GRIST_DEFAULT_EMAIL: "[email protected]"
GRIST_DOMAIN: "XXX.YYY.com"
GRIST_SESSION_DOMAIN: "XXX.YYY.com"
GRIST_SESSION_SECRET: SECRET
depends_on:
- redis
- postgres
networks:
- grist
redis:
image: redis:alpine
ports:
- "6379:6379"
networks:
- grist
postgres:
image: postgres:alpine
environment:
POSTGRES_DB: "grist"
POSTGRES_USER: "grist"
POSTGRES_PASSWORD: "SECRET"
ports:
- "5432:5432"
volumes:
- /data/ss/pgdata:/var/lib/postgresql/data
networks:
- grist
networks:
grist:
driver: bridge
That is the OS / other info
Linux oc2 5.15.0-1038-oracle #44~20.04.1-Ubuntu SMP Thu Jun 22 16:39:12 UTC 2023 aarch64 aarch64 aarch64 GNU/Linux
Latest grit (Version 1.1.14) and postgres version (16)
Please let me know if you need more information
Thank you so much for your prompt response @paulfitz
Excellent, thanks for that @crelocks. We'll work on replicating the problem and tracking it down.
Hi @crelocks. I'm going to investigate this problem. Give me a few days to look into it. Thanks!
I was able to reproduce this problem. While there is a rather ugly query there, it seems that the biggest problem isn't running it, but trying to JIT compile it.
After disabling JIT in Postgres, the problem was solved for me.
@crelocks @apnagaev @aliuq and @u0reo, try modifying your docker-compose.yaml to something like
postgres:
image: postgres:latest
command: -c jit=off
# other config follows...
and see if that fixes it for you.
I'm closing this issue. If it reappears or if this fix isn't sufficient, we can revisit the problem.
(Thanks to Zr40 of #postgresql in Libera IRC for help diagnosing this problem.)