explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pmQs : Optimization for: plan #Gpz2

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.032 151.489 ↑ 1.0 1 1

Aggregate (cost=3,794.30..3,794.31 rows=1 width=8) (actual time=151.489..151.489 rows=1 loops=1)

2. 0.664 151.457 ↑ 12.8 50 1

Hash Join (cost=3,674.46..3,792.70 rows=640 width=4) (actual time=125.602..151.457 rows=50 loops=1)

  • Hash Cond: (projects.namespace_id = gitlab_secondary_namespaces.id)
3. 2.792 139.206 ↓ 3.0 3,852 1

Hash Join (cost=309.56..417.32 rows=1,280 width=8) (actual time=113.778..139.206 rows=3,852 loops=1)

  • Hash Cond: (projects.id = design_management_designs.project_id)
4. 27.802 27.802 ↓ 1.6 4,001 1

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

5. 3.970 108.612 ↓ 19.3 3,852 1

Hash (cost=207.06..207.06 rows=200 width=4) (actual time=108.612..108.612 rows=3,852 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 168kB
6. 8.184 104.642 ↓ 19.3 3,852 1

HashAggregate (cost=205.06..207.06 rows=200 width=4) (actual time=103.459..104.642 rows=3,852 loops=1)

  • Group Key: design_management_designs.project_id
7. 96.458 96.458 ↓ 4.0 11,699 1

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

8. 0.018 11.587 ↑ 11.8 17 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
9. 0.028 11.569 ↑ 11.8 17 1

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

  • Group Key: gitlab_secondary_namespaces.id
10. 11.541 11.541 ↑ 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=2.838..11.541 rows=17 loops=1)

11.          

CTE base_and_descendants

12. 0.123 11.523 ↑ 1,130.3 17 1

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

13. 2.832 2.832 ↑ 1.2 12 1

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

14. 0.354 8.568 ↑ 960.0 2 2

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

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

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

16. 0.010 0.014 ↑ 18.8 8 2

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
17. 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)

Planning time : 0.554 ms
Execution time : 153.384 ms