explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Vuu2p

Settings
# exclusive inclusive rows x rows loops node
1. 526.358 3,644.388 ↑ 2.0 866,486 1

Sort (cost=484,606.70..488,945.50 rows=1,735,522 width=352) (actual time=3,521.074..3,644.388 rows=866,486 loops=1)

  • Sort Key: ua.asset_id
  • Sort Method: quicksort Memory: 92,558kB
2. 603.802 3,118.030 ↑ 2.0 866,486 1

HashAggregate (cost=270,035.97..287,391.19 rows=1,735,522 width=352) (actual time=2,915.528..3,118.030 rows=866,486 loops=1)

  • Group Key: ua.asset_id, u.organization, u.name
3. 68.543 2,514.228 ↑ 1.7 1,011,301 1

Append (cost=31,902.48..257,019.56 rows=1,735,522 width=352) (actual time=210.787..2,514.228 rows=1,011,301 loops=1)

4. 154.506 985.570 ↓ 1.1 864,830 1

Hash Join (cost=31,902.48..99,600.88 rows=796,998 width=34) (actual time=210.786..985.570 rows=864,830 loops=1)

  • Hash Cond: (ua.universe_id = u.id)
5. 402.550 830.968 ↓ 1.1 864,830 1

Hash Join (cost=31,811.26..97,350.37 rows=796,998 width=20) (actual time=210.663..830.968 rows=864,830 loops=1)

  • Hash Cond: (ua.asset_id = a.id)
6. 221.664 221.664 ↓ 1.0 1,273,679 1

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

7. 97.921 206.754 ↓ 1.0 535,459 1

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

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 33,292kB
8. 108.833 108.833 ↓ 1.0 535,459 1

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

  • Filter: (discovery_status = 200)
  • Rows Removed by Filter: 261,446
9. 0.035 0.096 ↑ 1.0 132 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
10. 0.061 0.061 ↑ 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.061 rows=132 loops=1)

11. 28.378 1,460.115 ↑ 6.4 146,471 1

Hash Join (cost=31,930.78..140,063.46 rows=938,524 width=34) (actual time=216.663..1,460.115 rows=146,471 loops=1)

  • Hash Cond: (ua_1.universe_id = u_1.id)
12. 44.494 1,431.652 ↑ 6.4 146,471 1

Hash Join (cost=31,839.56..137,429.52 rows=938,524 width=20) (actual time=216.546..1,431.652 rows=146,471 loops=1)

  • Hash Cond: (ur.child_id = a_1.id)
13. 130.792 1,174.628 ↑ 9.5 146,471 1

Merge Join (cost=28.30..101,979.37 rows=1,386,231 width=20) (actual time=0.075..1,174.628 rows=146,471 loops=1)

  • Merge Cond: (ur.parent_id = ua_1.asset_id)
14. 30.979 30.979 ↑ 1.0 196,032 1

Index Only Scan using underlying_relationships_pkey on underlying_relationships ur (cost=0.42..5,098.31 rows=196,054 width=32) (actual time=0.040..30.979 rows=196,032 loops=1)

  • Heap Fetches: 43,771
15. 170.770 1,012.857 ↑ 1.0 1,248,538 1

Materialize (cost=0.43..85,613.78 rows=1,272,641 width=20) (actual time=0.016..1,012.857 rows=1,248,538 loops=1)

16. 842.087 842.087 ↑ 1.1 1,179,526 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.012..842.087 rows=1,179,526 loops=1)

17. 104.002 212.530 ↓ 1.0 535,459 1

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

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 33,292kB
18. 108.528 108.528 ↓ 1.0 535,459 1

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

  • Filter: (discovery_status = 200)
  • Rows Removed by Filter: 261,446
19. 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
20. 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.013..0.056 rows=132 loops=1)