explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 91x2

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 307.869 ↓ 10.0 10 1

Limit (cost=4,102.46..4,102.50 rows=1 width=91) (actual time=307.761..307.869 rows=10 loops=1)

2. 0.088 307.860 ↓ 10.0 10 1

Unique (cost=4,102.46..4,102.50 rows=1 width=91) (actual time=307.759..307.860 rows=10 loops=1)

3. 0.668 307.772 ↓ 196.0 196 1

Sort (cost=4,102.46..4,102.47 rows=1 width=91) (actual time=307.758..307.772 rows=196 loops=1)

  • Sort Key: (count(labels.id) OVER (?)) DESC, labels.id, labels.title, labels.color, labels.project_id, labels.created_at, labels.updated_at, labels.template, labels.description, labels.description_html, labels.group_id, labels.cached_markdown_version
  • Sort Method: quicksort Memory: 253kB
4. 0.291 307.104 ↓ 441.0 441 1

WindowAgg (cost=4,102.43..4,102.45 rows=1 width=91) (actual time=306.740..307.104 rows=441 loops=1)

5. 0.309 306.813 ↓ 441.0 441 1

Sort (cost=4,102.43..4,102.44 rows=1 width=83) (actual time=306.727..306.813 rows=441 loops=1)

  • Sort Key: labels.id
  • Sort Method: quicksort Memory: 248kB
6. 0.214 306.504 ↓ 441.0 441 1

Nested Loop (cost=3,715.47..4,102.42 rows=1 width=83) (actual time=304.160..306.504 rows=441 loops=1)

7. 0.093 304.952 ↓ 8.1 446 1

Nested Loop (cost=3,715.03..4,074.72 rows=55 width=4) (actual time=304.140..304.952 rows=446 loops=1)

8. 0.131 304.159 ↑ 1.0 100 1

HashAggregate (cost=3,714.47..3,715.47 rows=100 width=4) (actual time=304.123..304.159 rows=100 loops=1)

  • Group Key: merge_requests.id
9. 0.047 304.028 ↑ 1.0 100 1

Limit (cost=1,245.95..3,713.22 rows=100 width=4) (actual time=35.147..304.028 rows=100 loops=1)

10. 19.128 303.981 ↑ 4,477.8 100 1

Nested Loop Semi Join (cost=1,245.95..11,049,142.73 rows=447,779 width=4) (actual time=35.146..303.981 rows=100 loops=1)

11. 101.203 101.203 ↑ 1,056.0 36,730 1

Index Scan Backward using merge_requests_pkey on merge_requests (cost=0.56..9,138,363.77 rows=38,785,961 width=8) (actual time=0.040..101.203 rows=36,730 loops=1)

12. 0.000 183.650 ↓ 0.0 0 36,730

Hash Semi Join (cost=1,245.39..1,245.43 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=36,730)

  • Hash Cond: (projects.namespace_id = namespaces.id)
13. 183.650 183.650 ↑ 1.0 1 36,730

Index Scan using projects_pkey on projects (cost=0.43..0.47 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=36,730)

  • Index Cond: (id = merge_requests.target_project_id)
14. 0.014 0.935 ↑ 1.5 88 1

Hash (cost=1,243.32..1,243.32 rows=131 width=4) (actual time=0.935..0.935 rows=88 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
15. 0.921 0.921 ↑ 1.5 88 1

CTE Scan on base_and_descendants namespaces (cost=1,239.39..1,242.01 rows=131 width=4) (actual time=0.023..0.921 rows=88 loops=1)

16.          

CTE base_and_descendants

17. 0.161 0.825 ↑ 1.5 88 1

Recursive Union (cost=0.43..1,239.39 rows=131 width=323) (actual time=0.020..0.825 rows=88 loops=1)

18. 0.014 0.014 ↑ 1.0 1 1

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

  • Index Cond: (id = 9970)
  • Filter: ((type)::text = 'Group'::text)
19. 0.029 0.650 ↓ 1.3 17 5

Nested Loop (cost=0.43..123.33 rows=13 width=323) (actual time=0.015..0.130 rows=17 loops=5)

20. 0.005 0.005 ↓ 1.8 18 5

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

21. 0.616 0.616 ↑ 1.0 1 88

Index Scan using index_namespaces_on_parent_id_and_id on namespaces namespaces_2 (cost=0.43..12.30 rows=1 width=323) (actual time=0.005..0.007 rows=1 loops=88)

  • Index Cond: (parent_id = base_and_descendants.id)
  • Filter: ((type)::text = 'Group'::text)
22. 0.700 0.700 ↓ 4.0 4 100

Index Scan using index_label_links_on_target_id_and_target_type on label_links (cost=0.56..3.58 rows=1 width=8) (actual time=0.005..0.007 rows=4 loops=100)

  • Index Cond: ((target_id = merge_requests.id) AND ((target_type)::text = 'MergeRequest'::text))
23. 1.338 1.338 ↑ 1.0 1 446

Index Scan using labels_pkey on labels (cost=0.43..0.49 rows=1 width=83) (actual time=0.003..0.003 rows=1 loops=446)

  • Index Cond: (id = label_links.label_id)
  • Filter: ((type)::text = 'GroupLabel'::text)
  • Rows Removed by Filter: 0
Planning time : 2.412 ms
Execution time : 308.065 ms