-
In production:
QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=168283.08..168283.09 rows=1 width=8) (actual time=1276.449..1276.450 rows=1 loops=1) -> Nested Loop Semi Join (cost=953.02..168283.06 rows=7 width=0) (actual time=49.904..1276.103 rows=518 loops=1) -> Nested Loop (cost=777.00..40388.07 rows=712 width=16) (actual time=49.840..1259.362 rows=518 loops=1) -> Nested Loop Semi Join (cost=776.57..37224.40 rows=799 width=12) (actual time=49.794..1247.874 rows=548 loops=1) -> Nested Loop (cost=776.14..31440.16 rows=1435 width=8) (actual time=49.779..1243.259 rows=548 loops=1) -> Bitmap Heap Scan on issues (cost=775.71..13843.70 rows=2577 width=4) (actual time=49.313..1194.204 rows=2947 loops=1) Recheck Cond: (((title)::text ~~* '%test1%'::text) OR (description ~~* '%test1%'::text)) Rows Removed by Index Recheck: 434 Filter: ((deleted_at IS NULL) AND ((confidential IS NOT TRUE) OR (confidential AND ((author_id = 1642716) OR (alternatives: SubPlan 1 or hashed SubPlan 2) OR (hashed SubPlan 3))))) Rows Removed by Filter: 228 Heap Blocks: exact=3462 -> BitmapOr (cost=600.12..600.12 rows=2644 width=0) (actual time=48.799..48.799 rows=0 loops=1) -> Bitmap Index Scan on index_issues_on_title_trigram (cost=0.00..326.90 rows=1987 width=0) (actual time=15.313..15.313 rows=718 loops=1) Index Cond: ((title)::text ~~* '%test1%'::text) -> Bitmap Index Scan on index_issues_on_description_trigram (cost=0.00..271.93 rows=657 width=0) (actual time=33.484..33.484 rows=2994 loops=1) Index Cond: (description ~~* '%test1%'::text) SubPlan 1 -> Index Only Scan using index_issue_assignees_on_issue_id_and_user_id on issue_assignees (cost=0.43..3.45 rows=1 width=0) (never executed) Index Cond: ((issue_id = issues.id) AND (user_id = 1642716)) Heap Fetches: 0 SubPlan 2 -> Index Scan using index_issue_assignees_on_user_id on issue_assignees issue_assignees_1 (cost=0.43..105.29 rows=76 width=4) (actual time=0.016..0.035 rows=8 loops=1) Index Cond: (user_id = 1642716) SubPlan 3 -> Nested Loop (cost=0.86..175.38 rows=84 width=4) (actual time=0.025..1.969 rows=271 loops=1) -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations (cost=0.43..6.45 rows=84 width=4) (actual time=0.014..0.154 rows=271 loops=1) Index Cond: ((user_id = 1642716) AND (access_level >= 20)) Heap Fetches: 3 -> Index Only Scan using projects_pkey on projects projects_2 (cost=0.43..2.00 rows=1 width=4) (actual time=0.005..0.006 rows=1 loops=271) Index Cond: (id = project_authorizations.project_id) Heap Fetches: 21 -> Index Scan using projects_pkey on projects (cost=0.43..6.82 rows=1 width=4) (actual time=0.015..0.015 rows=0 loops=2947) Index Cond: (id = issues.project_id) Filter: ((alternatives: SubPlan 4 or hashed SubPlan 5) OR (visibility_level = ANY ('{10,20}'::integer[]))) Rows Removed by Filter: 1 SubPlan 4 -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_1 (cost=0.43..3.45 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=2947) Index Cond: ((user_id = 1642716) AND (project_id = projects.id)) Heap Fetches: 0 SubPlan 5 -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_2 (cost=0.43..6.25 rows=85 width=4) (never executed) Index Cond: (user_id = 1642716) Heap Fetches: 0 -> Index Scan using projects_pkey on projects projects_1 (cost=0.43..4.03 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=548) Index Cond: (id = projects.id) Filter: ((alternatives: SubPlan 11 or hashed SubPlan 12) OR (visibility_level = ANY ('{10,20}'::integer[]))) SubPlan 11 -> 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) (actual time=0.003..0.003 rows=0 loops=548) Index Cond: ((user_id = 1642716) AND (project_id = projects_1.id)) Heap Fetches: 0 SubPlan 12 -> 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) (never executed) Index Cond: (user_id = 1642716) Heap Fetches: 0 -> Index Scan using index_project_features_on_project_id on project_features (cost=0.43..3.95 rows=1 width=8) (actual time=0.019..0.019 rows=1 loops=548) Index Cond: (project_id = projects.id) Filter: ((issues_access_level = ANY ('{NULL,20}'::integer[])) OR ((issues_access_level = 10) AND (alternatives: SubPlan 6 or hashed SubPlan 7))) Rows Removed by Filter: 0 SubPlan 6 -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_3 (cost=0.43..3.45 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=18) Index Cond: ((user_id = 1642716) AND (project_id = projects.id)) Heap Fetches: 0 SubPlan 7 -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_4 (cost=0.43..6.25 rows=85 width=4) (never executed) Index Cond: (user_id = 1642716) Heap Fetches: 0 -> Index Scan using index_issues_on_project_id_and_iid on issues issues_1 (cost=176.03..992.83 rows=229 width=4) (actual time=0.030..0.030 rows=1 loops=518) Index Cond: (project_id = projects.id) Filter: ((deleted_at IS NULL) AND ((confidential IS NOT TRUE) OR (confidential AND ((author_id = 1642716) OR (alternatives: SubPlan 8 or hashed SubPlan 9) OR (hashed SubPlan 10))))) Rows Removed by Filter: 1 SubPlan 8 -> Index Only Scan using index_issue_assignees_on_issue_id_and_user_id on issue_assignees issue_assignees_2 (cost=0.43..3.45 rows=1 width=0) (never executed) Index Cond: ((issue_id = issues_1.id) AND (user_id = 1642716)) Heap Fetches: 0 SubPlan 9 -> Index Scan using index_issue_assignees_on_user_id on issue_assignees issue_assignees_3 (cost=0.43..105.29 rows=76 width=4) (actual time=0.042..0.148 rows=8 loops=1) Index Cond: (user_id = 1642716) SubPlan 10 -> Nested Loop (cost=0.86..175.38 rows=84 width=4) (actual time=0.069..3.951 rows=271 loops=1) -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_5 (cost=0.43..6.45 rows=84 width=4) (actual time=0.037..0.223 rows=271 loops=1) Index Cond: ((user_id = 1642716) AND (access_level >= 20)) Heap Fetches: 3 -> Index Only Scan using projects_pkey on projects projects_3 (cost=0.43..2.00 rows=1 width=4) (actual time=0.012..0.013 rows=1 loops=271) Index Cond: (id = project_authorizations_5.project_id) Heap Fetches: 21 Planning time: 5.352 ms Execution time: 1277.268 ms (87 rows)Edited by Gregory Stark -
💬 @jprovaznikPretty formatted query:
SELECT COUNT(*) FROM "issues" INNER JOIN "projects" ON "projects"."id" = "issues"."project_id" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE "issues"."deleted_at" IS NULL AND (issues.confidential IS NOT TRUE OR (issues.confidential = TRUE AND (issues.author_id = 1642716 OR EXISTS (SELECT TRUE FROM issue_assignees WHERE user_id = 1642716 AND issue_id = issues.id) OR issues.project_id IN (SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "project_authorizations"."user_id" = 1642716 AND (project_authorizations.access_level >= 20))))) 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 "issues"."project_id" FROM "issues" WHERE "issues"."deleted_at" IS NULL AND (issues.confidential IS NOT TRUE OR (issues.confidential = TRUE AND (issues.author_id = 1642716 OR EXISTS (SELECT TRUE FROM issue_assignees WHERE user_id = 1642716 AND issue_id = issues.id) OR issues.project_id IN (SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "project_authorizations"."user_id" = 1642716 AND (project_authorizations.access_level >= 20)))))) AND ("project_features"."issues_access_level" IN (NULL, 20) OR ("project_features"."issues_access_level" = 10 AND EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 1642716 AND (project_authorizations.project_id = projects.id)))) AND "issues"."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 ("issues"."title" ILIKE '%test1%' OR "issues"."description" ILIKE '%test1%');- the "AND "projects"."id" IN" block can be removed completely - it check if the project id is in projects which have an issue we have access too - this is checked in previous "AND ..." filter
- the whole query could be flatten by left joining target project_authorization
- the "AND "issues"."project_id" IN" is redundant too
An optimized query might look like:
SELECT COUNT(*) FROM "issues" INNER JOIN "projects" ON "projects"."id" = "issues"."project_id" LEFT JOIN project_features ON projects.id = project_features.project_id LEFT OUTER JOIN project_authorizations ON (issues.project_id = project_authorizations.project_id AND project_authorizations.user_id = 1642716) WHERE "issues"."deleted_at" IS NULL AND (issues.confidential IS NOT TRUE OR (issues.confidential = TRUE AND (issues.author_id = 1642716 OR EXISTS (SELECT TRUE FROM issue_assignees WHERE user_id = 1642716 AND issue_id = issues.id) OR project_authorizations.access_level >= 20))) AND (project_authorizations.access_level is not NULL OR projects.visibility_level IN (10, 20)) AND ("project_features"."issues_access_level" IN (NULL, 20) OR ("project_features"."issues_access_level" = 10 AND project_authorizations.access_level is not NULL)) AND ("issues"."title" ILIKE '%test1%' OR "issues"."description" ILIKE '%test1%');Edited by Jan Provaznik -
🔴 @smcgivern@jprovaznik query plan for that most recent one from production:
QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------- Aggregate (cost=6920.03..6920.04 rows=1 width=8) (actual time=2572.875..2572.876 rows=1 loops=1) -> Nested Loop (cost=2080.65..6917.30 rows=1093 width=0) (actual time=2541.523..2572.720 rows=518 loops=1) Join Filter: (((project_authorizations.access_level IS NOT NULL) OR (projects.visibility_level = ANY ('{10,20}'::integer[]))) AND (issues.project_id = projects.id)) Rows Removed by Join Filter: 1921 -> Nested Loop (cost=2080.22..6303.02 rows=1155 width=12) (actual time=2541.387..2559.531 rows=2439 loops=1) Join Filter: ((project_features.issues_access_level = ANY ('{NULL,20}'::integer[])) OR ((project_features.issues_access_level = 10) AND (project_authorizations.access _level IS NOT NULL))) Rows Removed by Join Filter: 510 -> Merge Left Join (cost=2079.79..2089.97 rows=1242 width=8) (actual time=2541.356..2544.467 rows=2949 loops=1) Merge Cond: (issues.project_id = project_authorizations.project_id) Filter: ((issues.confidential IS NOT TRUE) OR (issues.confidential AND ((issues.author_id = 1642716) OR (alternatives: SubPlan 1 or hashed SubPlan 2) OR (projec t_authorizations.access_level >= 20)))) Rows Removed by Filter: 76 -> Sort (cost=2072.20..2075.35 rows=1258 width=13) (actual time=2541.105..2542.119 rows=3025 loops=1) Sort Key: issues.project_id Sort Method: quicksort Memory: 238kB -> Bitmap Heap Scan on issues (cost=95.71..2007.44 rows=1258 width=13) (actual time=24.506..2539.429 rows=3025 loops=1) Recheck Cond: (((title)::text ~~* '%test1%'::text) OR (description ~~* '%test1%'::text)) Rows Removed by Index Recheck: 435 Filter: (deleted_at IS NULL) Rows Removed by Filter: 151 Heap Blocks: exact=3465 -> BitmapOr (cost=95.71..95.71 rows=1277 width=0) (actual time=23.941..23.941 rows=0 loops=1) -> Bitmap Index Scan on index_issues_on_title_trigram (cost=0.00..30.88 rows=718 width=0) (actual time=6.411..6.411 rows=720 loops=1) Index Cond: ((title)::text ~~* '%test1%'::text) -> Bitmap Index Scan on index_issues_on_description_trigram (cost=0.00..64.19 rows=559 width=0) (actual time=17.528..17.528 rows=2994 loops= 1) Index Cond: (description ~~* '%test1%'::text) -> Sort (cost=7.59..7.80 rows=84 width=8) (actual time=0.238..0.339 rows=338 loops=1) Sort Key: project_authorizations.project_id Sort Method: quicksort Memory: 37kB -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations (cost=0.43..4.90 rows=84 width=8) (ac tual time=0.027..0.132 rows=272 loops=1) Index Cond: (user_id = 1642716) Heap Fetches: 1 SubPlan 1 -> Index Only Scan using index_issue_assignees_on_issue_id_and_user_id on issue_assignees (cost=0.43..3.45 rows=1 width=0) (never executed) Index Cond: ((issue_id = issues.id) AND (user_id = 1642716)) Heap Fetches: 0 SubPlan 2 -> Index Scan using index_issue_assignees_on_user_id on issue_assignees issue_assignees_1 (cost=0.43..104.26 rows=75 width=4) (actual time=0.013..0.031 rows =8 loops=1) Index Cond: (user_id = 1642716) -> Index Scan using index_project_features_on_project_id on project_features (cost=0.43..3.38 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=2949) Index Cond: (project_id = issues.project_id) -> Index Scan using projects_pkey on projects (cost=0.43..0.52 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=2439) Index Cond: (id = project_features.project_id) Planning time: 2.998 ms Execution time: 2573.027 msBoth return 518.
Please register or sign in to comment