The following TypeORM generated SQL query takes over 11 sec to complete :
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;
Given the following database indexes :
"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"
It feels like some left joins could be indexed, but I am unsure how to do it the proper way. Besides from indexing, is there anything I could do from TypeORM (or other), to really speed up the request?
Here is the EXPLAIN output :
"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"
TypeORM code :
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);
Feel free to ask for more information.
Advertisement
Answer
The 2 fold speed up from vacuuming is nice, but I thought it would be much more than that.
Far more than half the original time is going to this one node, so even with a 2 fold speed it still must be slow.
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"
What this is doing is jumping to the end of the part of the index where root_id has the desired value, walking backwards down the order of start_rev until it finds the first row which meets status=ANY ('{PUBLISHED,RELEASED,CANCELED}') condition, then stopping. And on average it has to walk down over 1026 entries with the wrong status before finding one entry with a right status. I don’t know if that is because those statuses are very rare, or because they are biased to occur only early on in the sequence of start_rev.
The ideal index for this case would be:
CREATE INDEX ON public.test_root_revision_node (root_id, start_rev)
where status in ('PUBLISHED','RELEASED','CANCELED')
With that index, it should be able to pull out the max value of start_rev which has the correct status, for each given root_id, pretty much immediately. And if those statuses are rare, then this index should also be pretty small.
Of course if those three listed statuses are not set in stone, but were just selected from a list of checkboxes and will be different each time, then this index won’t be much of a solution for you.
That is my bottom up analysis. Edouard’s top down analysis is also good, and it would be nice to know if you adopted any of those changes and how they worked.