我有一个带有两个实体的 .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 的索引。