Query doing largeish data processing
I was looking for queries using the "Top Queries" dashboard using temporary files (ie, sorts or hash joins spilled to disk) and found this query running in production:
SELECT issues.*, (SELECT MIN("label_priorities"."priority") FROM "labels" INNER JOIN "label_links" ON "label_links"."label_id" = "labels"."id" LEFT OUTER JOIN "label_priorities" ON "labels"."id" = "label_priorities"."label_id" WHERE (label_priorities.project_id = issues.project_id) AND (label_links.target_id = issues.id) AND "label_links"."target_type" = 'Issue') AS highest_priority FROM "issues" INNER JOIN "label_links" ON "label_links"."target_id" = "issues"."id" AND "label_links"."target_type" = 'Issue' INNER JOIN "labels" ON "labels"."id" = "label_links"."label_id" INNER JOIN "projects" ON "projects"."id" = "issues"."project_id" LEFT OUTER JOIN milestones ON issues.milestone_id = milestones.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 = 1296187
OR EXISTS (SELECT TRUE FROM issue_assignees WHERE user_id = 1296187 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" = 1296187 AND (project_authorizations.access_level >= 20))))) AND ("issues"."state" IN ('closed')) AND "milestones"."title" = 'Next 6-12 months' AND "labels"."title" = 'UI polish' AND "labels"."id" IN (SELECT "labels"."id" FROM "labels" WHERE (labels.id IN (SELECT "labels"."id" FROM "labels" WHERE "labels"."group_id" IN (SELECT "projects"."namespace_id" FROM "projects" INNER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id" WHERE "projects"."id" IN (SELECT "projects"."id" FROM "projects" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE (projects.id IN (SELECT "projects"."id" FROM "projects" INNER JOIN "project_group_links" ON "projects"."id" = "project_group_links"."project_id" WHERE "project_group_links"."group_id" = 9970
UNION
SELECT "projects"."id" FROM "projects" WHERE "projects"."namespace_id" = 9970)) 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" = 1296187 AND (project_authorizations.project_id = projects.id))))) AND "namespaces"."type" = 'Group')
UNION
SELECT "labels"."id" FROM "labels" WHERE "labels"."project_id" IN (SELECT "projects"."id" FROM "projects" WHERE "projects"."id" IN (SELECT "projects"."id" FROM "projects" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE (projects.id IN (SELECT "projects"."id" FROM "projects" INNER JOIN "project_group_links" ON "projects"."id" = "project_group_links"."project_id" WHERE "project_group_links"."group_id" = 9970
UNION
SELECT "projects"."id" FROM "projects" WHERE "projects"."namespace_id" = 9970)) 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" = 1296187 AND (project_authorizations.project_id = projects.id)))))))) ORDER BY "labels"."title" ASC) AND (projects.id IN (SELECT "projects"."id" FROM "projects" INNER JOIN "project_group_links" ON "projects"."id" = "project_group_links"."project_id" WHERE "project_group_links"."group_id" = 9970
UNION
SELECT "projects"."id" FROM "projects" WHERE "projects"."namespace_id" = 9970)) 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" = 1296187 AND (project_authorizations.project_id = projects.id)))) GROUP BY "issues"."id" ORDER BY relative_position ASC NULLS LAST, highest_priority ASC NULLS LAST, id DESC LIMIT 20 OFFSET 0
Here's the plan -- note the Hash Join with 8 batches which means it has spilled to disk (View this on [explain.depesz] (https://explain.depesz.com/s/y67d)):
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=196821.87..196821.88 rows=1 width=763) (actual time=16.613..16.614 rows=1 loops=1)
Buffers: shared hit=9532, temp read=12 written=10
-> Sort (cost=196821.87..196821.88 rows=1 width=763) (actual time=16.612..16.613 rows=1 loops=1)
Sort Key: issues.relative_position, ((SubPlan 1)), issues.id DESC
Sort Method: quicksort Memory: 27kB
Buffers: shared hit=9532, temp read=12 written=10
-> Group (cost=196804.47..196821.86 rows=1 width=763) (actual time=16.556..16.605 rows=1 loops=1)
Group Key: issues.id
Buffers: shared hit=9532, temp read=12 written=10
-> Nested Loop (cost=196804.47..196808.54 rows=1 width=759) (actual time=16.471..16.520 rows=1 loops=1)
Join Filter: ((project_features.issues_access_level = ANY ('{NULL,20}'::integer[])) OR ((project_features.issues_access_level = 10) AND (alternatives: SubPlan 5 or hashed SubPlan 6)))
Buffers: shared hit=9518, temp read=12 written=10
-> Merge Join (cost=196804.04..196804.61 rows=1 width=771) (actual time=16.464..16.512 rows=1 loops=1)
Merge Cond: (label_links.target_id = issues.id)
Buffers: shared hit=9514, temp read=12 written=10
-> Sort (cost=195041.08..195041.36 rows=110 width=4) (actual time=15.159..15.197 rows=483 loops=1)
Sort Key: label_links.target_id DESC
Sort Method: quicksort Memory: 47kB
Buffers: shared hit=8561, temp read=12 written=10
-> Nested Loop (cost=189449.43..195037.35 rows=110 width=4) (actual time=12.566..14.996 rows=483 loops=1)
Join Filter: (labels.id = label_links.label_id)
Buffers: shared hit=8561, temp read=12 written=10
-> Hash Semi Join (cost=189449.00..194795.13 rows=51 width=8) (actual time=12.543..13.516 rows=1 loops=1)
Hash Cond: (labels.id = "ANY_subquery".id)
Buffers: shared hit=7998, temp read=12 written=10
-> Index Scan using index_labels_on_title on labels (cost=0.43..155.73 rows=102 width=4) (actual time=0.025..0.038 rows=6 loops=1)
Index Cond: ((title)::text = 'UI polish'::text)
Buffers: shared hit=9
-> Hash (cost=167658.99..167658.99 rows=1328126 width=4) (actual time=10.988..10.988 rows=659 loops=1)
Buckets: 524288 Batches: 8 Memory Usage: 4100kB
Buffers: shared hit=7989
-> Subquery Scan on "ANY_subquery" (cost=151057.42..167658.99 rows=1328126 width=4) (actual time=10.639..10.775 rows=659 loops=1)
Buffers: shared hit=7989
-> Sort (cost=151057.42..154377.73 rows=1328126 width=13) (actual time=10.639..10.683 rows=659 loops=1)
Sort Key: labels_1.title
Sort Method: quicksort Memory: 62kB
Buffers: shared hit=7989
-> Nested Loop (cost=418.20..1389.37 rows=1328126 width=13) (actual time=6.977..9.877 rows=659 loops=1)
Buffers: shared hit=7989
-> HashAggregate (cost=417.77..420.57 rows=280 width=4) (actual time=6.969..7.073 rows=659 loops=1)
Group Key: labels_2.id
Buffers: shared hit=5332
-> Append (cost=202.73..417.07 rows=280 width=4) (actual time=3.220..6.786 rows=663 loops=1)
Buffers: shared hit=5332
-> Nested Loop (cost=202.73..207.60 rows=68 width=4) (actual time=3.220..3.585 rows=174 loops=1)
Buffers: shared hit=2618
-> HashAggregate (cost=202.30..202.33 rows=3 width=8) (actual time=3.205..3.206 rows=5 loops=1)
Group Key: projects_1.namespace_id
Buffers: shared hit=2417
-> Nested Loop (cost=182.48..202.29 rows=3 width=8) (actual time=2.377..3.180 rows=119 loops=1)
Buffers: shared hit=2417
-> Nested Loop (cost=182.05..192.25 rows=20 width=4) (actual time=2.368..2.853 rows=131 loops=1)
Buffers: shared hit=1893
-> HashAggregate (cost=181.62..181.82 rows=20 width=12) (actual time=2.363..2.379 rows=131 loops=1)
Group Key: projects_2.id
Buffers: shared hit=1366
-> Nested Loop (cost=44.82..181.57 rows=20 width=12) (actual time=0.529..2.302 rows=131 loops=1)
Buffers: shared hit=1366
-> Nested Loop (cost=44.39..90.94 rows=23 width=8) (actual time=0.517..1.291 rows=144 loops=1)
Buffers: shared hit=785
-> HashAggregate (cost=43.96..44.19 rows=23 width=4) (actual time=0.509..0.528 rows=144 loops=1)
Group Key: projects_3.id
Buffers: shared hit=232
-> Append (cost=0.72..43.90 rows=23 width=4) (actual time=0.021..0.470 rows=145 loops=1)
Buffers: shared hit=232
-> Nested Loop (cost=0.72..11.92 rows=3 width=4) (actual time=0.021..0.183 rows=21 loops=1)
Buffers: shared hit=102
-> Index Scan using index_project_group_links_on_group_id on project_group_links (cost=0.29..4.54 rows=3 width=4) (actual time=0.011..0.045 rows=21 loops=1)
Index Cond: (group_id = 9970)
Buffers: shared hit=20
-> Index Only Scan using projects_pkey on projects projects_3 (cost=0.43..2.45 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=21)
Index Cond: (id = project_group_links.project_id)
Heap Fetches: 2
Buffers: shared hit=82
-> Index Scan using index_projects_on_namespace_id on projects projects_4 (cost=0.43..31.76 rows=20 width=4) (actual time=0.011..0.274 rows=124 loops=1)
Index Cond: (namespace_id = 9970)
Buffers: shared hit=130
-> Index Only Scan using projects_pkey on projects projects_2 (cost=0.43..2.01 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=144)
Index Cond: (id = projects_3.id)
Heap Fetches: 10
Buffers: shared hit=553
-> Index Scan using index_project_features_on_project_id on project_features project_features_1 (cost=0.43..3.93 rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=144)
Index Cond: (project_id = projects_2.id)
Filter: ((issues_access_level = ANY ('{NULL,20}'::integer[])) OR ((issues_access_level = 10) AND (alternatives: SubPlan 9 or hashed SubPlan 10)))
Rows Removed by Filter: 0
Buffers: shared hit=581
SubPlan 9
-> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_5 (cost=0.43..3.45 rows=1 width=0) (actual time=0.010..0.010 rows=1 loops=1)
Index Cond: ((user_id = 1296187) AND (project_id = projects_2.id))
Heap Fetches: 0
Buffers: shared hit=4
SubPlan 10
-> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_6 (cost=0.43..10.22 rows=87 width=4) (never executed)
Index Cond: (user_id = 1296187)
Heap Fetches: 0
-> Index Scan using projects_pkey on projects projects_1 (cost=0.43..0.51 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=131)
Index Cond: (id = projects_2.id)
Buffers: shared hit=527
-> Index Scan using namespaces_pkey on namespaces (cost=0.43..0.49 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=131)
Index Cond: (id = projects_1.namespace_id)
Filter: ((type)::text = 'Group'::text)
Rows Removed by Filter: 0
Buffers: shared hit=524
-> Index Scan using index_labels_on_group_id_and_project_id_and_title on labels labels_2 (cost=0.43..1.53 rows=23 width=8) (actual time=0.005..0.069 rows=35 loops=5)
Index Cond: (group_id = namespaces.id)
Buffers: shared hit=201
-> Nested Loop (cost=191.68..206.68 rows=212 width=4) (actual time=1.671..3.149 rows=489 loops=1)
Buffers: shared hit=2714
-> HashAggregate (cost=191.25..191.45 rows=20 width=16) (actual time=1.647..1.663 rows=131 loops=1)
Group Key: projects_5.id
Buffers: shared hit=1870
-> Nested Loop (cost=182.05..191.20 rows=20 width=16) (actual time=1.227..1.599 rows=131 loops=1)
Buffers: shared hit=1870
-> HashAggregate (cost=181.62..181.82 rows=20 width=12) (actual time=1.223..1.238 rows=131 loops=1)
Group Key: projects_6.id
Buffers: shared hit=1366
-> Nested Loop (cost=44.82..181.57 rows=20 width=12) (actual time=0.230..1.175 rows=131 loops=1)
Buffers: shared hit=1366
-> Nested Loop (cost=44.39..90.94 rows=23 width=8) (actual time=0.224..0.655 rows=144 loops=1)
Buffers: shared hit=785
-> HashAggregate (cost=43.96..44.19 rows=23 width=4) (actual time=0.220..0.240 rows=144 loops=1)
Group Key: projects_7.id
Buffers: shared hit=232
-> Append (cost=0.72..43.90 rows=23 width=4) (actual time=0.009..0.186 rows=145 loops=1)
Buffers: shared hit=232
-> Nested Loop (cost=0.72..11.92 rows=3 width=4) (actual time=0.009..0.079 rows=21 loops=1)
Buffers: shared hit=102
-> Index Scan using index_project_group_links_on_group_id on project_group_links project_group_links_1 (cost=0.29..4.54 rows=3 width=4) (actual time=0.005..0.017 rows=21 loops=1)
Index Cond: (group_id = 9970)
Buffers: shared hit=20
-> Index Only Scan using projects_pkey on projects projects_7 (cost=0.43..2.45 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=21)
Index Cond: (id = project_group_links_1.project_id)
Heap Fetches: 2
Buffers: shared hit=82
-> Index Scan using index_projects_on_namespace_id on projects projects_8 (cost=0.43..31.76 rows=20 width=4) (actual time=0.006..0.103 rows=124 loops=1)
Index Cond: (namespace_id = 9970)
Buffers: shared hit=130
-> Index Only Scan using projects_pkey on projects projects_6 (cost=0.43..2.01 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=144)
Index Cond: (id = projects_7.id)
Heap Fetches: 10
Buffers: shared hit=553
-> Index Scan using index_project_features_on_project_id on project_features project_features_2 (cost=0.43..3.93 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=144)
Index Cond: (project_id = projects_6.id)
Filter: ((issues_access_level = ANY ('{NULL,20}'::integer[])) OR ((issues_access_level = 10) AND (alternatives: SubPlan 7 or hashed SubPlan 8)))
Rows Removed by Filter: 0
Buffers: shared hit=581
SubPlan 7
-> 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.005..0.005 rows=1 loops=1)
Index Cond: ((user_id = 1296187) AND (project_id = projects_6.id))
Heap Fetches: 0
Buffers: shared hit=4
SubPlan 8
-> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_4 (cost=0.43..10.22 rows=87 width=4) (never executed)
Index Cond: (user_id = 1296187)
Heap Fetches: 0
-> Index Only Scan using projects_pkey on projects projects_5 (cost=0.43..0.46 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=131)
Index Cond: (id = projects_6.id)
Heap Fetches: 8
Buffers: shared hit=504
-> Index Scan using index_labels_on_project_id on labels labels_3 (cost=0.43..0.65 rows=11 width=8) (actual time=0.005..0.011 rows=4 loops=131)
Index Cond: (project_id = projects_6.id)
Buffers: shared hit=844
-> Index Scan using labels_pkey on labels labels_1 (cost=0.43..3.44 rows=1 width=13) (actual time=0.004..0.004 rows=1 loops=659)
Index Cond: (id = labels_2.id)
Buffers: shared hit=2657
-> Index Scan using index_label_links_on_label_id on label_links (cost=0.43..3.29 rows=117 width=8) (actual time=0.021..1.314 rows=483 loops=1)
Index Cond: (label_id = "ANY_subquery".id)
Filter: ((target_type)::text = 'Issue'::text)
Rows Removed by Filter: 191
Buffers: shared hit=563
-> Sort (cost=1762.96..1762.97 rows=1 width=771) (actual time=1.271..1.273 rows=10 loops=1)
Sort Key: issues.id DESC
Sort Method: quicksort Memory: 43kB
Buffers: shared hit=953
-> Nested Loop (cost=305.41..1762.95 rows=1 width=771) (actual time=0.961..1.250 rows=11 loops=1)
Buffers: shared hit=953
-> Index Scan using index_milestones_on_title on milestones (cost=0.42..9.51 rows=5 width=4) (actual time=0.021..0.022 rows=1 loops=1)
Index Cond: ((title)::text = 'Next 6-12 months'::text)
Buffers: shared hit=4
-> Hash Join (cost=304.99..594.67 rows=1 width=771) (actual time=0.938..1.225 rows=11 loops=1)
Hash Cond: (issues.project_id = project_features.project_id)
Buffers: shared hit=949
-> Index Scan using index_issues_on_milestone_id on issues (cost=180.76..470.29 rows=38 width=759) (actual time=0.023..0.306 rows=11 loops=1)
Index Cond: (milestone_id = milestones.id)
Filter: ((deleted_at IS NULL) AND ((state)::text = 'closed'::text) AND ((confidential IS NOT TRUE) OR (confidential AND ((author_id = 1296187) OR (alternatives: SubPlan 2 or hashed SubPlan 3) OR (hashed SubPlan 4)))))
Rows Removed by Filter: 124
Buffers: shared hit=140
SubPlan 2
-> 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 = 1296187))
Heap Fetches: 0
SubPlan 3
-> Index Scan using index_issue_assignees_on_user_id on issue_assignees issue_assignees_1 (cost=0.43..106.59 rows=78 width=4) (never executed)
Index Cond: (user_id = 1296187)
SubPlan 4
-> Nested Loop (cost=0.86..179.99 rows=85 width=4) (never executed)
-> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations (cost=0.43..9.10 rows=85 width=4) (never executed)
Index Cond: ((user_id = 1296187) AND (access_level >= 20))
Heap Fetches: 0
-> Index Only Scan using projects_pkey on projects projects_11 (cost=0.43..2.00 rows=1 width=4) (never executed)
Index Cond: (id = project_authorizations.project_id)
Heap Fetches: 0
-> Hash (cost=123.94..123.94 rows=23 width=12) (actual time=0.907..0.907 rows=144 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 15kB
Buffers: shared hit=809
-> Nested Loop (cost=44.39..123.94 rows=23 width=12) (actual time=0.357..0.859 rows=144 loops=1)
Buffers: shared hit=809
-> HashAggregate (cost=43.96..44.19 rows=23 width=4) (actual time=0.351..0.374 rows=144 loops=1)
Group Key: projects_9.id
Buffers: shared hit=232
-> Append (cost=0.72..43.90 rows=23 width=4) (actual time=0.012..0.309 rows=145 loops=1)
Buffers: shared hit=232
-> Nested Loop (cost=0.72..11.92 rows=3 width=4) (actual time=0.012..0.108 rows=21 loops=1)
Buffers: shared hit=102
-> Index Scan using index_project_group_links_on_group_id on project_group_links project_group_links_2 (cost=0.29..4.54 rows=3 width=4) (actual time=0.006..0.030 rows=21 loops=1)
Index Cond: (group_id = 9970)
Buffers: shared hit=20
-> Index Only Scan using projects_pkey on projects projects_9 (cost=0.43..2.45 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=21)
Index Cond: (id = project_group_links_2.project_id)
Heap Fetches: 2
Buffers: shared hit=82
-> Index Scan using index_projects_on_namespace_id on projects projects_10 (cost=0.43..31.76 rows=20 width=4) (actual time=0.048..0.181 rows=124 loops=1)
Index Cond: (namespace_id = 9970)
Buffers: shared hit=130
-> Index Scan using index_project_features_on_project_id on project_features (cost=0.43..3.45 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=144)
Index Cond: (project_id = projects_9.id)
Buffers: shared hit=577
-> Index Only Scan using projects_pkey on projects (cost=0.43..0.46 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=1)
Index Cond: (id = issues.project_id)
Heap Fetches: 0
Buffers: shared hit=4
SubPlan 5
-> 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) (never executed)
Index Cond: ((user_id = 1296187) AND (project_id = projects.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_2 (cost=0.43..10.22 rows=87 width=4) (never executed)
Index Cond: (user_id = 1296187)
Heap Fetches: 0
SubPlan 1
-> Aggregate (cost=13.31..13.32 rows=1 width=4) (actual time=0.080..0.080 rows=1 loops=1)
Buffers: shared hit=14
-> Nested Loop (cost=1.15..13.31 rows=1 width=4) (actual time=0.079..0.079 rows=0 loops=1)
Buffers: shared hit=14
-> Nested Loop (cost=0.72..10.85 rows=1 width=12) (actual time=0.078..0.078 rows=0 loops=1)
Join Filter: (label_links_1.label_id = label_priorities.label_id)
Rows Removed by Join Filter: 20
Buffers: shared hit=14
-> Index Scan using index_label_priorities_on_project_id_and_label_id on label_priorities (cost=0.29..5.51 rows=3 width=8) (actual time=0.044..0.049 rows=4 loops=1)
Index Cond: (project_id = issues.project_id)
Buffers: shared hit=6
-> Materialize (cost=0.43..5.21 rows=3 width=4) (actual time=0.004..0.005 rows=5 loops=4)
Buffers: shared hit=8
-> Index Scan using index_label_links_on_target_id_and_target_type on label_links label_links_1 (cost=0.43..5.20 rows=3 width=4) (actual time=0.013..0.017 rows=5 loops=1)
Index Cond: ((target_id = issues.id) AND ((target_type)::text = 'Issue'::text))
Buffers: shared hit=8
-> Index Only Scan using labels_pkey on labels labels_4 (cost=0.43..2.45 rows=1 width=4) (never executed)
Index Cond: (id = label_links_1.label_id)
Heap Fetches: 0
Planning time: 15.400 ms
Execution time: 17.080 ms
(261 rows)
Edited by Gregory Stark