explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8c6O

Settings
# exclusive inclusive rows x rows loops node
1. 0.017 212,878.254 ↓ 100.0 100 1

Limit (cost=1,491,554.85..1,491,554.94 rows=1 width=1,244) (actual time=212,870.997..212,878.254 rows=100 loops=1)

  • Buffers: shared hit=698,180 read=183,279 dirtied=750
  • I/O Timings: read=208,438.911
2. 7.117 212,878.237 ↓ 100.0 100 1

Unique (cost=1,491,554.85..1,491,554.94 rows=1 width=1,244) (actual time=212,870.996..212,878.237 rows=100 loops=1)

  • Buffers: shared hit=698,180 read=183,279 dirtied=750
  • I/O Timings: read=208,438.911
3. 301.995 212,871.120 ↓ 634.0 634 1

Sort (cost=1,491,554.85..1,491,554.86 rows=1 width=1,244) (actual time=212,870.992..212,871.120 rows=634 loops=1)

  • Sort Key: epics.id DESC, epics.author_id, epics.assignee_id, epics.iid, epics.cached_markdown_version, epics.updated_by_id, epics.last_edited_by_id, epics.lock_version, epics.start_date, epics.end_date, epics.last_edited_at, epics.created_at, epics.updated_at, epics.title, epics.title_html, epics.description, epics.description_html, epics.start_date_sourcing_milestone_id, epics.due_date_sourcing_milestone_id, epics.start_date_fixed, epics.due_date_fixed, epics.start_date_is_fixed, epics.due_date_is_fixed, epics.closed_by_id, epics.closed_at, epics.parent_id, epics.relative_position, epics.state_id, epics.start_date_sourcing_epic_id, epics.due_date_sourcing_epic_id, epics.external_key, epics.confidential
  • Sort Method: quicksort Memory: 11,591kB
  • Buffers: shared hit=696,476 read=183,279 dirtied=750
  • I/O Timings: read=208,438.911
4. 15.198 212,569.125 ↓ 9,472.0 9,472 1

Nested Loop (cost=1,486,500.17..1,491,554.84 rows=1 width=1,244) (actual time=212,092.909..212,569.125 rows=9,472 loops=1)

  • Buffers: shared hit=658,116 read=183,279 dirtied=750
  • I/O Timings: read=208,438.911
5. 45.673 212,156.596 ↓ 969.1 9,691 1

Hash Join (cost=1,486,489.81..1,491,450.65 rows=10 width=4) (actual time=212,090.920..212,156.596 rows=9,691 loops=1)

  • Hash Cond: (epic_issues.issue_id = issues.id)
  • Buffers: shared hit=629,366 read=182,940 dirtied=750
  • I/O Timings: read=208,084.417
6. 20.037 20.037 ↓ 1.0 142,837 1

Seq Scan on public.epic_issues (cost=0.00..4,586.40 rows=142,640 width=8) (actual time=0.013..20.037 rows=142,837 loops=1)

  • Buffers: shared hit=790
7. 1.903 212,090.886 ↓ 969.1 9,691 1

