explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mXSu

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 1,935.876 ↑ 1.0 20 1

Limit (cost=110,466.57..110,466.62 rows=20 width=1,260) (actual time=1,935.870..1,935.876 rows=20 loops=1)

  • Buffers: shared hit=349,607 read=8,068
  • I/O Timings: read=811.780
2. 51.427 1,935.871 ↑ 228.3 20 1

Sort (cost=110,466.57..110,477.98 rows=4,566 width=1,260) (actual time=1,935.867..1,935.871 rows=20 loops=1)

  • Sort Key: ((SubPlan 1)) DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 56kB
  • Buffers: shared hit=349,607 read=8,068
  • I/O Timings: read=811.780
3. 36.352 1,884.444 ↓ 10.0 45,477 1

Nested Loop (cost=1,280.91..110,345.07 rows=4,566 width=1,260) (actual time=4.277..1,884.444 rows=45,477 loops=1)

  • Buffers: shared hit=349,604 read=8,068
  • I/O Timings: read=811.780
4. 0.879 73.976 ↑ 3.0 856 1

Nested Loop Left Join (cost=1,280.34..16,600.67 rows=2,543 width=4) (actual time=4.101..73.976 rows=856 loops=1)

  • Filter: ((project_features.issues_access_level > 0) OR (project_features.issues_access_level IS NULL))
  • Rows Removed by Filter: 84
  • Buffers: shared hit=10,175 read=79
  • I/O Timings: read=27.264
5. 0.420 54.297 ↑ 2.8 940 1

Nested Loop (cost=1,279.91..15,336.69 rows=2,591 width=4) (actual time=4.033..54.297 rows=940 loops=1)

  • Buffers: shared hit=6,414 read=79
  • I/O Timings: read=27.264
6. 0.248 3.957 ↑ 1.2 130 1

HashAggregate (cost=1,279.47..1,280.98 rows=151 width=4) (actual time=3.748..3.957 rows=130 loops=1)

  • Group Key: namespaces.id
  • Buffers: shared hit=653
7. 3.709 3.709 ↑ 1.2 130 1

CTE Scan on base_and_descendants namespaces (cost=1,274.57..1,277.59 rows=151 width=4) (actual time=0.075..3.709 rows=130 loops=1)

  • Buffers: shared hit=653
8.          

CTE base_and_descendants

9. 0.287 3.517 ↑ 1.2 130 1

Recursive Union (cost=0.43..1,274.57 rows=151 width=329) (actual time=0.072..3.517 rows=130 loops=1)

  • Buffers: shared hit=653
10. 0.060 0.060 ↑ 1.0 1 1

Index Scan using namespaces_pkey on namespaces namespaces_1 (cost=0.43..3.45 rows=1 width=329) (actual time=0.059..0.060 rows=1 loops=1)

  • Index Cond: (id = 9,970)
  • Filter: ((type)::text = 'Group'::text)
  • Buffers: shared hit=4
11. 0.035 3.170 ↓ 1.7 26 5

Nested Loop (cost=0.56..126.81 rows=15 width=329) (actual time=0.066..0.634 rows=26 loops=5)

  • Buffers: shared hit=649
12. 0.015 0.015 ↓ 2.6 26 5

WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.000..0.003 rows=26 loops=5)

13. 3.120 3.120 ↑ 1.0 1 130

Index Scan using index_namespaces_on_parent_id_and_id on namespaces namespaces_2 (cost=0.56..12.65 rows=1 width=329) (actual time=0.013..0.024 rows=1 loops=130)

  • Index Cond: (parent_id = base_and_descendants.id)
  • Filter: ((type)::text = 'Group'::text)
  • Buffers: shared hit=649
14. 14.200 49.920 ↑ 2.4 7 130

Index Scan using index_projects_on_namespace_id_and_id on projects (cost=0.43..92.91 rows=17 width=8) (actual time=0.138..0.384 rows=7 loops=130)

  • Index Cond: (namespace_id = namespaces.id)
  • Filter: ((NOT archived) AND (NOT archived) AND ((alternatives: SubPlan 2 or hashed SubPlan 3) OR (visibility_level = ANY ('{0,10,20}'::integer[]))))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=5,761 read=79
  • I/O Timings: read=27.264
15.          

SubPlan (for Index Scan)

16. 35.720 35.720 ↑ 1.0 1 940

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations (cost=0.57..3.59 rows=1 width=0) (actual time=0.038..0.038 rows=1 loops=940)

  • Index Cond: ((user_id = 1) AND (project_id = projects.id))
  • Heap Fetches: 189
  • Buffers: shared hit=4,330 read=79
  • I/O Timings: read=27.264
