explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ugMN

Settings
# exclusive inclusive rows x rows loops node
1. 1,117.249 52,949.231 ↑ 60.6 162,452 1

Unique (cost=2,742,729.32..2,841,095.31 rows=9,836,599 width=30) (actual time=50,646.858..52,949.231 rows=162,452 loops=1)

2. 9,882.746 51,831.982 ↑ 1.0 9,830,389 1

Sort (cost=2,742,729.32..2,767,320.81 rows=9,836,599 width=30) (actual time=50,646.857..51,831.982 rows=9,830,389 loops=1)

  • Sort Key: vw.order_id, vw.order_name, ni.most_recent_notice_id
  • Sort Method: external merge Disk: 407368kB
3. 37,661.156 41,949.236 ↑ 1.0 9,830,389 1

Hash Left Join (cost=126,223.52..1,129,520.71 rows=9,836,599 width=30) (actual time=2,034.620..41,949.236 rows=9,830,389 loops=1)

  • Hash Cond: (vw.order_id = ni.order_id)
4. 2,253.674 2,253.674 ↑ 1.0 9,830,389 1

Seq Scan on vw_spot_and_header vw (cost=0.00..977,470.99 rows=9,836,599 width=26) (actual time=0.116..2,253.674 rows=9,830,389 loops=1)

5. 39.151 2,034.406 ↓ 21,243.2 212,432 1

Hash (cost=126,223.40..126,223.40 rows=10 width=8) (actual time=2,034.406..2,034.406 rows=212,432 loops=1)

  • Buckets: 131072 (originally 1024) Batches: 4 (originally 1) Memory Usage: 3106kB
6. 19.404 1,995.255 ↓ 21,243.2 212,432 1

Subquery Scan on ni (cost=126,223.12..126,223.40 rows=10 width=8) (actual time=1,896.151..1,995.255 rows=212,432 loops=1)

7. 52.370 1,975.851 ↓ 21,243.2 212,432 1

GroupAggregate (cost=126,223.12..126,223.30 rows=10 width=8) (actual time=1,896.150..1,975.851 rows=212,432 loops=1)

  • Group Key: orn.order_id
8. 133.409 1,923.481 ↓ 21,562.8 215,628 1

Sort (cost=126,223.12..126,223.15 rows=10 width=8) (actual time=1,896.143..1,923.481 rows=215,628 loops=1)

  • Sort Key: orn.order_id
  • Sort Method: external merge Disk: 3816kB
9. 526.974 1,790.072 ↓ 21,562.8 215,628 1

Hash Join (cost=66,717.57..126,222.96 rows=10 width=8) (actual time=1,047.061..1,790.072 rows=215,628 loops=1)

  • Hash Cond: ((orn.order_id = order_notice.order_id) AND ((orn.time_received)::text = (max((order_notice.time_received)::text))))
10. 216.433 216.433 ↑ 1.0 1,419,894 1

Seq Scan on order_notice orn (cost=0.00..31,937.94 rows=1,419,894 width=28) (actual time=0.110..216.433 rows=1,419,894 loops=1)

11. 62.596 1,046.665 ↓ 2.4 212,432 1

Hash (cost=64,677.89..64,677.89 rows=89,379 width=36) (actual time=1,046.665..1,046.665 rows=212,432 loops=1)

  • Buckets: 65536 (originally 65536) Batches: 4 (originally 2) Memory Usage: 3585kB
12. 781.534 984.069 ↓ 2.4 212,432 1

GroupAggregate (cost=0.43..63,784.10 rows=89,379 width=36) (actual time=0.033..984.069 rows=212,432 loops=1)

  • Group Key: order_notice.order_id
13. 202.535 202.535 ↑ 1.0 1,419,894 1

Index Only Scan using idx_stage_admiral_traffic_order_notice_jn_ids on order_notice (cost=0.43..55,790.84 rows=1,419,894 width=24) (actual time=0.018..202.535 rows=1,419,894 loops=1)

  • Heap Fetches: 0
Planning time : 0.704 ms
Execution time : 53,075.357 ms