explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QMia : https://gitlab.com/gitlab-org/gitlab/merge_requests/20173#note_251915874

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

Aggregate (cost=1,096.76..1,096.77 rows=1 width=8) (actual time=4.948..4.948 rows=1 loops=1)

  • Buffers: shared hit=393
2. 1.189 4.868 ↑ 40.9 143 1

Hash Join (cost=759.78..1,082.14 rows=5,850 width=4) (actual time=2.379..4.868 rows=143 loops=1)

  • Hash Cond: (design_management_designs.project_id = projects.id)
  • Buffers: shared hit=393
3. 1.342 1.342 ↑ 1.0 11,699 1

Seq Scan on design_management_designs (cost=0.00..219.99 rows=11,699 width=4) (actual time=0.010..1.342 rows=11,699 loops=1)

  • Buffers: shared hit=103
4. 0.022 2.337 ↑ 37.7 53 1

Hash (cost=734.78..734.78 rows=2,000 width=4) (actual time=2.337..2.337 rows=53 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 18kB
  • Buffers: shared hit=290
5. 1.120 2.315 ↑ 37.7 53 1

Hash Join (cost=408.03..734.78 rows=2,000 width=4) (actual time=0.579..2.315 rows=53 loops=1)

  • Hash Cond: (projects.namespace_id = namespaces.id)
  • Buffers: shared hit=290
6. 0.672 0.672 ↓ 1.0 4,001 1

Seq Scan on projects (cost=0.00..294.00 rows=4,000 width=8) (actual time=0.003..0.672 rows=4,001 loops=1)

  • Buffers: shared hit=254
7. 0.015 0.523 ↑ 11.8 17 1

Hash (cost=405.53..405.53 rows=200 width=4) (actual time=0.523..0.523 rows=17 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=36
  • -> HashAggregate (cost=403.53..405.53 rows=200 width=4) (actual time=0.514..0.517 rows=17 loops=)
  • Group Key: namespaces.id
  • Buffers: shared hit=36
8. 0.508 0.508 ↑ 59.8 17 1

CTE Scan on base_and_descendants namespaces (cost=370.47..390.81 rows=1,017 width=4) (actual time=0.020..0.508 rows=17 loops=1)

  • Buffers: shared hit=36
9.          

CTE base_and_descendants

10. 0.011 0.503 ↑ 59.8 17 1

Recursive Union (cost=2.21..370.47 rows=1,017 width=4) (actual time=0.018..0.503 rows=17 loops=1)

  • Buffers: shared hit=36
11. 0.011 0.018 ↓ 0.0 12 1

Bitmap Heap Scan on geo_node_namespace_links (cost=2.21..9.65 rows=7 =4) (cost=0..0 rows=0 width=0) (actual time=0.016..0.018 rows=12 loops=1)

  • Recheck Cond: (geo_node_id = 2)
  • Heap Blocks: exact=1
  • Buffers: shared hit=2
12. 0.007 0.007 ↓ 1.7 12 1

Bitmap Index Scan on index_geo_node_namespace_links_on_geo_node_id (cost=0.00..2.21 rows=7 width=0) (actual time=0.007..0.007 rows=12 loops=1)

  • Index Cond: (geo_node_id = 2)
  • Buffers: shared hit=1
13. 0.234 0.474 ↑ 50.5 2 2

Hash Join (cost=2.28..34.05 rows=101 width=4) (actual time=0.183..0.237 rows=2 loops=2)

  • Hash Cond: (namespaces_1.parent_id = base_and_descendants.id)
  • Buffers: shared hit=34
14. 0.230 0.230 ↓ 1.0 1,004 2

Seq Scan on namespaces namespaces_1 (cost=0.00..27.01 rows=1,001 width=8) (actual time=0.004..0.115 rows=1,004 loops=2)

  • Buffers: shared hit=34
15. 0.008 0.010 ↑ 8.8 8 2

Hash (cost=1.40..1.40 rows=70 width=4) (actual time=0.005..0.005 rows=8 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 0.002 0.002 ↑ 8.8 8 2

WorkTable Scan on base_and_descendants (cost=0.00..1.40 rows=70 width=4) (actual time=0.000..0.001 rows=8 loops=2)

Planning time : 0.911 ms
Execution time : 5.206 ms