explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wmm5

Settings
# exclusive inclusive rows x rows loops node
1. 171.291 3,922.261 ↓ 7.3 292,035 1

Sort (cost=320,431.84..320,531.84 rows=40,000 width=166) (actual time=3,882.477..3,922.261 rows=292,035 loops=1)

  • Sort Key: ua.asset_id
  • Sort Method: quicksort Memory: 49,899kB
2. 653.083 3,750.970 ↓ 7.3 292,035 1

HashAggregate (cost=316,874.30..317,374.30 rows=40,000 width=166) (actual time=3,631.536..3,750.970 rows=292,035 loops=1)

  • Group Key: ua.asset_id, u.name
3. 648.631 3,097.887 ↑ 2.0 866,736 1

HashAggregate (cost=269,420.41..286,676.37 rows=1,725,596 width=352) (actual time=2,859.977..3,097.887 rows=866,736 loops=1)

  • Group Key: ua.asset_id, u.organization, u.name
4. 69.602 2,449.256 ↑ 1.7 1,020,156 1

Append (cost=31,902.48..256,478.44 rows=1,725,596 width=352) (actual time=217.285..2,449.256 rows=1,020,156 loops=1)

5. 160.492 1,003.030 ↓ 1.1 864,978 1

Hash Join (cost=31,902.48..99,600.88 rows=796,998 width=34) (actual time=217.285..1,003.030 rows=864,978 loops=1)

  • Hash Cond: (ua.universe_id = u.id)
6. 405.406 842.449 ↓ 1.1 864,978 1

Hash Join (cost=31,811.26..97,350.37 rows=796,998 width=20) (actual time=217.176..842.449 rows=864,978 loops=1)

  • Hash Cond: (ua.asset_id = a.id)
7. 223.795 223.795 ↓ 1.0 1,273,792 1

Seq Scan on universe_assets ua (cost=0.00..62,198.41 rows=1,272,641 width=20) (actual time=0.006..223.795 rows=1,273,792 loops=1)

8. 103.612 213.248 ↓ 1.0 535,519 1

Hash (cost=25,139.06..25,139.06 rows=533,776 width=16) (actual time=213.248..213.248 rows=535,519 loops=1)

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 33,295kB
9. 109.636 109.636 ↓ 1.0 535,519 1

Seq Scan on assets a (cost=0.00..25,139.06 rows=533,776 width=16) (actual time=0.007..109.636 rows=535,519 loops=1)

  • Filter: (discovery_status = 200)
  • Rows Removed by Filter: 256,082
10. 0.029 0.089 ↑ 1.0 132 1

Hash (cost=89.57..89.57 rows=132 width=22) (actual time=0.089..0.089 rows=132 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
11. 0.060 0.060 ↑ 1.0 132 1

Index Scan using universes_pkey on universes u (cost=0.14..89.57 rows=132 width=22) (actual time=0.013..0.060 rows=132 loops=1)

12. 30.053 1,376.624 ↑ 6.0 155,178 1

Hash Join (cost=31,930.00..139,621.60 rows=928,598 width=34) (actual time=212.856..1,376.624 rows=155,178 loops=1)

  • Hash Cond: (ua_1.universe_id = u_1.id)
13. 45.742 1,346.486 ↑ 6.0 155,178 1

Hash Join (cost=31,838.78..137,014.55 rows=928,598 width=20) (actual time=212.742..1,346.486 rows=155,178 loops=1)

  • Hash Cond: (ur.child_id = a_1.id)
14. 134.049 1,092.022 ↑ 8.8 155,178 1

Merge Join (cost=27.52..101,602.89 rows=1,371,570 width=20) (actual time=0.074..1,092.022 rows=155,178 loops=1)

  • Merge Cond: (ur.parent_id = ua_1.asset_id)
15. 30.128 30.128 ↑ 1.0 193,331 1

Index Only Scan using underlying_relationships_pkey on underlying_relationships ur (cost=0.42..4,944.01 rows=193,980 width=32) (actual time=0.041..30.128 rows=193,331 loops=1)

  • Heap Fetches: 40,321
16. 169.491 927.845 ↑ 1.0 1,253,558 1

Materialize (cost=0.43..85,613.78 rows=1,272,641 width=20) (actual time=0.015..927.845 rows=1,253,558 loops=1)

17. 758.354 758.354 ↑ 1.1 1,179,738 1

Index Scan using idx_universe_assets_assetid on universe_assets ua_1 (cost=0.43..82,432.18 rows=1,272,641 width=20) (actual time=0.011..758.354 rows=1,179,738 loops=1)

18. 100.066 208.722 ↓ 1.0 535,519 1

Hash (cost=25,139.06..25,139.06 rows=533,776 width=16) (actual time=208.722..208.722 rows=535,519 loops=1)

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 33,295kB
19. 108.656 108.656 ↓ 1.0 535,519 1

Seq Scan on assets a_1 (cost=0.00..25,139.06 rows=533,776 width=16) (actual time=0.007..108.656 rows=535,519 loops=1)

  • Filter: (discovery_status = 200)
  • Rows Removed by Filter: 256,082
20. 0.029 0.085 ↑ 1.0 132 1

Hash (cost=89.57..89.57 rows=132 width=22) (actual time=0.085..0.085 rows=132 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
21. 0.056 0.056 ↑ 1.0 132 1

Index Scan using universes_pkey on universes u_1 (cost=0.14..89.57 rows=132 width=22) (actual time=0.012..0.056 rows=132 loops=1)