以下TypeORM生成的SQL查询需要超过11秒才能完成：
SELECT "node"."node_id" AS "node_id",
"node"."updated_on" AS "updated_on",
"node"."lang" AS "lang",
"node"."second_lang" AS "second_lang",
"node"."title" AS "title",
"node"."execution_modes" AS "execution_modes",
"artifact"."current_rev" AS "current_rev",
"artifact"."created_on" AS "created_on",
"artifact"."reference" AS "reference",
"ac_program"."name" AS "ac_program_name",
"release_in_production"."rgti_revision" AS "release_in_production_rgti_revision",
"release_in_production"."name" AS "release_in_production_name",
"release_in_production"."in_production_date" AS "release_in_production_date",
"last_release"."id" AS "review_release_id",
"node"."root_id" AS "root_id",
(("last_release"."status" = 'CHECK_IN_PROGRESS'
AND "last_release_checker"."id" IS NOT NULL
AND "last_release_checker"."review_date" IS NULL) OR("last_release"."status" = 'APPROVAL_IN_PROGRESS'
AND "last_release_approver"."id" IS NOT NULL
AND "last_release_approver"."review_date" IS NULL)) AS "has_pending_review",
CASE
WHEN "last_release"."rgti_revision" = "artifact"."current_rev"
AND "last_release"."status" = 'IN_PRODUCTION' THEN 'DASHBOARD_STATUS_RELEASED'
WHEN "last_release"."rgti_revision" = "artifact"."current_rev"
AND "last_release"."status" = 'CHECK_IN_PROGRESS' THEN 'DASHBOARD_STATUS_CHECK_IN_PROGRESS'
WHEN "last_release"."rgti_revision" = "artifact"."current_rev"
AND "last_release"."status" = 'APPROVAL_IN_PROGRESS' THEN 'DASHBOARD_STATUS_APPROVAL_IN_PROGRESS'
WHEN "last_release"."rgti_revision" = "artifact"."current_rev"
AND "last_release"."status" = 'APPROVED' THEN 'DASHBOARD_STATUS_APPROVED'
WHEN "rev"."status" = 'CANCELED' THEN 'DASHBOARD_STATUS_CANCELED'
ELSE 'DASHBOARD_STATUS_WORK_IN_PROGRESS'
END AS "status"
FROM "test_root_node" "node"
LEFT JOIN "test_root_revision_node" "node_target_revision_data" ON "node_target_revision_data".ROOT_ID = "node".ROOT_ID
AND "node_target_revision_data".START_REV =
(SELECT MAX(START_REV)
FROM "test_root_revision_node"
WHERE ROOT_ID = "node".ROOT_ID
AND STATUS IN ('PUBLISHED',
'RELEASED',
'CANCELED'))
LEFT JOIN "test_root_artifact" "artifact" ON "artifact"."root_id" = "node"."root_id"
LEFT JOIN "ext_usr_user" "created_by" ON "created_by"."username" = "artifact"."created_by"
LEFT JOIN "ext_spf_ac_program" "ac_program" ON "ac_program"."id" = "artifact"."ac_program_id"
LEFT JOIN "test_root_revision_node" "rev" ON "rev"."root_id" = "artifact"."root_id"
AND (("rev"."start_rev" <= "node_target_revision_data"."start_rev"
AND ("rev"."end_rev" IS NULL
OR "node_target_revision_data"."start_rev" < "rev"."end_rev")))
LEFT JOIN "ext_usr_user" "owner" ON "owner"."username" = "node"."owner"
LEFT JOIN "ext_usr_user" "updated_by" ON "updated_by"."username" = "node"."updated_by"
LEFT JOIN "release" "release_in_production" ON "release_in_production"."root_id" = "node"."root_id"
AND "release_in_production"."rgti_revision" =
(SELECT MAX("lastreleasedsub"."rgti_revision")
FROM "release" "lastreleasedsub"
WHERE "lastreleasedsub"."root_id" = "node"."root_id"
AND "lastreleasedsub"."status" = 'IN_PRODUCTION')
LEFT JOIN "release" "last_release" ON "last_release"."root_id" = "node"."root_id"
AND "last_release"."rgti_revision" =
(SELECT MAX("subrelease"."rgti_revision")
FROM "release" "subrelease"
WHERE "subrelease"."root_id" = "node"."root_id")
LEFT JOIN "release_checker" "last_release_checker" ON "last_release_checker"."releaseId" = "last_release"."id"
AND "last_release_checker"."user" = $1
LEFT JOIN "release_approver" "last_release_approver" ON "last_release_approver"."releaseId" = "last_release"."id"
AND "last_release_approver"."user" = $2
WHERE ("node"."start_rev" <= "node_target_revision_data"."start_rev"
AND ("node"."end_rev" IS NULL
OR "node_target_revision_data"."start_rev" < "node"."end_rev"))
AND EXISTS
(SELECT 1
FROM "test_root_node" "sub_node"
LEFT JOIN "r_test_root_node_ext_spf_prod_site" "sub_node_sub_sites" ON "sub_node_sub_sites"."node_object_id" = "sub_node"."object_id"
LEFT JOIN "ext_spf_prod_site" "sub_sites" ON "sub_sites"."id" = "sub_node_sub_sites"."ext_id"
WHERE ("sub_node"."start_rev" <= "node_target_revision_data"."start_rev"
AND ("sub_node"."end_rev" IS NULL
OR "node_target_revision_data"."start_rev" < "sub_node"."end_rev"))
AND "sub_node"."node_id" = "node"."node_id"
AND "sub_sites"."id" IN ($3,
$4,
$5,
$6,
$7,
$8,
$9,
$10))
ORDER BY "artifact"."reference" ASC NULLS FIRST
LIMIT 10;
给定以下数据库索引：
"table_name" "index_name" "column_name"
"attachment_node" "pk_7122c15632e3fcc73b05e1469a1" "object_id"
"chapter_node" "UQ_2d0c8eed0c372d42d7869e4342d" "start_rev"
"chapter_node" "UQ_2d0c8eed0c372d42d7869e4342d" "node_id"
"chapter_node" "pk_3af2e67c786c8eeaf80813962c5" "object_id"
"ext_data" "PK_c7f24c9babbc3720df7a872aed3" "id"
"ext_data_metadata" "PK_b23dcef2a424f213e81db02286a" "id"
"ext_ref_ata" "pk_b631c9d4aee24712830048347f2" "code"
"ext_ref_ca" "pk_2a80f7e8b3e7373cac24f23fbdf" "code"
"ext_ref_fin" "pk_78fcd46235cc23f3ee0100724eb" "code"
"ext_ref_skill" "pk_2d74c6f76a209e1c36cf3c4d42c" "code"
"ext_spf_ac_program" "pk_920689a78eec92b1d3df0778917" "id"
"ext_spf_logical_station" "PK_4b06ae89b75a82eea4c9d4b6597" "id"
"ext_spf_prod_site" "pk_3903558ebb361b80dd910f279c9" "id"
"ext_tag" "pk_b3854f993092dacbf46550b2f4f" "id"
"ext_usr_user" "pk_9c530ad1f37e2a16dcc8cf87377" "username"
"external_link_node" "pk_5a45e83762204e743e8ffe0b02e" "object_id"
"r_test_description_node_chapter_node" "IDX_05e0f7d1f1d6a3ed69efa395d7" "child_node_id"
"r_test_description_node_chapter_node" "IDX_2d532fdf79f618d8968be9a01c" "parent_node_id"
"r_test_description_node_chapter_node" "pk_ee9ecf26f6d13535119096c686e" "parent_node_id"
"r_test_description_node_chapter_node" "pk_ee9ecf26f6d13535119096c686e" "child_node_id"
"r_test_procedure_node_test_unit_node" "IDX_02fd2faaaa95ecbc79b800580e" "child_node_id"
"r_test_procedure_node_test_unit_node" "IDX_43277ef8ec99e6849961e6f753" "parent_node_id"
"r_test_procedure_node_test_unit_node" "pk_6b1b30f65aeb1ea38f7d4c5054c" "parent_node_id"
"r_test_procedure_node_test_unit_node" "pk_6b1b30f65aeb1ea38f7d4c5054c" "child_node_id"
"r_test_root_node_attachment_node" "IDX_023d1ccb57bf62eb0e416c9a57" "child_node_id"
"r_test_root_node_attachment_node" "IDX_1afd533b8e0225d34aa4296f66" "parent_node_id"
"r_test_root_node_attachment_node" "pk_b71b09716e34babc1d909bb2596" "child_node_id"
"r_test_root_node_attachment_node" "pk_b71b09716e34babc1d909bb2596" "parent_node_id"
"r_test_root_node_compatible_st" "IDX_1b084bb256388137e8dacc6a4f" "node_object_id"
"r_test_root_node_compatible_st" "IDX_5c7b084d4be5d3cdb76ecd21f5" "ext_id"
"r_test_root_node_compatible_st" "PK_a2f2908df7c3c388077d1e6f7c3" "node_object_id"
"r_test_root_node_compatible_st" "PK_a2f2908df7c3c388077d1e6f7c3" "ext_id"
"r_test_root_node_ext_ref_ata" "IDX_15a68018d69beb38bdfa69b70c" "node_object_id"
"r_test_root_node_ext_ref_ata" "IDX_cf116974d178ad630b66ecd1f8" "ext_id"
"r_test_root_node_ext_ref_ata" "pk_eb4ac377637f5ceb6b90bcf0c1b" "ext_id"
"r_test_root_node_ext_ref_ata" "pk_eb4ac377637f5ceb6b90bcf0c1b" "node_object_id"
"r_test_root_node_ext_ref_ca" "IDX_d6407cff145959ca53bdae1b67" "ext_id"
"r_test_root_node_ext_ref_ca" "IDX_ee0f5a6fed3a63b556930e9580" "node_object_id"
"r_test_root_node_ext_ref_ca" "pk_ba7ec25548904cd793d9e780fe2" "ext_id"
"r_test_root_node_ext_ref_ca" "pk_ba7ec25548904cd793d9e780fe2" "node_object_id"
"r_test_root_node_ext_ref_fin" "IDX_9e594314b9a706ec52aa227b00" "node_object_id"
"r_test_root_node_ext_ref_fin" "IDX_ac8952cd8cbf2424a839f51b86" "ext_id"
"r_test_root_node_ext_ref_fin" "pk_ef72ac007e236fdcfb354dfcce8" "node_object_id"
"r_test_root_node_ext_ref_fin" "pk_ef72ac007e236fdcfb354dfcce8" "ext_id"
"r_test_root_node_ext_spf_prod_site" "IDX_4ab972ad15555172e68e01c047" "ext_id"
"r_test_root_node_ext_spf_prod_site" "IDX_d7461b317b01d414d72ef1921d" "node_object_id"
"r_test_root_node_ext_spf_prod_site" "pk_8deb35a540a1740c59fb4ec09d9" "node_object_id"
"r_test_root_node_ext_spf_prod_site" "pk_8deb35a540a1740c59fb4ec09d9" "ext_id"
"r_test_root_node_ext_tag" "IDX_a1af2f18b63c38bae16eea0117" "ext_id"
"r_test_root_node_ext_tag" "IDX_eaa5a7223fc32ef14285260b24" "node_object_id"
"r_test_root_node_ext_tag" "pk_142d28e8680266144d30bf5b9af" "ext_id"
"r_test_root_node_ext_tag" "pk_142d28e8680266144d30bf5b9af" "node_object_id"
"r_test_root_node_external_link_node" "IDX_9345d21892e27e9a49214e2bc1" "parent_node_id"
"r_test_root_node_external_link_node" "IDX_d8a27de16f8f9f7ee7a2752ebb" "child_node_id"
"r_test_root_node_external_link_node" "pk_c24fdc9d19be4184714af742ac4" "child_node_id"
"r_test_root_node_external_link_node" "pk_c24fdc9d19be4184714af742ac4" "parent_node_id"
"r_test_root_node_logical_st" "IDX_8641c5a17185cdad9d16a99589" "node_object_id"
"r_test_root_node_logical_st" "IDX_8b932582af835ebbb533868556" "ext_id"
"r_test_root_node_logical_st" "PK_17a7361140b5326cf4faff83a82" "ext_id"
"r_test_root_node_logical_st" "PK_17a7361140b5326cf4faff83a82" "node_object_id"
"r_test_root_node_test_description_node" "IDX_81d33a6a9563c41577478d3fb7" "parent_node_id"
"r_test_root_node_test_description_node" "IDX_b1a1d9076d2c19f1b961aae7e5" "child_node_id"
"r_test_root_node_test_description_node" "pk_803a0f6bf3aadbd497a10b83685" "parent_node_id"
"r_test_root_node_test_description_node" "pk_803a0f6bf3aadbd497a10b83685" "child_node_id"
"r_test_root_node_test_procedure_node" "IDX_a6a416d8ab88e2d53ddfa35e0b" "child_node_id"
"r_test_root_node_test_procedure_node" "IDX_ab832058fe3cd089c2ca954216" "parent_node_id"
"r_test_root_node_test_procedure_node" "pk_e7037371e536c9034a52c73c6bf" "child_node_id"
"r_test_root_node_test_procedure_node" "pk_e7037371e536c9034a52c73c6bf" "parent_node_id"
"release" "PK_1a2253436964eea9c558f9464f4" "id"
"release_approver" "PK_8679d35b0b83d5528c791eff3de" "id"
"release_checker" "PK_bf6acbf084e68d6777aacc5e9e4" "id"
"release_comment" "PK_fecf4efcbd373bfe8efb169a20a" "id"
"report" "PK_99e4d0bea58cba73c57f935a546" "id"
"test_description_node" "UQ_2be180cda3ed4488dcfba3db5d5" "start_rev"
"test_description_node" "UQ_2be180cda3ed4488dcfba3db5d5" "node_id"
"test_description_node" "pk_77a1e6f9e8ef568cc503aa291d3" "object_id"
"test_procedure_node" "UQ_f35233791742eaf7f3e69bc064d" "start_rev"
"test_procedure_node" "UQ_f35233791742eaf7f3e69bc064d" "node_id"
"test_procedure_node" "pk_57f51bcf74a4175322a7dd193bd" "object_id"
"test_root_artifact" "pk_0a59a848eada13a258968f056b2" "root_id"
"test_root_history" "PK_487fa43ef42115f91b5e0d35e93" "id"
"test_root_node" "UQ_1aff647d44dff994820090ae31f" "node_id"
"test_root_node" "UQ_1aff647d44dff994820090ae31f" "start_rev"
"test_root_node" "pk_54a38aaa1312a11f60c78340fb8" "object_id"
"test_root_revision_node" "IDX_fd47296459998d846f8aaf5d7c" "root_id"
"test_root_revision_node" "IDX_fd47296459998d846f8aaf5d7c" "status"
"test_root_revision_node" "IDX_fd47296459998d846f8aaf5d7c" "start_rev"
"test_root_revision_node" "PK_ebfe64a40be8b84ba0b11f20148" "object_id"
"test_root_revision_node" "UQ_9203c83320251254c301b461fcc" "start_rev"
"test_root_revision_node" "UQ_9203c83320251254c301b461fcc" "node_id"
"test_root_updater_backup" "PK_1fff2d2064b28ecabebd59eb5f3" "id"
"test_root_updater_backup" "UQ_4f7e4f45e3176ff030d98cbd03a" "user"
"test_root_updater_backup" "UQ_4f7e4f45e3176ff030d98cbd03a" "root_id"
"test_unit_node" "UQ_983f4c5181620ed69c4b1d30c16" "node_id"
"test_unit_node" "UQ_983f4c5181620ed69c4b1d30c16" "start_rev"
"test_unit_node" "pk_93c0e29964aa0071fff9584eb74" "object_id"
感觉一些左联接可以索引，但我不确定如何以正确的方式做到这一点。 除了索引之外，我还可以从TypeORM(或其他)中做些什么来真正加快请求速度？
这是解释输出：
"Limit (cost=1069379.70..1069379.73 rows=10 width=719)"
" -> Sort (cost=1069379.70..1069382.09 rows=955 width=719)"
" Sort Key: artifact.reference NULLS FIRST"
" -> Nested Loop Left Join (cost=1056844.28..1069359.06 rows=955 width=719)"
" Join Filter: (last_release_approver.""releaseId"" = last_release.id)"
" -> Nested Loop Left Join (cost=1056844.28..1069316.62 rows=955 width=744)"
" Join Filter: (last_release_checker.""releaseId"" = last_release.id)"
" -> Merge Left Join (cost=1056844.28..1069300.45 rows=955 width=720)"
" Merge Cond: ((node.root_id = release_in_production.root_id) AND (((SubPlan 3)) = release_in_production.rgti_revision))"
" -> Sort (cost=1056824.45..1056826.84 rows=955 width=702)"
" Sort Key: node.root_id, ((SubPlan 3))"
" -> Merge Left Join (cost=1045162.88..1056777.18 rows=955 width=702)"
" Merge Cond: ((node.root_id = last_release.root_id) AND (((SubPlan 4)) = last_release.rgti_revision))"
" -> Sort (cost=1045143.04..1045145.43 rows=955 width=668)"
" Sort Key: node.root_id, ((SubPlan 4))"
" -> Hash Semi Join (cost=4242.36..1045095.77 rows=955 width=668)"
" Hash Cond: (node.node_id = sub_node.node_id)"
" Join Filter: ((sub_node.start_rev <= node_target_revision_data.start_rev) AND ((sub_node.end_rev IS NULL) OR (node_target_revision_data.start_rev < sub_node.end_rev)))"
" -> Nested Loop Left Join (cost=2290.10..1043100.04 rows=8424 width=672)"
" -> Nested Loop Left Join (cost=2289.69..1035188.46 rows=754 width=672)"
" Join Filter: (ac_program.id = artifact.ac_program_id)"
" -> Nested Loop (cost=2289.69..1033603.66 rows=754 width=158)"
" -> Merge Left Join (cost=2237.79..2535.94 rows=19808 width=162)"
" Merge Cond: (node.root_id = artifact.root_id)"
" -> Sort (cost=2222.76..2272.28 rows=19808 width=134)"
" Sort Key: node.root_id"
" -> Seq Scan on test_root_node node (cost=0.00..809.08 rows=19808 width=134)"
" -> Sort (cost=15.03..15.55 rows=207 width=46)"
" Sort Key: artifact.root_id"
" -> Seq Scan on test_root_artifact artifact (cost=0.00..7.07 rows=207 width=46)"
" -> Index Only Scan using ""IDX_fd47296459998d846f8aaf5d7c"" on test_root_revision_node node_target_revision_data (cost=51.90..52.04 rows=1 width=20)"
" Index Cond: ((root_id = node.root_id) AND (start_rev >= node.start_rev) AND (start_rev = (SubPlan 2)))"
" Filter: ((node.end_rev IS NULL) OR (start_rev < node.end_rev))"
" SubPlan 2"
" -> Result (cost=51.48..51.49 rows=1 width=4)"
" InitPlan 1 (returns $1)"
" -> Limit (cost=0.41..51.48 rows=1 width=4)"
" -> Index Only Scan Backward using ""IDX_fd47296459998d846f8aaf5d7c"" on test_root_revision_node (cost=0.41..357.88 rows=7 width=4)"
" Index Cond: ((root_id = node.root_id) AND (start_rev IS NOT NULL))"
" Filter: ((status)::text = ANY ('{PUBLISHED,RELEASED,CANCELED}'::text[]))"
" -> Materialize (cost=0.00..12.10 rows=140 width=524)"
" -> Seq Scan on ext_spf_ac_program ac_program (cost=0.00..11.40 rows=140 width=524)"
" -> Index Scan using ""IDX_fd47296459998d846f8aaf5d7c"" on test_root_revision_node rev (cost=0.41..10.38 rows=11 width=40)"
" Index Cond: ((root_id = artifact.root_id) AND (start_rev <= node_target_revision_data.start_rev))"
" Filter: ((end_rev IS NULL) OR (node_target_revision_data.start_rev < end_rev))"
" -> Hash (cost=1692.48..1692.48 rows=20782 width=24)"
" -> Hash Join (cost=1057.83..1692.48 rows=20782 width=24)"
" Hash Cond: (sub_node_sub_sites.node_object_id = sub_node.object_id)"
" -> Hash Join (cost=1.15..581.23 rows=20782 width=16)"
" Hash Cond: ((sub_node_sub_sites.ext_id)::text = (sub_sites.id)::text)"
" -> Seq Scan on r_test_root_node_ext_spf_prod_site sub_node_sub_sites (cost=0.00..475.77 rows=25977 width=33)"
" -> Hash (cost=1.10..1.10 rows=4 width=82)"
" -> Seq Scan on ext_spf_prod_site sub_sites (cost=0.00..1.10 rows=4 width=82)"
" Filter: ((id)::text = ANY ('{CC,BB,AA,A,B,C,D,E}'::text[]))"
" -> Hash (cost=809.08..809.08 rows=19808 width=40)"
" -> Seq Scan on test_root_node sub_node (cost=0.00..809.08 rows=19808 width=40)"
" SubPlan 4"
" -> Aggregate (cost=9.43..9.44 rows=1 width=4)"
" -> Seq Scan on release subrelease (cost=0.00..9.43 rows=3 width=4)"
" Filter: (root_id = node.root_id)"
" -> Sort (cost=19.83..20.52 rows=274 width=50)"
" Sort Key: last_release.root_id, last_release.rgti_revision"
" -> Seq Scan on release last_release (cost=0.00..8.74 rows=274 width=50)"
" SubPlan 3"
" -> Aggregate (cost=10.12..10.13 rows=1 width=4)"
" -> Seq Scan on release lastreleasedsub (cost=0.00..10.11 rows=3 width=4)"
" Filter: ((root_id = node.root_id) AND ((status)::text = 'IN_PRODUCTION'::text))"
" -> Sort (cost=19.83..20.52 rows=274 width=34)"
" Sort Key: release_in_production.root_id, release_in_production.rgti_revision"
" -> Seq Scan on release release_in_production (cost=0.00..8.74 rows=274 width=34)"
" -> Materialize (cost=0.00..1.85 rows=1 width=40)"
" -> Seq Scan on release_checker last_release_checker (cost=0.00..1.85 rows=1 width=40)"
" Filter: ((""user"")::text = 'test'::text)"
" -> Materialize (cost=0.00..1.85 rows=1 width=40)"
" -> Seq Scan on release_approver last_release_approver (cost=0.00..1.85 rows=1 width=40)"
" Filter: ((""user"")::text = 'test'::text)"
"JIT:"
" Functions: 115"
" Options: Inlining true, Optimization true, Expressions true, Deforming true"
类型ORM代码：
const query: AlmQueryBuilder<TestRootNodeEntity> = this.almManager.createQueryBuilder({
targetStatus: [TestRootStatusEnum.PUBLISHED, TestRootStatusEnum.RELEASED, TestRootStatusEnum.CANCELED]
});
query.from(TestRootNodeEntity, 'node');
// common joins from test root attributes
query
.leftJoin('node.artifact', 'artifact')
.leftJoin('artifact.created_by', 'created_by')
.leftJoin('artifact.ac_program', 'ac_program')
.leftJoin('artifact.revisions', 'rev') // It's a list but alm will retrieve only one revision
.leftJoin('node.owner', 'owner')
.leftJoin('node.updated_by', 'updated_by');
// Last release in production
query.leftJoin(
ReleaseEntity,
'release_in_production',
'release_in_production.root_id = node.root_id AND release_in_production.rgti_revision = ' +
this.sqGetReleaseInProductionRevision(query, 'node.root_id')
);
// Last release of the rgti
query.leftJoin(
ReleaseEntity,
'last_release',
'last_release.root_id = node.root_id AND last_release.rgti_revision = ' +
this.sqGetMaxReleaseRevision(query, 'node.root_id')
);
// Last release - user checker if present
query.leftJoin(
ReleaseCheckerEntity,
'last_release_checker',
'last_release_checker.release.id = last_release.id ' + 'AND last_release_checker.user.username = :username',
{ username }
);
// Last release - user approver if present
query.leftJoin(
ReleaseApproverEntity,
'last_release_approver',
'last_release_approver.release.id = last_release.id ' +
'AND last_release_approver.user.username = :username',
{ username }
);
query
.select('node.node_id', 'node_id')
.addSelect('node.root_id', 'root_id')
.addSelect('artifact.reference', 'reference')
.addSelect('artifact.created_on', 'created_on')
.addSelect('artifact.current_rev', 'current_rev')
.addSelect('ac_program.name', 'ac_program_name')
.addSelect('node.updated_on', 'updated_on')
.addSelect('node.lang', 'lang')
.addSelect('node.second_lang', 'second_lang')
.addSelect('node.title', 'title')
.addSelect('node.execution_modes', 'execution_modes')
.addSelect('release_in_production.name', 'release_in_production_name')
.addSelect('release_in_production.in_production_date', 'release_in_production_date')
.addSelect('release_in_production.rgti_revision', 'release_in_production_rgti_revision')
.addSelect(this.queryHasPendingReview(), 'has_pending_review')
.addSelect('last_release.id', 'review_release_id')
.addSelect(this.queryDashboardStatus(), 'status');
query.offset(isNil(pagination.offset) ? 0 : pagination.offset);
query.limit(isNil(pagination.limit) ? 50 : pagination.limit);
请随时询问更多信息。
重新思考怪物查询的一些评论和建议：
(A) 删除查询其余部分未使用的LEFT JOINed 表
查询的其余部分不使用几个LEFT JOIN表，因此可以简单地从查询中删除它们：
LEFT JOIN "ext_usr_user" "created_by" ON "created_by"."username" = "artifact"."created_by"
LEFT JOIN "ext_usr_user" "owner" ON "owner"."username" = "node"."owner"
LEFT JOIN "ext_usr_user" "updated_by" ON "updated_by"."username" = "node"."updated_by"
(B) 使用范围类型优化一些时态语句
有几个类似的说法，例如
("node"."start_rev" <= "node_target_revision_data"."start_rev" AND ("node"."end_rev" IS NULL OR "node_target_revision_data"."start_rev" < "node"."end_rev"))"
可以替换为
tsrange("node"."start_rev", "node"."end_rev") @> "node_target_revision_data"."start_rev"
为了避免可能比AND更昂贵的OR条款。
如果start_rev和end_rev属于Timestamp without time zone类型，则使用tsrange()，或者对于Timestamp with time zone类型必须替换为tstzrange()，对于date类型，必须将其替换为daterange()。
请注意，当"node"."end_rev" IS NULL时，tsrange("node"."start_rev", "node"."end_rev")的上限是无穷大，因此当条件tsrange("node"."start_rev", "node"."end_rev") @> "node_target_revision_data"."start_rev")为真时"node_target_revision_data"."start_rev" >= "node"."start_rev"始终为真。
此更改可能适用于：
(1)LEFT JOIN "test_root_revision_node" "rev"ON条款：
LEFT JOIN "test_root_revision_node" "rev"
ON "rev"."root_id" = "artifact"."root_id"
AND tsrange("rev"."start_rev", "rev"."end_rev") @> "node_target_revision_data"."start_rev"
(2)最后WHERE条款的第一部分：
WHERE tsrange("node"."start_rev", "node"."end_rev"= @> "node_target_revision_data"."start_rev"
(3)最终WHERE子句第二部分的子查询：
WHERE tsrange("sub_node"."start_rev", "sub_node"."end_rev") @> "node_target_revision_data"."start_rev"
为了使现有索引适应这些查询更改，您可以将其中一些替换为新索引：
-- this new index replaces "UQ_1aff647d44dff994820090ae31f"
CREATE INDEX IF NOT EXISTS index_name ON "test_root_node"
USING GiST (node_id, tsrange(start_rev, end_rev)) ;
-- this new index replaces "IDX_fd47296459998d846f8aaf5d7c"
CREATE INDEX IF NOT EXISTS index_name ON "test_root_revision_node"
USING GiST (root_id, tsrange(start_rev, end_rev)) ;
(C) 最后一个 WHERE 子句中的子查询看起来像超重
子查询 (SELECT 1 FROM "test_root_node" "sub_node" ...)将提供来自主表的行"test_root_node"LEFT JOINed 表中存在(或不存在)的任何内容"r_test_root_node_ext_spf_prod_site"和"ext_spf_prod_site"。因此，在这个特定的子查询中，LEFT JOINed 表听起来毫无用处，可以像WHERE子句中的相应条件一样删除。因此，此子查询可以简化为：
AND EXISTS
(SELECT 1
FROM "test_root_node" "sub_node"
WHERE tsrange("sub_node"."start_rev", "sub_node"."end_rev") @> "node_target_revision_data"."start_rev"
AND "sub_node"."node_id" = "node"."node_id"
)
但更进一步看，如果主要查询
SELECT *
FROM "test_root_node" "node"
LEFT JOIN "test_root_revision_node" "node_target_revision_data"
ON "node_target_revision_data"."root_id" = "node"."root_id"
AND "node_target_revision_data"."start_rev" =
( SELECT MAX(START_REV)
FROM "test_root_revision_node"
WHERE ROOT_ID = "node".ROOT_ID
AND STATUS IN ('PUBLISHED','RELEASED','CANCELED'))
WHERE int4range("node"."start_rev", "node"."end_rev") @> "node_target_revision_data"."start_rev"
返回一行或多行，然后对于其中的每一行，以下条件将为真，因为返回的行符合该条件：
EXISTS
(SELECT 1
FROM "test_root_node" "sub_node"
WHERE tsrange("sub_node"."start_rev", "sub_node"."end_rev") @> "node_target_revision_data"."start_rev"
AND "sub_node"."node_id" = "node"."node_id"
)
所以最后这个子查询看起来只是多余的，主要WHERE子句的第一部分可以减少如下：
WHERE tsrange("node"."start_rev", "node"."end_rev") @> "node_target_revision_data"."start_rev"
(d) 重新设计LEFT JOIN "test_root_revision_node" "node_target_revision_data"
完整的ON子句可以重新纳入LEFT JOIN，由于在WHERE子句中引用了"node"."root_id"、"node"."start_rev"和"node"."end_rev"而变得LEFT JOIN LATERAL：
LEFT JOIN LATERAL
( SELECT "node_target_revision_data"."start_rev"
FROM "test_root_revision_node" "node_target_revision_data"
WHERE "node_target_revision_data"."root_id" = "node"."root_id"
AND "node_target_revision_data"."status" IN ('PUBLISHED','RELEASED','CANCELED')
AND tsrange("node"."start_rev", "node"."end_rev") @> "node_target_revision_data"."start_rev"
ORDER BY "node_target_revision_data"."start_rev" DESC
LIMIT 1
) "node_target_revision_data"
ON True
对node表列的引用也可以外部化，以便返回到LEFT JOIN形式：
LEFT JOIN
( SELECT "node_target_revision_data"."start_rev"
FROM "test_root_revision_node" "node_target_revision_data"
WHERE "node_target_revision_data"."status" IN ('PUBLISHED','RELEASED','CANCELED')
ORDER BY "node_target_revision_data"."start_rev" DESC
LIMIT 1
) "node_target_revision_data"
ON "node_target_revision_data"."root_id" = "node"."root_id"
AND tsrange("node"."start_rev", "node"."end_rev") @> "node_target_revision_data"."start_rev"
(E) 对最后WHERE条款的致命打击
比较 (C) 和 (D) 的结果，很明显，最终的WHERE子句只是与第一个LEFT JOIN LATERAL中的部分WHERE子句冗余，因此最终的WHERE子句看起来毫无用处，可以从最终查询中完全删除。
(f) 重新设计LEFT JOIN "release" "release_in_production"
与(D)类似，完整的ON子句可以重新纳入LEFT JOIN，由于WHERE子句中对"node"."root_id"的引用，该变得LEFT JOIN LATERAL：
LEFT JOIN LATERAL
( SELECT "release_in_production"."name"
, "release_in_production"."rgti_revision"
, "release_in_production"."in_production_date"
FROM "release" "release_in_production"
WHERE "release_in_production"."root_id" = "node"."root_id"
AND "release_in_production"."status" = 'IN_PRODUCTION'
ORDER BY "release_in_production"."rgti_revision" DESC
LIMIT 1
) "release_in_production"
ON True
对node表列的引用也可以外部化，以便返回到LEFT JOIN形式：
LEFT JOIN
( SELECT "release_in_production"."name"
, "release_in_production"."rgti_revision"
, "release_in_production"."in_production_date"
FROM "release" "release_in_production"
WHERE "release_in_production"."status" = 'IN_PRODUCTION'
ORDER BY "release_in_production"."rgti_revision" DESC
LIMIT 1
) "release_in_production"
ON "release_in_production"."root_id" = "node"."root_id"
(g) 重新设计LEFT JOIN "release" "last_release"
与(D)类似，完整的ON子句可以重新纳入LEFT JOIN，由于WHERE子句中对"node"."root_id"的引用而变得LEFT JOIN LATERAL：
LEFT JOIN LATERAL
( SELECT "last_release"."id"
, "last_release"."status"
, "last_release"."rgti_revision"
FROM "release" "last_release"
WHERE "last_release"."root_id" = "node"."root_id"
ORDER BY "last_release"."rgti_revision" DESC
LIMIT 1
) "last_release"
ON True
对node表列的引用也可以外部化，以便返回到LEFT JOIN形式：
LEFT JOIN
( SELECT "last_release"."id"
, "last_release"."status"
, "last_release"."rgti_revision"
FROM "release" "last_release"
ORDER BY "last_release"."rgti_revision" DESC
LIMIT 1
) "last_release"
ON "last_release"."root_id" = "node"."root_id"
(h) 重新设计LEFT JOIN "release_checker" "last_release_checker"
ON子句是指变量$1，该变量应出现在查询的最终WHERE子句中，或者出现在此LEFT JOIN子句中，如果完整的ON子句重新集成到LEFT JOIN LATERAL的WHERE子句中，该子句由于引用"last_release"."id"而变得LEFT JOIN LATERAL：
LEFT JOIN LATERAL
( SELECT "last_release_checker"."id"
, "last_release_checker"."review_date"
FROM "release_checker" "last_release_checker"
WHERE "last_release_checker"."releaseId" = "last_release"."id"
AND "last_release_checker"."user" = $1
) "last_release_checker"
ON True
(一) 重新设计LEFT JOIN "release_approver" "last_release_approver"
与(H)类似，完整的ON子句可以重新纳入LEFT JOIN，由于WHERE子句中对"last_release"."id"的引用，该变得LEFT JOIN LATERAL：
LEFT JOIN LATERAL
( SELECT "last_release_approver"."id"
, "last_release_approver"."review_date"
FROM "release_approver" "last_release_approver"
WHERE "last_release_approver"."releaseId" = "last_release"."id"
AND "last_release_approver"."user" = $2
) "last_release_approver"
ON True
(J) 考虑到ORDER BY条款和LIMIT条款
LIMIT 10子句直接链接到ORDER BY "artifact"."reference" ASC NULLS FIRST子句，子句FROM中仅涉及两个表，因此要执行：
FROM "test_root_node" "node"
LEFT JOIN "test_root_artifact" "artifact"
ON "artifact"."root_id" = "node"."root_id"
所以这里的想法是将这部分查询隔离在 cte 中，以便将结果行数显着限制为 10，这些行将存储在临时表中，然后连接其他表以获得最终结果：
WITH limited_list AS
(
SELECT "node".*
, "artifact"."created_by"
, "artifact"."created_on"
, "artifact"."updated_by"
, "artifact"."reference"
FROM "test_root_node" "node"
LEFT JOIN "test_root_artifact" "artifact"
ON "artifact"."root_id" = "node"."root_id"
ORDER BY "artifact"."reference" ASC NULLS FIRST
LIMIT 10
)
SELECT [the rest of the final SELECT clause]
FROM limited_list AS l
LEFT JOIN [the rest of the FROM clause]
(K) 缩短查询规划时间
查询中仍然有许多 LEFT JOINed 表，因此 pg 计划程序可能会花费大量时间来调查要执行的最佳计划，即使LEFT JOIN LATERAL强制执行执行顺序也是如此。可以明确定义优先顺序，以帮助规划者更快地找到最佳计划。
(L) 分析的最终结果
最终查询可能如下所示：
WITH limited_list AS
(
SELECT "node".*
, "artifact"."created_by"
, "artifact"."created_on"
, "artifact"."updated_by"
, "artifact"."reference"
FROM "test_root_node" "node"
LEFT JOIN "test_root_artifact" "artifact"
ON "artifact"."root_id" = "node"."root_id"
ORDER BY "artifact"."reference" ASC NULLS FIRST
LIMIT 10
) SELECT l."node_id" AS "node_id",
l."updated_on" AS "updated_on",
l."lang" AS "lang",
l."second_lang" AS "second_lang",
l."title" AS "title",
l."execution_modes" AS "execution_modes",
l."current_rev" AS "current_rev",
l."created_on" AS "created_on",
l."reference" AS "reference",
"ac_program"."name" AS "ac_program_name",
"release_in_production"."rgti_revision" AS "release_in_production_rgti_revision",
"release_in_production"."name" AS "release_in_production_name",
"release_in_production"."in_production_date" AS "release_in_production_date",
"last_release"."id" AS "review_release_id",
l."root_id" AS "root_id",
( ("last_release"."status" = 'CHECK_IN_PROGRESS'
AND "last_release_checker"."id" IS NOT NULL
AND "last_release_checker"."review_date" IS NULL)
OR("last_release"."status" = 'APPROVAL_IN_PROGRESS'
AND "last_release_approver"."id" IS NOT NULL
AND "last_release_approver"."review_date" IS NULL)
) AS "has_pending_review",
CASE
WHEN "last_release"."rgti_revision" = l."current_rev"
AND "last_release"."status" = 'IN_PRODUCTION'
THEN 'DASHBOARD_STATUS_RELEASED'
WHEN "last_release"."rgti_revision" = l."current_rev"
AND "last_release"."status" = 'CHECK_IN_PROGRESS'
THEN 'DASHBOARD_STATUS_CHECK_IN_PROGRESS'
WHEN "last_release"."rgti_revision" = l."current_rev"
AND "last_release"."status" = 'APPROVAL_IN_PROGRESS'
THEN 'DASHBOARD_STATUS_APPROVAL_IN_PROGRESS'
WHEN "last_release"."rgti_revision" = l."current_rev"
AND "last_release"."status" = 'APPROVED'
THEN 'DASHBOARD_STATUS_APPROVED'
WHEN "rev"."status" = 'CANCELED'
THEN 'DASHBOARD_STATUS_CANCELED'
ELSE 'DASHBOARD_STATUS_WORK_IN_PROGRESS'
END AS "status"
FROM limted_list AS l
LEFT JOIN
( SELECT "node_target_revision_data"."start_rev"
FROM "test_root_revision_node" "node_target_revision_data"
WHERE "node_target_revision_data"."status" IN ('PUBLISHED','RELEASED','CANCELED')
ORDER BY "node_target_revision_data"."start_rev" DESC
LIMIT 1
) "node_target_revision_data"
ON "node_target_revision_data"."root_id" = l."root_id"
AND tsrange(l."start_rev", l."end_rev") @> "node_target_revision_data"."start_rev"
LEFT JOIN "ext_spf_ac_program" "ac_program"
ON "ac_program"."id" = l."ac_program_id"
LEFT JOIN "test_root_revision_node" "rev"
ON "rev"."root_id" = l."root_id"
AND tsrange("rev"."start_rev", "rev"."end_rev") @> "node_target_revision_data"."start_rev"
LEFT JOIN LATERAL
( SELECT "release_in_production"."name"
, "release_in_production"."rgti_revision"
, "release_in_production"."in_production_date"
FROM "release" "release_in_production"
WHERE "release_in_production"."root_id" = l."root_id"
AND "release_in_production"."status" = 'IN_PRODUCTION'
ORDER BY "release_in_production"."rgti_revision" DESC
LIMIT 1
) "release_in_production"
ON True
LEFT JOIN LATERAL
( SELECT "last_release"."id"
, "last_release"."status"
, "last_release"."rgti_revision"
FROM "release" "last_release"
WHERE "last_release"."root_id" = l."root_id"
ORDER BY "last_release"."rgti_revision" DESC
LIMIT 1
) "last_release"
ON True
LEFT JOIN LATERAL
( SELECT "last_release_checker"."id"
, "last_release_checker"."review_date"
FROM "release_checker" "last_release_checker"
WHERE "last_release_checker"."releaseId" = "last_release"."id"
AND "last_release_checker"."user" = $1
) "last_release_checker"
ON True
LEFT JOIN LATERAL
( SELECT "last_release_approver"."id"
, "last_release_approver"."review_date"
FROM "release_approver" "last_release_approver"
WHERE "last_release_approver"."releaseId" = "last_release"."id"
AND "last_release_approver"."user" = $2
) "last_release_approver"
ON True ;
(M)LATERAL JOIN可能不是性能的最佳选择
最终查询仍然包括许多横向连接，这些连接通常不是性能竞赛的赢家。它们中的大多数可以通过提取其WHERE子句中的条件来转换回LEFT JOIN，这些条件明确引用其他连接的表并将它们放入复活LEFT JOIN的ON子句中。
(N) 警告
显然，我既不能检查最终查询是否有效，也不能提供预期的结果，也不能证明性能会得到改进，但我希望这种分析将为改进和/或重新设计提供一些方向。任何反馈将不胜感激。
吸尘的 2 倍速度很好，但我认为它会远不止于此。
远远超过一半的原始时间都流向了这个节点，所以即使速度是 2 倍，它仍然必须很慢。
Index Only Scan Backward using ""IDX_fd47296459998d846f8aaf5d7c"" on test_root_revision_node (cost=0.41..357.88 rows=7 width=4) (actual time=0.472..0.472 rows=1 loops=19835)"
Index Cond: ((root_id = node.root_id) AND (start_rev IS NOT NULL))"
Filter: ((status)::text = ANY ('{PUBLISHED,RELEASED,CANCELED}'::text[]))"
Rows Removed by Filter: 1026"
Heap Fetches: 20376052"
Buffers: shared hit=15008089"
这样做是跳到索引中root_id具有所需值的部分的末尾，按start_rev顺序向后走，直到找到满足status=ANY ('{PUBLISHED,RELEASED,CANCELED}')条件的第一行，然后停止。 平均而言，它必须向下走过 1026 个状态错误的条目，然后才能找到一个状态正确的条目。 我不知道这是因为这些状态非常罕见，还是因为它们偏向于仅在start_rev序列的早期发生。
这种情况的理想索引是：
CREATE INDEX ON public.test_root_revision_node (root_id, start_rev)
where status in ('PUBLISHED','RELEASED','CANCELED')
有了这个索引，它应该能够立即拉出start_rev的最大值，对于每个给定root_id具有正确的状态。 如果这些状态很少见，那么这个指数也应该很小。
当然，如果列出的这三个状态不是一成不变的，而是从复选框列表中选择的，并且每次都会有所不同，那么此索引对您来说将不是太大的解决方案。
这是我自下而上的分析。 爱德华的自上而下的分析也很好，很高兴知道你是否采用了这些变化中的任何一个以及它们是如何工作的。