explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sp5W

Settings
# exclusive inclusive rows x rows loops node
1. 10,876.647 48,483.534 ↓ 0.0 0 1

Insert on order_set (cost=126,223.62..1,140,628.00 rows=9,836,599 width=30) (actual time=48,483.534..48,483.534 rows=0 loops=1)

2.          

CTE recon_notice

3. 52.584 1,906.297 ↓ 21,243.2 212,432 1

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

  • Group Key: orn.order_id
4. 130.596 1,853.713 ↓ 21,562.8 215,628 1

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

  • Sort Key: orn.order_id
  • Sort Method: external merge Disk: 3816kB
5. 511.860 1,723.117 ↓ 21,562.8 215,628 1

Hash Join (cost=66,717.57..126,222.96 rows=10 width=8) (actual time=995.834..1,723.117 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))))
6. 215.825 215.825 ↑ 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..215.825 rows=1,419,894 loops=1)

7. 57.738 995.432 ↓ 2.4 212,432 1

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

  • Buckets: 65536 (originally 65536) Batches: 4 (originally 2) Memory Usage: 3585kB
8. 744.755 937.694 ↓ 2.4 212,432 1

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

  • Group Key: order_notice.order_id
9. 192.939 192.939 ↑ 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..192.939 rows=1,419,894 loops=1)

  • Heap Fetches: 0
10. 33,348.732 37,606.887 ↑ 1.0 9,830,389 1

Hash Left Join (cost=0.33..1,014,404.70 rows=9,836,599 width=30) (actual time=2,002.651..37,606.887 rows=9,830,389 loops=1)

  • Hash Cond: (vw.order_id = ni.order_id)
11. 2,255.710 2,255.710 ↑ 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.117..2,255.710 rows=9,830,389 loops=1)

12. 37.041 2,002.445 ↓ 21,243.2 212,432 1

Hash (cost=0.20..0.20 rows=10 width=8) (actual time=2,002.445..2,002.445 rows=212,432 loops=1)

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

CTE Scan on recon_notice ni (cost=0.00..0.20 rows=10 width=8) (actual time=1,826.253..1,965.404 rows=212,432 loops=1)

Planning time : 0.773 ms
Execution time : 48,486.275 ms