17. 0.000 0.000 ↓ 0.0 0

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_1 (cost=0.57..207.54 rows=1,253 width=4) (never executed)

  • Index Cond: (user_id = 1)
  • Heap Fetches: 0
18. 18.800 18.800 ↑ 1.0 1 940

Index Scan using index_project_features_on_project_id on project_features (cost=0.43..0.48 rows=1 width=8) (actual time=0.020..0.020 rows=1 loops=940)

  • Index Cond: (projects.id = project_id)
  • Buffers: shared hit=3,761
19. 500.760 500.760 ↑ 3.8 53 856

Index Scan using idx_issues_on_project_id_and_rel_position_and_state_id_and_id on issues (cost=0.56..6.48 rows=200 width=1,252) (actual time=0.027..0.585 rows=53 loops=856)

  • Index Cond: ((project_id = projects.id) AND (state_id = 1))
  • Buffers: shared hit=50,549 read=135
  • I/O Timings: read=10.516
20.          

SubPlan (for Nested Loop)

21. 0.000 1,273.356 ↑ 1.0 1 45,477

Aggregate (cost=15.80..15.81 rows=1 width=8) (actual time=0.028..0.028 rows=1 loops=45,477)

  • Buffers: shared hit=288,880 read=7,854
  • I/O Timings: read=774.000
22. 31.378 1,273.356 ↓ 0.0 0 45,477

Nested Loop (cost=5.01..15.80 rows=1 width=4) (actual time=0.027..0.028 rows=0 loops=45,477)

  • Join Filter: (((issue_links.source_id = issues.id) AND (issue_links.link_type = 1) AND (as_blocked.state_id = 1)) OR ((issue_links.target_id = issues.id) AND (issue_links.link_type = 2) AND (as_blocked_by.state_id = 1)))
  • Rows Removed by Join Filter: 0
  • Buffers: shared hit=288,880 read=7,854
  • I/O Timings: read=774.000
23. 35.056 1,227.879 ↓ 0.0 0 45,477

Nested Loop (cost=4.44..12.19 rows=1 width=16) (actual time=0.027..0.027 rows=0 loops=45,477)

  • Buffers: shared hit=285,826 read=7,843
  • I/O Timings: read=773.178
24. 363.816 1,182.402 ↓ 0.0 0 45,477

Bitmap Heap Scan on issue_links (cost=3.88..8.61 rows=1 width=14) (actual time=0.026..0.026 rows=0 loops=45,477)

  • Recheck Cond: ((source_id = issues.id) OR (target_id = issues.id))
  • Filter: (((source_id = issues.id) AND (link_type = 1)) OR ((target_id = issues.id) AND (link_type = 2)))
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=17,433
  • Buffers: shared hit=282,763 read=7,841
  • I/O Timings: read=773.157
25. 45.477 818.586 ↓ 0.0 0 45,477

BitmapOr (cost=3.88..3.88 rows=3 width=0) (actual time=0.018..0.018 rows=0 loops=45,477)

  • Buffers: shared hit=268,361 read=4,810
  • I/O Timings: read=476.788
26. 545.724 545.724 ↓ 0.0 0 45,477

Bitmap Index Scan on index_issue_links_on_source_id_and_target_id (cost=0.00..1.94 rows=2 width=0) (actual time=0.012..0.012 rows=0 loops=45,477)

  • Index Cond: (source_id = issues.id)
  • Buffers: shared hit=133,763 read=2,822
  • I/O Timings: read=386.268
27. 227.385 227.385 ↓ 0.0 0 45,477

Bitmap Index Scan on index_issue_links_on_target_id (cost=0.00..1.94 rows=2 width=0) (actual time=0.005..0.005 rows=0 loops=45,477)

  • Index Cond: (target_id = issues.id)
  • Buffers: shared hit=134,598 read=1,988
  • I/O Timings: read=90.520
28. 10.421 10.421 ↑ 1.0 1 613

Index Scan using issues_pkey on issues as_blocked (cost=0.56..3.58 rows=1 width=6) (actual time=0.017..0.017 rows=1 loops=613)

  • Index Cond: (id = issue_links.target_id)
  • Buffers: shared hit=3,063 read=2
  • I/O Timings: read=0.021
29. 14.099 14.099 ↑ 1.0 1 613

Index Scan using issues_pkey on issues as_blocked_by (cost=0.56..3.58 rows=1 width=6) (actual time=0.023..0.023 rows=1 loops=613)

  • Index Cond: (id = issue_links.source_id)
  • Buffers: shared hit=3,054 read=11
  • I/O Timings: read=0.822
Planning time : 18.750 ms
Execution time : 1,936.706 ms