explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pX51

Settings
# exclusive inclusive rows x rows loops node
1. 0.027 0.554 ↑ 3.3 12 1

HashAggregate (cost=56.01..56.61 rows=40 width=16) (actual time=0.548..0.554 rows=12 loops=1)

  • Group Key: date_part('day'::text, (merge_request_metrics.merged_at - merge_requests.created_at))
2. 0.036 0.527 ↑ 2.4 17 1

Nested Loop Left Join (cost=37.37..55.81 rows=40 width=8) (actual time=0.369..0.527 rows=17 loops=1)

  • Filter: ((project_features.merge_requests_access_level > 0) OR (project_features.merge_requests_access_level IS NULL))
3. 0.027 0.457 ↓ 5.7 17 1

Hash Semi Join (cost=37.22..53.74 rows=3 width=20) (actual time=0.350..0.457 rows=17 loops=1)

  • Hash Cond: (projects.namespace_id = namespaces.id)
4. 0.027 0.292 ↓ 2.8 17 1

Nested Loop (cost=7.08..23.54 rows=6 width=24) (actual time=0.194..0.292 rows=17 loops=1)

5. 0.041 0.146 ↓ 2.8 17 1

Hash Join (cost=6.94..8.72 rows=6 width=20) (actual time=0.119..0.146 rows=17 loops=1)

  • Hash Cond: (merge_request_metrics.merge_request_id = merge_requests.id)
6. 0.037 0.037 ↑ 1.0 22 1

Seq Scan on merge_request_metrics (cost=0.00..1.73 rows=22 width=12) (actual time=0.025..0.037 rows=22 loops=1)

  • Filter: (merged_at >= '2019-05-15 13:52:27.665'::timestamp without time zone)
  • Rows Removed by Filter: 36
7. 0.019 0.068 ↑ 1.0 17 1

Hash (cost=6.72..6.72 rows=17 width=16) (actual time=0.067..0.068 rows=17 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
8. 0.049 0.049 ↑ 1.0 17 1

Seq Scan on merge_requests (cost=0.00..6.72 rows=17 width=16) (actual time=0.011..0.049 rows=17 loops=1)

  • Filter: ((state)::text = 'merged'::text)
  • Rows Removed by Filter: 41
9. 0.094 0.119 ↑ 1.0 1 17

Index Scan using idx_projects_on_repository_storage_last_repository_updated_at on projects (cost=0.14..2.44 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=17)

  • Index Cond: (id = merge_requests.target_project_id)
  • Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (visibility_level = ANY ('{0,10,20}'::integer[])))
10.          

SubPlan (forIndex Scan)

11. 0.000 0.000 ↓ 0.0 0

Seq Scan on project_authorizations (cost=0.00..1.99 rows=1 width=0) (never executed)

  • Filter: ((user_id = 1) AND (project_id = projects.id))
12. 0.025 0.025 ↓ 2.1 17 1

Seq Scan on project_authorizations project_authorizations_1 (cost=0.00..1.83 rows=8 width=4) (actual time=0.011..0.025 rows=17 loops=1)

  • Filter: (user_id = 1)
  • Rows Removed by Filter: 67
13. 0.014 0.138 ↑ 5.5 2 1

Hash (cost=30.01..30.01 rows=11 width=4) (actual time=0.138..0.138 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.124 0.124 ↑ 5.5 2 1

CTE Scan on base_and_descendants namespaces (cost=29.68..29.90 rows=11 width=4) (actual time=0.026..0.124 rows=2 loops=1)

15.          

CTE base_and_descendants

16. 0.024 0.116 ↑ 5.5 2 1

Recursive Union (cost=0.14..29.68 rows=11 width=332) (actual time=0.023..0.116 rows=2 loops=1)

17. 0.016 0.016 ↑ 1.0 1 1

Index Scan using namespaces_pkey on namespaces namespaces_1 (cost=0.14..2.16 rows=1 width=332) (actual time=0.014..0.016 rows=1 loops=1)

  • Index Cond: (id = 63)
  • Filter: ((type)::text = 'Group'::text)
18. 0.034 0.076 ↓ 0.0 0 2

Hash Join (cost=0.47..2.73 rows=1 width=332) (actual time=0.033..0.038 rows=0 loops=2)

  • Hash Cond: (namespaces_2.parent_id = base_and_descendants.id)
19. 0.030 0.030 ↑ 1.0 12 2

Index Scan using index_namespaces_on_type on namespaces namespaces_2 (cost=0.14..2.35 rows=12 width=332) (actual time=0.009..0.015 rows=12 loops=2)

  • Index Cond: ((type)::text = 'Group'::text)
20. 0.006 0.012 ↑ 10.0 1 2

Hash (cost=0.20..0.20 rows=10 width=4) (actual time=0.006..0.006 rows=1 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
21. 0.006 0.006 ↑ 10.0 1 2

WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.003..0.003 rows=1 loops=2)

22. 0.034 0.034 ↑ 1.0 1 17

Index Scan using index_project_features_on_project_id on project_features (cost=0.15..0.61 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=17)

  • Index Cond: (projects.id = project_id)
Planning time : 3.288 ms
Execution time : 0.991 ms