-
QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=27917892.40..27917892.41 rows=1 width=8) (actual time=19755.516..19755.516 rows=1 loops=1) -> Nested Loop (cost=27762451.92..27917892.40 rows=2 width=0) (actual time=19375.887..19755.467 rows=54 loops=1) -> Nested Loop Semi Join (cost=27762451.49..27917884.48 rows=2 width=12) (actual time=19375.842..19753.226 rows=62 loops=1) -> Nested Loop (cost=27762451.06..27917787.68 rows=214 width=8) (actual time=19375.810..19751.678 rows=62 loops=1) -> Merge Join (cost=27762450.63..27915142.07 rows=384 width=4) (actual time=19375.739..19750.066 rows=62 loops=1) Merge Cond: (merge_requests_2.id = merge_requests.id) -> Unique (cost=27760721.76..27804343.37 rows=8724323 width=4) (actual time=19194.573..19511.556 rows=220515 loops=1) -> Sort (cost=27760721.76..27782532.56 rows=8724323 width=4) (actual time=19194.572..19335.048 rows=432936 loops=1) Sort Key: merge_requests_2.id Sort Method: external merge Disk: 5928kB -> Append (cost=12823443.05..26678275.11 rows=8724323 width=4) (actual time=2082.666..18756.686 rows=433223 loops=1) -> Hash Join (cost=12823443.05..13421693.08 rows=5608213 width=4) (actual time=2082.664..8138.679 rows=212806 loops=1) Hash Cond: (merge_requests_2.source_project_id = projects_1.id) -> Seq Scan on merge_requests merge_requests_2 (cost=0.00..494321.13 rows=5608213 width=8) (actual time=0.027..3579.847 rows=5220945 loops=1) -> Hash (cost=12790176.92..12790176.92 rows=2027610 width=4) (actual time=2077.888..2077.888 rows=397996 loops=1) Buckets: 524288 Batches: 8 Memory Usage: 5859kB -> Seq Scan on projects projects_1 (cost=0.00..12790176.92 rows=2027610 width=4) (actual time=1.572..1913.043 rows=397996 loops=1) Filter: ((alternatives: SubPlan 7 or hashed SubPlan 8) OR (visibility_level = ANY ('{10,20}'::integer[]))) Rows Removed by Filter: 3091442 SubPlan 7 -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_6 (cost=0.43..3.45 rows=1 width=0) (never executed) Index Cond: ((user_id = 1642716) AND (project_id = projects_1.id)) Heap Fetches: 0 SubPlan 8 -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_7 (cost=0.43..6.25 rows=85 width=4) (actual time=1.173..1.387 rows=271 loops=1) Index Cond: (user_id = 1642716) Heap Fetches: 3 -> Merge Semi Join (cost=7277.96..13169338.81 rows=3116110 width=4) (actual time=2.628..10414.296 rows=220417 loops=1) Merge Cond: (merge_requests_3.target_project_id = projects_2.id) -> Index Only Scan using index_merge_requests_on_tp_id_and_merge_commit_sha_and_id on merge_requests merge_requests_3 (cost=0.56..202022.82 rows=5608213 width=8) (actual time=0.049..2956.221 rows=5220945 loops=1) Heap Fetches: 59203 -> Materialize (cost=0.43..12917478.82 rows=2027610 width=4) (actual time=0.903..5922.883 rows=397993 loops=1) -> Index Scan using projects_pkey on projects projects_2 (cost=0.43..12912409.79 rows=2027610 width=4) (actual time=0.900..5699.716 rows=397993 loops=1) Filter: ((alternatives: SubPlan 5 or hashed SubPlan 6) OR (visibility_level = ANY ('{10,20}'::integer[]))) Rows Removed by Filter: 3091393 SubPlan 5 -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_4 (cost=0.43..3.45 rows=1 width=0) (never executed) Index Cond: ((user_id = 1642716) AND (project_id = projects_2.id)) Heap Fetches: 0 SubPlan 6 -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_5 (cost=0.43..6.25 rows=85 width=4) (actual time=0.022..0.132 rows=271 loops=1) Index Cond: (user_id = 1642716) Heap Fetches: 3 -> Sort (cost=1728.87..1730.79 rows=768 width=8) (actual time=179.706..179.941 rows=708 loops=1) Sort Key: merge_requests.id Sort Method: quicksort Memory: 58kB -> Bitmap Heap Scan on merge_requests (cost=540.17..1692.06 rows=768 width=8) (actual time=19.795..179.313 rows=708 loops=1) Recheck Cond: (((title)::text ~~* '%test1%'::text) OR (description ~~* '%test1%'::text)) Rows Removed by Index Recheck: 93 Filter: (deleted_at IS NULL) Rows Removed by Filter: 13 Heap Blocks: exact=789 -> BitmapOr (cost=540.17..540.17 rows=771 width=0) (actual time=18.425..18.425 rows=0 loops=1) -> Bitmap Index Scan on index_merge_requests_on_title_trigram (cost=0.00..144.86 rows=514 width=0) (actual time=8.814..8.814 rows=533 loops=1) Index Cond: ((title)::text ~~* '%test1%'::text) -> Bitmap Index Scan on index_merge_requests_on_description_trigram (cost=0.00..394.93 rows=257 width=0) (actual time=9.608..9.608 rows=298 loops=1) Index Cond: (description ~~* '%test1%'::text) -> Index Scan using projects_pkey on projects (cost=0.43..6.88 rows=1 width=4) (actual time=0.023..0.023 rows=1 loops=62) Index Cond: (id = merge_requests.target_project_id) Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (visibility_level = ANY ('{10,20}'::integer[]))) SubPlan 1 -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations (cost=0.43..3.45 rows=1 width=0) (actual time=0.006..0.006 rows=0 loops=62) Index Cond: ((user_id = 1642716) AND (project_id = projects.id)) Heap Fetches: 0 SubPlan 2 -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_1 (cost=0.43..6.25 rows=85 width=4) (never executed) Index Cond: (user_id = 1642716) Heap Fetches: 0 -> Index Scan using index_merge_requests_on_target_project_id_and_iid on merge_requests merge_requests_1 (cost=0.43..4.09 rows=188 width=4) (actual time=0.024..0.024 rows=1 loops=62) Index Cond: (target_project_id = projects.id) Filter: (deleted_at IS NULL) -> Index Scan using index_project_features_on_project_id on project_features (cost=0.43..3.95 rows=1 width=8) (actual time=0.033..0.034 rows=1 loops=62) Index Cond: (project_id = projects.id) Filter: ((merge_requests_access_level = ANY ('{NULL,20}'::integer[])) OR ((merge_requests_access_level = 10) AND (alternatives: SubPlan 3 or hashed SubPlan 4))) Rows Removed by Filter: 0 SubPlan 3 -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_2 (cost=0.43..3.45 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=8) Index Cond: ((user_id = 1642716) AND (project_id = projects.id)) Heap Fetches: 0 SubPlan 4 -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_3 (cost=0.43..6.25 rows=85 width=4) (never executed) Index Cond: (user_id = 1642716) Heap Fetches: 0 Planning time: 9.251 ms Execution time: 19758.517 ms (85 rows) -
Pretty formatted query:
SELECT COUNT(*) FROM "merge_requests" INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE "merge_requests"."deleted_at" IS NULL AND (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 1642716 AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (10, 20)) AND "projects"."id" IN (SELECT "merge_requests"."target_project_id" FROM "merge_requests" WHERE "merge_requests"."deleted_at" IS NULL) AND ("project_features"."merge_requests_access_level" IN (NULL, 20) OR ("project_features"."merge_requests_access_level" = 10 AND EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 1642716 AND (project_authorizations.project_id = projects.id)))) AND (merge_requests.id IN (SELECT "merge_requests"."id" FROM "merge_requests" WHERE "merge_requests"."source_project_id" IN (SELECT "projects"."id" FROM "projects" WHERE (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 1642716 AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (10, 20))) UNION SELECT "merge_requests"."id" FROM "merge_requests" WHERE "merge_requests"."target_project_id" IN (SELECT "projects"."id" FROM "projects" WHERE (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 1642716 AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (10, 20))))) AND ("merge_requests"."title" ILIKE '%test1%' OR "merge_requests"."description" ILIKE '%test1%');Couple of possible optimizations:
- Authorization on target_project is done twice.
- The whole "AND ... UNION" block could be probably removed because it checks if user is authorized to access source or target projects, but we already check if user can access target project in earlier "AND ..." filter.
- the whole query could be flatten by left joining target project_authorization
So simplified query might look like:
SELECT COUNT(*) FROM "merge_requests" INNER JOIN projects target_projects ON "target_projects"."id" = "merge_requests"."target_project_id" LEFT JOIN project_features target_features ON target_projects.id = target_features.project_id LEFT OUTER JOIN project_authorizations target_authorizations ON (target_projects.id = target_authorizations.project_id AND target_authorizations.user_id = 1642716) WHERE "merge_requests"."deleted_at" IS NULL AND (target_authorizations.access_level is not NULL OR target_projects.visibility_level IN (10, 20)) AND ("target_features"."merge_requests_access_level" IN (NULL, 20) OR ("target_features"."merge_requests_access_level" = 10 AND target_authorizations.access_level is not NULL)) AND ("merge_requests"."title" ILIKE '%test1%' OR "merge_requests"."description" ILIKE '%test1%')Edited by Jan Provaznik -
🔴 @smcgivern@jprovaznik query plan for that most recent one is:
QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------- Aggregate (cost=5217.24..5217.25 rows=1 width=8) (actual time=85.951..85.951 rows=1 loops=1) -> Nested Loop (cost=814.22..5215.48 rows=701 width=0) (actual time=33.994..85.916 rows=54 loops=1) Join Filter: ((target_features.merge_requests_access_level = ANY ('{NULL,20}'::integer[])) OR ((target_features.merge_requests_access_level = 10) AND (target_authorizations .access_level IS NOT NULL))) Rows Removed by Join Filter: 8 -> Hash Left Join (cost=813.79..4828.58 rows=797 width=12) (actual time=33.947..83.620 rows=62 loops=1) Hash Cond: (target_projects.id = target_authorizations.project_id) Filter: ((target_authorizations.access_level IS NOT NULL) OR (target_projects.visibility_level = ANY ('{10,20}'::integer[]))) Rows Removed by Filter: 647 -> Nested Loop (cost=807.83..4736.94 rows=797 width=12) (actual time=30.136..82.848 rows=709 loops=1) -> Bitmap Heap Scan on merge_requests (cost=807.40..2002.31 rows=797 width=4) (actual time=30.072..66.466 rows=709 loops=1) Recheck Cond: (((title)::text ~~* '%test1%'::text) OR (description ~~* '%test1%'::text)) Rows Removed by Index Recheck: 93 Filter: (deleted_at IS NULL) Rows Removed by Filter: 13 Heap Blocks: exact=790 -> BitmapOr (cost=807.40..807.40 rows=800 width=0) (actual time=29.494..29.494 rows=0 loops=1) -> Bitmap Index Scan on index_merge_requests_on_title_trigram (cost=0.00..245.50 rows=533 width=0) (actual time=13.245..13.245 rows=534 loops=1) Index Cond: ((title)::text ~~* '%test1%'::text) -> Bitmap Index Scan on index_merge_requests_on_description_trigram (cost=0.00..561.50 rows=267 width=0) (actual time=16.247..16.247 rows=298 loop s=1) Index Cond: (description ~~* '%test1%'::text) -> Index Scan using projects_pkey on projects target_projects (cost=0.43..3.42 rows=1 width=8) (actual time=0.021..0.022 rows=1 loops=709) Index Cond: (id = merge_requests.target_project_id) -> Hash (cost=4.90..4.90 rows=84 width=8) (actual time=0.354..0.354 rows=272 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 19kB -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations target_authorizations (cost=0.43..4.90 rows =84 width=8) (actual time=0.093..0.260 rows=272 loops=1) Index Cond: (user_id = 1642716) Heap Fetches: 1 -> Index Scan using index_project_features_on_project_id on project_features target_features (cost=0.43..0.47 rows=1 width=8) (actual time=0.034..0.034 rows=1 loops=62) Index Cond: (project_id = target_projects.id) Planning time: 5.736 ms Execution time: 86.165 msBoth return 54 as the result!
Please register or sign in to comment