explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bSId

Settings
# exclusive inclusive rows x rows loops node
1. 65.358 53,063.743 ↓ 0.0 0 1

Insert on order_set (cost=2,753,836.60..2,950,568.58 rows=9,836,599 width=30) (actual time=53,063.743..53,063.743 rows=0 loops=1)

2.          

CTE recon_notice

3. 51.948 2,024.104 ↓ 21,243.2 212,432 1

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

  • Group Key: orn.order_id
4. 136.630 1,972.156 ↓ 21,562.8 215,628 1

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

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

Hash Join (cost=66,717.57..126,222.96 rows=10 width=8) (actual time=1,014.376..1,835.526 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. 244.151 244.151 ↑ 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.117..244.151 rows=1,419,894 loops=1)

7. 62.758 1,013.939 ↓ 2.4 212,432 1

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

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

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

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

  • Heap Fetches: 0
10. 1,138.923 52,998.385 ↑ 60.6 162,452 1

Unique (cost=2,627,613.31..2,725,979.30 rows=9,836,599 width=30) (actual time=50,659.437..52,998.385 rows=162,452 loops=1)

11. 9,804.862 51,859.462 ↑ 1.0 9,830,389 1

Sort (cost=2,627,613.31..2,652,204.80 rows=9,836,599 width=30) (actual time=50,659.436..51,859.462 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
12. 37,534.762 42,054.600 ↑ 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,121.907..42,054.600 rows=9,830,389 loops=1)

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

14. 38.502 2,121.683 ↓ 21,243.2 212,432 1

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

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

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

Planning time : 0.810 ms
Execution time : 53,138.047 ms