Hash (cost=1,486,489.68..1,486,489.68 rows=10 width=4) (actual time=212,090.886..212,090.886 rows=9,691 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 469kB
  • Buffers: shared hit=628,576 read=182,940 dirtied=750
  • I/O Timings: read=208,084.417
8. 2.426 212,088.983 ↓ 969.1 9,691 1

Unique (cost=1,486,489.53..1,486,489.58 rows=10 width=4) (actual time=212,085.641..212,088.983 rows=9,691 loops=1)

  • Buffers: shared hit=628,576 read=182,940 dirtied=750
  • I/O Timings: read=208,084.417
9. 21.680 212,086.557 ↓ 969.1 9,691 1

Sort (cost=1,486,489.53..1,486,489.56 rows=10 width=4) (actual time=212,085.638..212,086.557 rows=9,691 loops=1)

  • Sort Key: issues.id
  • Sort Method: quicksort Memory: 839kB
  • Buffers: shared hit=628,576 read=182,940 dirtied=750
  • I/O Timings: read=208,084.417
10. 46.032 212,064.877 ↓ 969.1 9,691 1

Nested Loop Left Join (cost=16,423.60..1,486,489.36 rows=10 width=4) (actual time=58.651..212,064.877 rows=9,691 loops=1)

  • Filter: ((project_features.issues_access_level IS NULL) OR (project_features.issues_access_level = ANY ('{20,30}'::integer[])) OR ((project_features.issues_access_level = 10) AND (alternatives: SubPlan 8 or hashed SubPlan 9)))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=628,576 read=182,940 dirtied=750
  • I/O Timings: read=208,084.417
11. 94.323 211,912.104 ↓ 969.1 9,691 1

Hash Join (cost=16,423.16..1,486,448.36 rows=10 width=8) (actual time=58.631..211,912.104 rows=9,691 loops=1)

  • Hash Cond: (issues.project_id = projects.id)
  • Buffers: shared hit=589,755 read=182,940 dirtied=750
  • I/O Timings: read=208,084.417
12. 567.158 211,808.390 ↑ 1.7 54,700 1

Nested Loop (cost=0.98..1,469,676.40 rows=93,250 width=8) (actual time=49.209..211,808.390 rows=54,700 loops=1)

  • Buffers: shared hit=582,860 read=182,940 dirtied=750
  • I/O Timings: read=208,084.417
13. 842.331 842.331 ↓ 1.0 142,837 1

Index Only Scan using index_epic_issues_on_issue_id on public.epic_issues epic_issues_1 (cost=0.42..3,445.00 rows=142,640 width=4) (actual time=0.015..842.331 rows=142,837 loops=1)

  • Heap Fetches: 39,526
  • Buffers: shared hit=31,728 read=538 dirtied=151
  • I/O Timings: read=416.846
14. 208,175.145 210,398.901 ↓ 0.0 0 142,837

Index Scan using issues_pkey on public.issues (cost=0.56..10.28 rows=1 width=8) (actual time=1.473..1.473 rows=0 loops=142,837)

  • Index Cond: (issues.id = epic_issues_1.issue_id)
  • Filter: ((issues.state_id = 1) AND ((issues.confidential IS NOT TRUE) OR (issues.confidential AND ((issues.author_id = 1,642,716) OR (alternatives: SubPlan 2 or hashed SubPlan 3) OR (alternatives: SubPlan 4 or hashed SubPlan 5)))))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=551,132 read=182,402 dirtied=599
  • I/O Timings: read=207,667.571
15.          

SubPlan (for Index Scan)

16. 2,188.854 2,188.854 ↓ 0.0 0 2,493

Index Only Scan using index_issue_assignees_on_issue_id_and_user_id on public.issue_assignees (cost=0.43..3.45 rows=1 width=0) (actual time=0.878..0.878 rows=0 loops=2,493)

  • Index Cond: ((issue_assignees.issue_id = issues.id) AND (issue_assignees.user_id = 1,642,716))
  • Heap Fetches: 0
  • Buffers: shared hit=5,718 read=1,768
  • I/O Timings: read=2,148.849
17. 0.000 0.000 ↓ 0.0 0 0

Index Scan using index_issue_assignees_on_user_id on public.issue_assignees issue_assignees_1 (cost=0.43..85.21 rows=85 width=4) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: (issue_assignees_1.user_id = 1,642,716)
18. 34.902 34.902 ↑ 1.0 1 2,493

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on public.project_authorizations (cost=0.57..3.59 rows=1 width=0) (actual time=0.014..0.014 rows=1 loops=2,493)

  • Index Cond: ((project_authorizations.user_id = 1,642,716) AND (project_authorizations.project_id = issues.project_id) AND (project_authorizations.access_level >= 20))
  • Heap Fetches: 288
  • Buffers: shared hit=11,108
19. 0.000 0.000 ↓ 0.0 0 0

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on public.project_authorizations project_authorizations_1 (cost=0.57..360.15 rows=1,298 width=4) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: ((project_authorizations_1.user_id = 1,642,716) AND (project_authorizations_1.access_level >= 20))
  • Heap Fetches: 0
20. 0.255 9.391 ↑ 1.6 978 1

Hash (cost=16,402.36..16,402.36 rows=1,585 width=4) (actual time=9.391..9.391 rows=978 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 51kB
  • Buffers: shared hit=6,895
21. 0.343 9.136 ↑ 1.6 978 1

Nested Loop (cost=1,433.13..16,402.36 rows=1,585 width=4) (actual time=2.368..9.136 rows=978 loops=1)

  • Buffers: shared hit=6,895
22. 0.149 2.367 ↑ 1.1 153 1

HashAggregate (cost=1,432.70..1,434.31 rows=161 width=4) (actual time=2.330..2.367 rows=153 loops=1)

  • Group Key: namespaces.id
  • Buffers: shared hit=767
23. 2.218 2.218 ↑ 1.1 153 1

CTE Scan on base_and_descendants namespaces (cost=1,427.46..1,430.68 rows=161 width=4) (actual time=0.095..2.218 rows=153 loops=1)

  • Buffers: shared hit=767
24.          

CTE base_and_descendants

25. 0.429 1.959 ↑ 1.1 153 1

Recursive Union (cost=0.43..1,427.46 rows=161 width=345) (actual time=0.090..1.959 rows=153 loops=1)

  • Buffers: shared hit=767
26. 0.075 0.075 ↑ 1.0 1 1

Index Scan using namespaces_pkey on public.namespaces namespaces_1 (cost=0.43..3.45 rows=1 width=345) (actual time=0.074..0.075 rows=1 loops=1)

  • Index Cond: (namespaces_1.id = 9,970)
  • Filter: ((namespaces_1.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=4
27. 0.206 1.455 ↓ 1.9 30 5

Nested Loop (cost=0.56..142.08 rows=16 width=345) (actual time=0.024..0.291 rows=30 loops=5)

  • Buffers: shared hit=763
28. 0.025 0.025 ↓ 3.1 31 5

WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.001..0.005 rows=31 loops=5)

29. 1.224 1.224 ↑ 2.0 1 153

Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_2 (cost=0.56..14.17 rows=2 width=345) (actual time=0.005..0.008 rows=1 loops=153)

  • Index Cond: (namespaces_2.parent_id = base_and_descendants.id)
  • Filter: ((namespaces_2.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=763
30. 3.492 6.426 ↑ 1.7 6 153

Index Scan using index_projects_on_namespace_id_and_id on public.projects (cost=0.43..92.87 rows=10 width=8) (actual time=0.009..0.042 rows=6 loops=153)

  • Index Cond: (projects.namespace_id = namespaces.id)
  • Filter: ((NOT projects.archived) AND ((alternatives: SubPlan 6 or hashed SubPlan 7) OR (projects.visibility_level = ANY ('{10,20}'::integer[]))))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=6,128
31.          

SubPlan (for Index Scan)

32. 2.934 2.934 ↑ 1.0 1 978

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on public.project_authorizations project_authorizations_2 (cost=0.57..3.59 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=978)

  • Index Cond: ((project_authorizations_2.user_id = 1,642,716) AND (project_authorizations_2.project_id = projects.id) AND (project_authorizations_2.access_level >= 10))
  • Heap Fetches: 239
  • Buffers: shared hit=4,644
33. 0.000 0.000 ↓ 0.0 0 0

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on public.project_authorizations project_authorizations_3 (cost=0.57..425.56 rows=1,562 width=4) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: ((project_authorizations_3.user_id = 1,642,716) AND (project_authorizations_3.access_level >= 10))
  • Heap Fetches: 0
34. 106.601 106.601 ↑ 1.0 1 9,691

Index Scan using index_project_features_on_project_id on public.project_features (cost=0.43..0.49 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=9,691)

  • Index Cond: (projects.id = project_features.project_id)
  • Buffers: shared hit=38,764
35.          

SubPlan (for Nested Loop Left Join)

36. 0.140 0.140 ↑ 1.0 1 14

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on public.project_authorizations project_authorizations_4 (cost=0.57..3.59 rows=1 width=0) (actual time=0.010..0.010 rows=1 loops=14)

  • Index Cond: ((project_authorizations_4.user_id = 1,642,716) AND (project_authorizations_4.project_id = projects.id) AND (project_authorizations_4.access_level >= 10))
  • Heap Fetches: 0
  • Buffers: shared hit=57
37. 0.000 0.000 ↓ 0.0 0 0

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on public.project_authorizations project_authorizations_5 (cost=0.57..425.56 rows=1,562 width=4) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: ((project_authorizations_5.user_id = 1,642,716) AND (project_authorizations_5.access_level >= 10))
  • Heap Fetches: 0
38. 396.921 397.331 ↑ 1.0 1 9,691

Index Scan using epics_pkey on public.epics (cost=10.36..10.42 rows=1 width=1,244) (actual time=0.041..0.041 rows=1 loops=9,691)

  • Index Cond: (epics.id = epic_issues.epic_id)
  • Filter: (((NOT epics.confidential) OR (epics.confidential AND (hashed SubPlan 1))) AND (epics.group_id = 9,970))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=28,750 read=339
  • I/O Timings: read=354.494
39.          

SubPlan (for Index Scan)

40. 0.018 0.410 ↑ 1.0 1 1

Bitmap Heap Scan on public.members (cost=8.55..10.07 rows=1 width=4) (actual time=0.410..0.410 rows=1 loops=1)

  • Filter: ((members.access_level > 10) AND ((members.type)::text = 'GroupMember'::text))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=15
41. 0.007 0.392 ↓ 0.0 0 1

BitmapAnd (cost=8.55..8.55 rows=1 width=0) (actual time=0.392..0.392 rows=0 loops=1)

  • Buffers: shared hit=14
42. 0.022 0.022 ↓ 3.4 62 1

Bitmap Index Scan using index_members_on_user_id (cost=0.00..2.07 rows=18 width=0) (actual time=0.022..0.022 rows=62 loops=1)

  • Index Cond: (members.user_id = 1,642,716)
  • Buffers: shared hit=3
43. 0.363 0.363 ↓ 4.9 1,306 1

Bitmap Index Scan using index_members_on_source_id_and_source_type (cost=0.00..6.22 rows=266 width=0) (actual time=0.363..0.363 rows=1,306 loops=1)

  • Index Cond: ((members.source_id = 9,970) AND ((members.source_type)::text = 'Namespace'::text))
  • Buffers: shared hit=11