explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Gpz2

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.080 178.798 ↑ 1.0 1 1

Aggregate (cost=4,338.87..4,338.88 rows=1 width=8) (actual time=178.798..178.798 rows=1 loops=1)

2. 10.959 178.718 ↑ 130.9 143 1

Merge Join (cost=4,004.87..4,292.07 rows=18,720 width=4) (actual time=166.211..178.718 rows=143 loops=1)

  • Merge Cond: (projects.id = design_management_designs.project_id)
3. 0.069 41.377 ↑ 24.2 53 1

Sort (cost=3,638.72..3,641.92 rows=1,280 width=4) (actual time=41.363..41.377 rows=53 loops=1)

  • Sort Key: projects.id
  • Sort Method: quicksort Memory: 27kB
4. 0.948 41.308 ↑ 24.2 53 1

Hash Join (cost=3,464.90..3,572.66 rows=1,280 width=4) (actual time=7.716..41.308 rows=53 loops=1)

  • Hash Cond: (projects.namespace_id = gitlab_secondary_namespaces.id)
5. 33.601 33.601 ↓ 1.6 4,001 1

Foreign Scan on projects (cost=100.00..186.80 rows=2,560 width=8) (actual time=0.909..33.601 rows=4,001 loops=1)

6. 0.008 6.759 ↑ 11.8 17 1

Hash (cost=3,362.40..3,362.40 rows=200 width=4) (actual time=6.759..6.759 rows=17 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
7. 0.024 6.751 ↑ 11.8 17 1

HashAggregate (cost=3,360.40..3,362.40 rows=200 width=4) (actual time=6.745..6.751 rows=17 loops=1)

  • Group Key: gitlab_secondary_namespaces.id
8. 6.727 6.727 ↑ 1,130.3 17 1

CTE Scan on base_and_descendants gitlab_secondary_namespaces (cost=2,735.91..3,120.21 rows=19,215 width=4) (actual time=0.535..6.727 rows=17 loops=1)

9.          

CTE base_and_descendants

10. 0.115 6.715 ↑ 1,130.3 17 1

Recursive Union (cost=100.00..2,735.91 rows=19,215 width=4) (actual time=0.533..6.715 rows=17 loops=1)

11. 0.532 0.532 ↑ 1.2 12 1

Foreign Scan on geo_node_namespace_links (cost=100.00..146.86 rows=15 width=4) (actual time=0.529..0.532 rows=12 loops=1)

12. 0.352 6.068 ↑ 960.0 2 2

Hash Join (cost=104.88..220.47 rows=1,920 width=4) (actual time=2.542..3.034 rows=2 loops=2)

  • Hash Cond: (namespaces.parent_id = base_and_descendants.id)
13. 5.704 5.704 ↑ 2.5 1,004 2

Foreign Scan on namespaces (cost=100.00..186.80 rows=2,560 width=8) (actual time=0.460..2.852 rows=1,004 loops=2)

14. 0.008 0.012 ↑ 18.8 8 2

Hash (cost=3.00..3.00 rows=150 width=4) (actual time=0.006..0.006 rows=8 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
15. 0.004 0.004 ↑ 18.8 8 2

WorkTable Scan on base_and_descendants (cost=0.00..3.00 rows=150 width=4) (actual time=0.001..0.002 rows=8 loops=2)

16. 7.863 126.382 ↓ 4.0 11,614 1

Sort (cost=366.15..373.46 rows=2,925 width=4) (actual time=124.787..126.382 rows=11,614 loops=1)

  • Sort Key: design_management_designs.project_id
  • Sort Method: quicksort Memory: 933kB
17. 118.519 118.519 ↓ 4.0 11,699 1

Foreign Scan on design_management_designs (cost=100.00..197.75 rows=2,925 width=4) (actual time=0.548..118.519 rows=11,699 loops=1)

Planning time : 0.424 ms
Execution time : 180.365 ms