postgresql - Hasura 查询在生产 Heroku 服务器上超时，但在 Heroku 登台服务器上快如闪电
问题描述
我正在使用 Hasura v1.3.1 在我的网站上查询和填充网格。我遇到了一个问题，即在 Hasura Production（Heroku Standard 2x dyno）上执行的相同查询在 30 秒后超时，但在 Hasura Staging（Heroku Standard dyno）上执行的相同查询速度快如闪电。Hasura 的两个 Heroku 实例都连接到标准测功机上的不同 Heroku Postgres 13.4 实例。
这是查询本身：
query collections($submissionRecordId: bigint!) {
collections_submissions_by_pk(id: $submissionRecordId) {
collections(where: {status: {_neq: "deleted"}}) {
id
debtor_number
invoice_number
invoice_date
invoice_amount
verification_alerts(where: {verifiable_record_type: {_eq: "Collection"}}) {
verification_types {
name
message
}
}
}
}
}
在第一次通过时，缓慢似乎与表/角色权限有关，因为 verify_alerts 和 verify_types 表具有一些深度嵌套的权限过滤器。如果我删除查询中嵌套最深的部分并仅用 id 替换它，则查询在生产中再次变得很快。例如
query collections($submissionRecordId: bigint!) {
collections_submissions_by_pk(id: $submissionRecordId) {
collections(where: {status: {_neq: "deleted"}}) {
id
debtor_number
invoice_number
invoice_date
invoice_amount
verification_alerts(where: {verifiable_record_type: {_eq: "Collection"}}) {
verification_type_id
}
}
}
}
这让我相信这是一个表角色权限问题。我认为权限检查可能由于嵌套查询而呈指数增长，从而减慢了速度。为了检查这一点，我关闭了表上的权限过滤，并且查询在生产中就像我预期的那样快如闪电。所以我一定发现了问题……我想。
为了仔细检查，我做了一个完整的备份并从生产中恢复到我在 heroku（标准 dyno）上的暂存 postgres 数据库，并使用与生产中相同的权限过滤器运行相同的查询。如果我确实发现了问题，查询应该已经超时。不，它快如闪电，并且运行在功能较弱的 hasura dyno（标准与标准 2x）上。
接下来我想到的是，我的生产数据库一定比我的暂存数据库膨胀得多。确实是这样，所以我在制作中执行了 VACUUM FULL ，将膨胀降低到与分期相近的水平。但是，当我重新打开表权限过滤器时，查询仍然在生产中超时。
我只是无法解释在我的生产环境中可能有什么不同，当相同的数据库和相同的 hasura 表元数据在登台时速度快如闪电时，查询时间会超过 30 秒。我迫切需要比我更聪明的人来解决这个问题。
即使我有一个临时解决方案，在 prod 中禁用权限过滤，这也不能满足我了解为什么会发生这种情况的需要。
对于那些愿意帮助我的人，可以在下面找到一些进一步的信息。
这是查询在生产中生成的 SQL：
SELECT
coalesce((json_agg("root") -> 0), 'null') AS "root"
FROM
(
SELECT
row_to_json(
(
SELECT
"_12_e"
FROM
(
SELECT
"_11_root.ar.root.collections"."collections" AS "collections"
) AS "_12_e"
)
) AS "root"
FROM
(
SELECT
*
FROM
"public"."collections_submissions"
WHERE
(
("public"."collections_submissions"."id") = (('23453') :: bigint)
)
) AS "_0_root.base"
LEFT OUTER JOIN LATERAL (
SELECT
coalesce(json_agg("collections"), '[]') AS "collections"
FROM
(
SELECT
row_to_json(
(
SELECT
"_9_e"
FROM
(
SELECT
"_1_root.ar.root.collections.base"."id" AS "id",
"_1_root.ar.root.collections.base"."debtor_number" AS "debtor_number",
"_1_root.ar.root.collections.base"."invoice_number" AS "invoice_number",
"_1_root.ar.root.collections.base"."invoice_date" AS "invoice_date",
"_1_root.ar.root.collections.base"."invoice_amount" AS "invoice_amount",
"_8_root.ar.root.collections.ar.collections.verification_alerts"."verification_alerts" AS "verification_alerts"
) AS "_9_e"
)
) AS "collections"
FROM
(
SELECT
*
FROM
"public"."collections"
WHERE
(
(
("_0_root.base"."id") = ("collections_submission_id")
)
AND (
("public"."collections"."status") <> (('deleted') :: text)
)
)
) AS "_1_root.ar.root.collections.base"
LEFT OUTER JOIN LATERAL (
SELECT
coalesce(json_agg("verification_alerts"), '[]') AS "verification_alerts"
FROM
(
SELECT
row_to_json(
(
SELECT
"_6_e"
FROM
(
SELECT
"_5_root.ar.root.collections.ar.collections.verification_alerts.or.verification_types"."verification_types" AS "verification_types"
) AS "_6_e"
)
) AS "verification_alerts"
FROM
(
SELECT
*
FROM
"public"."verification_alerts"
WHERE
(
(
("_1_root.ar.root.collections.base"."id") = ("verifiable_record_id")
)
AND (
(
"public"."verification_alerts"."verifiable_record_type"
) = (('Collection') :: text)
)
)
) AS "_2_root.ar.root.collections.ar.collections.verification_alerts.base"
LEFT OUTER JOIN LATERAL (
SELECT
row_to_json(
(
SELECT
"_4_e"
FROM
(
SELECT
"_3_root.ar.root.collections.ar.collections.verification_alerts.or.verification_types.base"."name" AS "name",
"_3_root.ar.root.collections.ar.collections.verification_alerts.or.verification_types.base"."message" AS "message"
) AS "_4_e"
)
) AS "verification_types"
FROM
(
SELECT
*
FROM
"public"."verification_types"
WHERE
(
(
"_2_root.ar.root.collections.ar.collections.verification_alerts.base"."verification_type_id"
) = ("id")
)
) AS "_3_root.ar.root.collections.ar.collections.verification_alerts.or.verification_types.base"
) AS "_5_root.ar.root.collections.ar.collections.verification_alerts.or.verification_types" ON ('true')
) AS "_7_root.ar.root.collections.ar.collections.verification_alerts"
) AS "_8_root.ar.root.collections.ar.collections.verification_alerts" ON ('true')
) AS "_10_root.ar.root.collections"
) AS "_11_root.ar.root.collections" ON ('true')
) AS "_13_root"
以下是生产查询的执行计划：
Aggregate (cost=504.36..504.36 rows=1 width=32)
-> Nested Loop Left Join (cost=500.34..504.35 rows=1 width=32)
-> Index Only Scan using collections_submissions_pkey on collections_submissions (cost=0.06..4.06 rows=1 width=8)
Index Cond: (id = '23453'::bigint)
-> Aggregate (cost=500.28..500.29 rows=1 width=32)
-> Nested Loop Left Join (cost=5.23..499.97 rows=78 width=64)
-> Index Scan using index_collections_on_collections_submission_id on collections (cost=0.08..98.15 rows=78 width=32)
Index Cond: (collections_submissions.id = collections_submission_id)
Filter: ((status)::text <> 'deleted'::text)
-> Aggregate (cost=5.14..5.15 rows=1 width=32)
-> Nested Loop Left Join (cost=0.08..5.14 rows=1 width=32)
Join Filter: (verification_alerts.verification_type_id = verification_types.id)
-> Index Only Scan using index_all_verification_alerts_fields_unique on verification_alerts (cost=0.08..4.09 rows=1 width=8)
Index Cond: ((verifiable_record_id = collections.id) AND (verifiable_record_type = 'Collection'::text))
-> Seq Scan on verification_types (cost=0.00..1.03 rows=5 width=40)
SubPlan 3
-> Result (cost=0.00..0.00 rows=1 width=32)
SubPlan 4
-> Result (cost=0.00..0.00 rows=1 width=32)
SubPlan 2
-> Result (cost=0.00..0.00 rows=1 width=32)
SubPlan 1
-> Result (cost=0.00..0.00 rows=1 width=32)
这是在登台时生成的 SQL 查询：
SELECT
coalesce((json_agg("root") -> 0), 'null') AS "root"
FROM
(
SELECT
row_to_json(
(
SELECT
"_12_e"
FROM
(
SELECT
"_11_root.ar.root.collections"."collections" AS "collections"
) AS "_12_e"
)
) AS "root"
FROM
(
SELECT
*
FROM
"public"."collections_submissions"
WHERE
(
("public"."collections_submissions"."id") = (('23453') :: bigint)
)
) AS "_0_root.base"
LEFT OUTER JOIN LATERAL (
SELECT
coalesce(json_agg("collections"), '[]') AS "collections"
FROM
(
SELECT
row_to_json(
(
SELECT
"_9_e"
FROM
(
SELECT
"_1_root.ar.root.collections.base"."id" AS "id",
"_1_root.ar.root.collections.base"."debtor_number" AS "debtor_number",
"_1_root.ar.root.collections.base"."invoice_number" AS "invoice_number",
"_1_root.ar.root.collections.base"."invoice_date" AS "invoice_date",
"_1_root.ar.root.collections.base"."invoice_amount" AS "invoice_amount",
"_8_root.ar.root.collections.ar.collections.verification_alerts"."verification_alerts" AS "verification_alerts"
) AS "_9_e"
)
) AS "collections"
FROM
(
SELECT
*
FROM
"public"."collections"
WHERE
(
(
("_0_root.base"."id") = ("collections_submission_id")
)
AND (
("public"."collections"."status") <> (('deleted') :: text)
)
)
) AS "_1_root.ar.root.collections.base"
LEFT OUTER JOIN LATERAL (
SELECT
coalesce(json_agg("verification_alerts"), '[]') AS "verification_alerts"
FROM
(
SELECT
row_to_json(
(
SELECT
"_6_e"
FROM
(
SELECT
"_5_root.ar.root.collections.ar.collections.verification_alerts.or.verification_types"."verification_types" AS "verification_types"
) AS "_6_e"
)
) AS "verification_alerts"
FROM
(
SELECT
*
FROM
"public"."verification_alerts"
WHERE
(
(
("_1_root.ar.root.collections.base"."id") = ("verifiable_record_id")
)
AND (
(
"public"."verification_alerts"."verifiable_record_type"
) = (('Collection') :: text)
)
)
) AS "_2_root.ar.root.collections.ar.collections.verification_alerts.base"
LEFT OUTER JOIN LATERAL (
SELECT
row_to_json(
(
SELECT
"_4_e"
FROM
(
SELECT
"_3_root.ar.root.collections.ar.collections.verification_alerts.or.verification_types.base"."name" AS "name",
"_3_root.ar.root.collections.ar.collections.verification_alerts.or.verification_types.base"."message" AS "message"
) AS "_4_e"
)
) AS "verification_types"
FROM
(
SELECT
*
FROM
"public"."verification_types"
WHERE
(
(
"_2_root.ar.root.collections.ar.collections.verification_alerts.base"."verification_type_id"
) = ("id")
)
) AS "_3_root.ar.root.collections.ar.collections.verification_alerts.or.verification_types.base"
) AS "_5_root.ar.root.collections.ar.collections.verification_alerts.or.verification_types" ON ('true')
) AS "_7_root.ar.root.collections.ar.collections.verification_alerts"
) AS "_8_root.ar.root.collections.ar.collections.verification_alerts" ON ('true')
) AS "_10_root.ar.root.collections"
) AS "_11_root.ar.root.collections" ON ('true')
) AS "_13_root"
以下是 staging 的执行计划：
Aggregate (cost=450.33..450.34 rows=1 width=32)
-> Nested Loop Left Join (cost=448.32..450.33 rows=1 width=32)
-> Index Only Scan using collections_submissions_pkey on collections_submissions (cost=0.06..2.06 rows=1 width=8)
Index Cond: (id = '23453'::bigint)
-> Aggregate (cost=448.26..448.26 rows=1 width=32)
-> Nested Loop Left Join (cost=5.20..447.98 rows=70 width=64)
-> Index Scan using index_collections_on_collections_submission_id on collections (cost=0.08..89.12 rows=70 width=32)
Index Cond: (collections_submission_id = collections_submissions.id)
Filter: ((status)::text <> 'deleted'::text)
-> Aggregate (cost=5.12..5.12 rows=1 width=32)
-> Nested Loop Left Join (cost=0.06..5.11 rows=1 width=32)
Join Filter: (verification_alerts.verification_type_id = verification_types.id)
-> Index Only Scan using index_all_verification_alerts_fields_unique on verification_alerts (cost=0.06..4.06 rows=1 width=8)
Index Cond: ((verifiable_record_id = collections.id) AND (verifiable_record_type = 'Collection'::text))
-> Seq Scan on verification_types (cost=0.00..1.03 rows=5 width=40)
SubPlan 3
-> Result (cost=0.00..0.00 rows=1 width=32)
SubPlan 4
-> Result (cost=0.00..0.00 rows=1 width=32)
SubPlan 2
-> Result (cost=0.00..0.00 rows=1 width=32)
SubPlan 1
-> Result (cost=0.00..0.00 rows=1 width=32)
执行计划的差异仅在于“成本”的值，但我认为运行时间的差异不足以超过 30 秒。
Hasura 查询生成的 SQL 毫无疑问是相同的。
解决方案
推荐阅读
- dart - 在 Flutter 中使用 Futures 加载 config.json
- ssrs-2008 - 有没有办法进行西班牙语/英语邮件合并？
- javascript - ui-grid 3.1.1 随机渲染表格内容
- node.js - 在 AWS 中部署机器人实例
- google-chrome-extension - 删除权限会导致扩展程序被禁用
- java - jax-rs @Path 表达式中的路径参数是否需要用斜杠分隔？
- javascript - 在 setInterval 完成之前变量变为 true
- bash - 使用 IFS 编写 Bash 脚本
- r - R - 错误：“汽车”的包或命名空间加载失败；加载库失败
- c# - 合并两个列表的重复项时条件合并