我有一个带有两个实体的 .NET WebApi(请原谅这些愚蠢的名称,这只是一个示例):
public class Father
{
public int Id { get; set; }
public string Name { get; set; } = null!;
public int? ChildAId { get; set; }
public int? ChildBId { get; set; }
public int? ChildCId { get; set; }
public int? ChildDId { get; set; }
public int? ChildEId { get; set; }
public int? ChildFId { get; set; }
public int? ChildGId { get; set; }
public int? ChildHId { get; set; }
public int? ChildIId { get; set; }
public int? ChildJId { get; set; }
public int? ChildKId { get; set; }
public int? ChildLId { get; set; }
public int? ChildMId { get; set; }
public int? ChildNId { get; set; }
public int? ChildOId { get; set; }
public int? ChildPId { get; set; }
public int? ChildQId { get; set; }
public int? ChildRId { get; set; }
public int? ChildSId { get; set; }
public int? ChildTId { get; set; }
public int? ChildUId { get; set; }
public int? ChildVId { get; set; }
public int? ChildWId { get; set; }
public int? ChildXId { get; set; }
public int? ChildYId { get; set; }
public int? ChildZId { get; set; }
public int? Child0Id { get; set; }
public int? Child1Id { get; set; }
public int? Child2Id { get; set; }
public int? Child3Id { get; set; }
public int? Child4Id { get; set; }
public int? Child5Id { get; set; }
public int? Child6Id { get; set; }
public int? Child7Id { get; set; }
public int? Child8Id { get; set; }
public int? Child9Id { get; set; }
public Child? ChildA { get; set; }
public Child? ChildB { get; set; }
public Child? ChildC { get; set; }
public Child? ChildD { get; set; }
public Child? ChildE { get; set; }
public Child? ChildF { get; set; }
public Child? ChildG { get; set; }
public Child? ChildH { get; set; }
public Child? ChildI { get; set; }
public Child? ChildJ { get; set; }
public Child? ChildK { get; set; }
public Child? ChildL { get; set; }
public Child? ChildM { get; set; }
public Child? ChildN { get; set; }
public Child? ChildO { get; set; }
public Child? ChildP { get; set; }
public Child? ChildQ { get; set; }
public Child? ChildR { get; set; }
public Child? ChildS { get; set; }
public Child? ChildT { get; set; }
public Child? ChildU { get; set; }
public Child? ChildV { get; set; }
public Child? ChildW { get; set; }
public Child? ChildX { get; set; }
public Child? ChildY { get; set; }
public Child? ChildZ { get; set; }
public Child? Child0 { get; set; }
public Child? Child1 { get; set; }
public Child? Child2 { get; set; }
public Child? Child3 { get; set; }
public Child? Child4 { get; set; }
public Child? Child5 { get; set; }
public Child? Child6 { get; set; }
public Child? Child7 { get; set; }
public Child? Child8 { get; set; }
public Child? Child9 { get; set; }
}
public class Child
{
public int Id { get; set; }
public string Name { get; set; } = null!;
public string Description { get; set; } = null!;
}
我插入了 100,000 个父亲和他们的孩子。现在我尝试使用 EF Core 从数据库中恢复它们(但 PgAdmin 给出了相同的结果)。
20 名有限制/抵消的父亲
SELECT f."Id", f."Child0Id", f."Child1Id", f."Child2Id", f."Child3Id", f."Child4Id", f."Child5Id", f."Child6Id", f."Child7Id", f."Child8Id", f."Child9Id", f."ChildAId", f."ChildBId", f."ChildCId", f."ChildDId", f."ChildEId", f."ChildFId", f."ChildGId", f."ChildHId", f."ChildIId", f."ChildJId", f."ChildKId", f."ChildLId", f."ChildMId", f."ChildNId", f."ChildOId", f."ChildPId", f."ChildQId", f."ChildRId", f."ChildSId", f."ChildTId", f."ChildUId", f."ChildVId", f."ChildWId", f."ChildXId", f."ChildYId", f."ChildZId", f."Name"
FROM "Father" AS f
LIMIT 20 OFFSET 98000
查询在 130 毫秒内完成
20 位父亲及其所有孩子和限制/抵消
SELECT t."Id", t."Child0Id", t."Child1Id", t."Child2Id", t."Child3Id", t."Child4Id", t."Child5Id", t."Child6Id", t."Child7Id", t."Child8Id", t."Child9Id", t."ChildAId", t."ChildBId", t."ChildCId", t."ChildDId", t."ChildEId", t."ChildFId", t."ChildGId", t."ChildHId", t."ChildIId", t."ChildJId", t."ChildKId", t."ChildLId", t."ChildMId", t."ChildNId", t."ChildOId", t."ChildPId", t."ChildQId", t."ChildRId", t."ChildSId", t."ChildTId", t."ChildUId", t."ChildVId", t."ChildWId", t."ChildXId", t."ChildYId", t."ChildZId", t."Name", c."Id", c."Description", c."Name", c0."Id", c0."Description", c0."Name", c1."Id", c1."Description", c1."Name", c2."Id", c2."Description", c2."Name", c3."Id", c3."Description", c3."Name", c4."Id", c4."Description", c4."Name", c5."Id", c5."Description", c5."Name", c6."Id", c6."Description", c6."Name", c7."Id", c7."Description", c7."Name", c8."Id", c8."Description", c8."Name", c9."Id", c9."Description", c9."Name", c10."Id", c10."Description", c10."Name", c11."Id", c11."Description", c11."Name", c12."Id", c12."Description", c12."Name", c13."Id", c13."Description", c13."Name", c14."Id", c14."Description", c14."Name", c15."Id", c15."Description", c15."Name", c16."Id", c16."Description", c16."Name", c17."Id", c17."Description", c17."Name", c18."Id", c18."Description", c18."Name", c19."Id", c19."Description", c19."Name", c20."Id", c20."Description", c20."Name", c21."Id", c21."Description", c21."Name", c22."Id", c22."Description", c22."Name", c23."Id", c23."Description", c23."Name", c24."Id", c24."Description", c24."Name", c25."Id", c25."Description", c25."Name", c26."Id", c26."Description", c26."Name", c27."Id", c27."Description", c27."Name", c28."Id", c28."Description", c28."Name", c29."Id", c29."Description", c29."Name", c30."Id", c30."Description", c30."Name", c31."Id", c31."Description", c31."Name", c32."Id", c32."Description", c32."Name", c33."Id", c33."Description", c33."Name", c34."Id", c34."Description", c34."Name"
FROM (
SELECT f."Id", f."Child0Id", f."Child1Id", f."Child2Id", f."Child3Id", f."Child4Id", f."Child5Id", f."Child6Id", f."Child7Id", f."Child8Id", f."Child9Id", f."ChildAId", f."ChildBId", f."ChildCId", f."ChildDId", f."ChildEId", f."ChildFId", f."ChildGId", f."ChildHId", f."ChildIId", f."ChildJId", f."ChildKId", f."ChildLId", f."ChildMId", f."ChildNId", f."ChildOId", f."ChildPId", f."ChildQId", f."ChildRId", f."ChildSId", f."ChildTId", f."ChildUId", f."ChildVId", f."ChildWId", f."ChildXId", f."ChildYId", f."ChildZId", f."Name"
FROM "Father" AS f
LIMIT 20 OFFSET 98000
) AS t
LEFT JOIN "Child" AS c ON t."ChildAId" = c."Id"
LEFT JOIN "Child" AS c0 ON t."ChildBId" = c0."Id"
LEFT JOIN "Child" AS c1 ON t."ChildCId" = c1."Id"
LEFT JOIN "Child" AS c2 ON t."ChildDId" = c2."Id"
LEFT JOIN "Child" AS c3 ON t."ChildEId" = c3."Id"
LEFT JOIN "Child" AS c4 ON t."ChildFId" = c4."Id"
LEFT JOIN "Child" AS c5 ON t."ChildGId" = c5."Id"
LEFT JOIN "Child" AS c6 ON t."ChildHId" = c6."Id"
LEFT JOIN "Child" AS c7 ON t."ChildIId" = c7."Id"
LEFT JOIN "Child" AS c8 ON t."ChildJId" = c8."Id"
LEFT JOIN "Child" AS c9 ON t."ChildKId" = c9."Id"
LEFT JOIN "Child" AS c10 ON t."ChildLId" = c10."Id"
LEFT JOIN "Child" AS c11 ON t."ChildMId" = c11."Id"
LEFT JOIN "Child" AS c12 ON t."ChildNId" = c12."Id"
LEFT JOIN "Child" AS c13 ON t."ChildOId" = c13."Id"
LEFT JOIN "Child" AS c14 ON t."ChildPId" = c14."Id"
LEFT JOIN "Child" AS c15 ON t."ChildQId" = c15."Id"
LEFT JOIN "Child" AS c16 ON t."ChildRId" = c16."Id"
LEFT JOIN "Child" AS c17 ON t."ChildSId" = c17."Id"
LEFT JOIN "Child" AS c18 ON t."ChildTId" = c18."Id"
LEFT JOIN "Child" AS c19 ON t."ChildUId" = c19."Id"
LEFT JOIN "Child" AS c20 ON t."ChildVId" = c20."Id"
LEFT JOIN "Child" AS c21 ON t."ChildWId" = c21."Id"
LEFT JOIN "Child" AS c22 ON t."ChildXId" = c22."Id"
LEFT JOIN "Child" AS c23 ON t."ChildYId" = c23."Id"
LEFT JOIN "Child" AS c24 ON t."ChildZId" = c24."Id"
LEFT JOIN "Child" AS c25 ON t."Child0Id" = c25."Id"
LEFT JOIN "Child" AS c26 ON t."Child1Id" = c26."Id"
LEFT JOIN "Child" AS c27 ON t."Child2Id" = c27."Id"
LEFT JOIN "Child" AS c28 ON t."Child3Id" = c28."Id"
LEFT JOIN "Child" AS c29 ON t."Child4Id" = c29."Id"
LEFT JOIN "Child" AS c30 ON t."Child5Id" = c30."Id"
LEFT JOIN "Child" AS c31 ON t."Child6Id" = c31."Id"
LEFT JOIN "Child" AS c32 ON t."Child7Id" = c32."Id"
LEFT JOIN "Child" AS c33 ON t."Child8Id" = c33."Id"
LEFT JOIN "Child" AS c34 ON t."Child9Id" = c34."Id"
查询在 300 毫秒内完成
根据 ID 确定的单亲父亲(限制由单一默认设置)
SELECT f."Id", f."Child0Id", f."Child1Id", f."Child2Id", f."Child3Id", f."Child4Id", f."Child5Id", f."Child6Id", f."Child7Id", f."Child8Id", f."Child9Id", f."ChildAId", f."ChildBId", f."ChildCId", f."ChildDId", f."ChildEId", f."ChildFId", f."ChildGId", f."ChildHId", f."ChildIId", f."ChildJId", f."ChildKId", f."ChildLId", f."ChildMId", f."ChildNId", f."ChildOId", f."ChildPId", f."ChildQId", f."ChildRId", f."ChildSId", f."ChildTId", f."ChildUId", f."ChildVId", f."ChildWId", f."ChildXId", f."ChildYId", f."ChildZId", f."Name"
FROM "Father" AS f
WHERE f."Id" = 1
LIMIT 2
查询在 115 毫秒内完成
现在问题来了:单亲父亲和所有孩子的身份证件(由单一默认设置的限制)
SELECT f."Id", f."Child0Id", f."Child1Id", f."Child2Id", f."Child3Id", f."Child4Id", f."Child5Id", f."Child6Id", f."Child7Id", f."Child8Id", f."Child9Id", f."ChildAId", f."ChildBId", f."ChildCId", f."ChildDId", f."ChildEId", f."ChildFId", f."ChildGId", f."ChildHId", f."ChildIId", f."ChildJId", f."ChildKId", f."ChildLId", f."ChildMId", f."ChildNId", f."ChildOId", f."ChildPId", f."ChildQId", f."ChildRId", f."ChildSId", f."ChildTId", f."ChildUId", f."ChildVId", f."ChildWId", f."ChildXId", f."ChildYId", f."ChildZId", f."Name", c."Id", c."Description", c."Name", c0."Id", c0."Description", c0."Name", c1."Id", c1."Description", c1."Name", c2."Id", c2."Description", c2."Name", c3."Id", c3."Description", c3."Name", c4."Id", c4."Description", c4."Name", c5."Id", c5."Description", c5."Name", c6."Id", c6."Description", c6."Name", c7."Id", c7."Description", c7."Name", c8."Id", c8."Description", c8."Name", c9."Id", c9."Description", c9."Name", c10."Id", c10."Description", c10."Name", c11."Id", c11."Description", c11."Name", c12."Id", c12."Description", c12."Name", c13."Id", c13."Description", c13."Name", c14."Id", c14."Description", c14."Name", c15."Id", c15."Description", c15."Name", c16."Id", c16."Description", c16."Name", c17."Id", c17."Description", c17."Name", c18."Id", c18."Description", c18."Name", c19."Id", c19."Description", c19."Name", c20."Id", c20."Description", c20."Name", c21."Id", c21."Description", c21."Name", c22."Id", c22."Description", c22."Name", c23."Id", c23."Description", c23."Name", c24."Id", c24."Description", c24."Name", c25."Id", c25."Description", c25."Name", c26."Id", c26."Description", c26."Name", c27."Id", c27."Description", c27."Name", c28."Id", c28."Description", c28."Name", c29."Id", c29."Description", c29."Name", c30."Id", c30."Description", c30."Name", c31."Id", c31."Description", c31."Name", c32."Id", c32."Description", c32."Name", c33."Id", c33."Description", c33."Name", c34."Id", c34."Description", c34."Name"
FROM "Father" AS f
LEFT JOIN "Child" AS c ON f."ChildAId" = c."Id"
LEFT JOIN "Child" AS c0 ON f."ChildBId" = c0."Id"
LEFT JOIN "Child" AS c1 ON f."ChildCId" = c1."Id"
LEFT JOIN "Child" AS c2 ON f."ChildDId" = c2."Id"
LEFT JOIN "Child" AS c3 ON f."ChildEId" = c3."Id"
LEFT JOIN "Child" AS c4 ON f."ChildFId" = c4."Id"
LEFT JOIN "Child" AS c5 ON f."ChildGId" = c5."Id"
LEFT JOIN "Child" AS c6 ON f."ChildHId" = c6."Id"
LEFT JOIN "Child" AS c7 ON f."ChildIId" = c7."Id"
LEFT JOIN "Child" AS c8 ON f."ChildJId" = c8."Id"
LEFT JOIN "Child" AS c9 ON f."ChildKId" = c9."Id"
LEFT JOIN "Child" AS c10 ON f."ChildLId" = c10."Id"
LEFT JOIN "Child" AS c11 ON f."ChildMId" = c11."Id"
LEFT JOIN "Child" AS c12 ON f."ChildNId" = c12."Id"
LEFT JOIN "Child" AS c13 ON f."ChildOId" = c13."Id"
LEFT JOIN "Child" AS c14 ON f."ChildPId" = c14."Id"
LEFT JOIN "Child" AS c15 ON f."ChildQId" = c15."Id"
LEFT JOIN "Child" AS c16 ON f."ChildRId" = c16."Id"
LEFT JOIN "Child" AS c17 ON f."ChildSId" = c17."Id"
LEFT JOIN "Child" AS c18 ON f."ChildTId" = c18."Id"
LEFT JOIN "Child" AS c19 ON f."ChildUId" = c19."Id"
LEFT JOIN "Child" AS c20 ON f."ChildVId" = c20."Id"
LEFT JOIN "Child" AS c21 ON f."ChildWId" = c21."Id"
LEFT JOIN "Child" AS c22 ON f."ChildXId" = c22."Id"
LEFT JOIN "Child" AS c23 ON f."ChildYId" = c23."Id"
LEFT JOIN "Child" AS c24 ON f."ChildZId" = c24."Id"
LEFT JOIN "Child" AS c25 ON f."Child0Id" = c25."Id"
LEFT JOIN "Child" AS c26 ON f."Child1Id" = c26."Id"
LEFT JOIN "Child" AS c27 ON f."Child2Id" = c27."Id"
LEFT JOIN "Child" AS c28 ON f."Child3Id" = c28."Id"
LEFT JOIN "Child" AS c29 ON f."Child4Id" = c29."Id"
LEFT JOIN "Child" AS c30 ON f."Child5Id" = c30."Id"
LEFT JOIN "Child" AS c31 ON f."Child6Id" = c31."Id"
LEFT JOIN "Child" AS c32 ON f."Child7Id" = c32."Id"
LEFT JOIN "Child" AS c33 ON f."Child8Id" = c33."Id"
LEFT JOIN "Child" AS c34 ON f."Child9Id" = c34."Id"
WHERE f."Id" = 1
LIMIT 2
查询在两秒多内完成!2300毫秒
有人可以解释一下为什么以及如何优化这个查询吗?我有所有 PK 和 FK 的索引。
直观上,大约 36 个连接对于 WRT 性能来说有点难以推理。尽管如此,该
LIMIT 2
术语具有欺骗性,因为它可能会在返回前 2 个连接之前渲染整个 1:n 连接作为一种不同的方法,让我们尝试一下:
对父亲进行任何查询,并为每个父亲记录创建该行的 JSON 对象。即对于每个父亲
使用 JSON_EACH 遍历父行以查找不是来自
ID
或name
字段的所有值。即对于任何孩子为我们在步骤 2 中生成的每个子 ID 加入子项。
总而言之,我得到了:
笔记:
Id
的字段;田野也是如此。children
child_id
father_id
father
CTE/WITH 语句中表上的条件EXPLAIN ANALYZE
之前添加前缀SELECT
,然后发回您的结果。将行解析为 JSON 格式有点奇怪,但会为您提供一条分解子 ID 的路径,执行 1、单个 JOIN,以及规划器通过简单索引解析的简单查询