explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IPt9

Settings
# exclusive inclusive rows x rows loops node
1. 0.011 2,780.992 ↓ 7.0 7 1

Nested Loop Left Join (cost=68,480.68..112,646.97 rows=1 width=91) (actual time=2,780.820..2,780.992 rows=7 loops=1)

2. 0.133 2,780.946 ↓ 7.0 7 1

Merge Join (cost=68,480.39..112,638.66 rows=1 width=88) (actual time=2,780.788..2,780.946 rows=7 loops=1)

  • Merge Cond: (history.fom_pallet_id = pallet.id)
3. 7.675 2,779.108 ↓ 5.9 59 1

Nested Loop (cost=68,270.32..131,342.48 rows=10 width=88) (actual time=2,775.353..2,779.108 rows=59 loops=1)

  • Join Filter: (history.fom_pallet_id = inthist.fom_pallet_id)
  • Rows Removed by Join Filter: 82,187
4. 0.693 1,250.579 ↑ 1.8 1,394 1

Finalize GroupAggregate (cost=65,811.05..66,525.22 rows=2,477 width=16) (actual time=1,247.317..1,250.579 rows=1,394 loops=1)

  • Group Key: inthist.fom_pallet_id
5. 236.900 1,249.886 ↑ 1.8 2,701 1

Gather Merge (cost=65,811.05..66,475.68 rows=4,954 width=16) (actual time=1,247.312..1,249.886 rows=2,701 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 1.156 1,012.986 ↑ 2.8 900 3 / 3

Partial GroupAggregate (cost=64,811.03..64,903.84 rows=2,477 width=16) (actual time=1,011.503..1,012.986 rows=900 loops=3)

  • Group Key: inthist.fom_pallet_id
7. 4.390 1,011.830 ↑ 1.2 7,258 3 / 3

Sort (cost=64,811.03..64,833.71 rows=9,072 width=8) (actual time=1,011.486..1,011.830 rows=7,258 loops=3)

  • Sort Key: inthist.fom_pallet_id
  • Sort Method: quicksort Memory: 510kB
  • Worker 0: Sort Method: quicksort Memory: 421kB
  • Worker 1: Sort Method: quicksort Memory: 503kB
8. 125.280 1,007.440 ↑ 1.2 7,258 3 / 3

Hash Join (cost=778.94..64,214.67 rows=9,072 width=8) (actual time=11.584..1,007.440 rows=7,258 loops=3)

  • Hash Cond: (inthist.id = intbox.latest_fom_transportation_box_history_id)
9. 872.170 872.170 ↑ 1.3 651,565 3 / 3

Parallel Seq Scan on fom_transportation_box_history inthist (cost=0.00..60,280.92 rows=817,092 width=16) (actual time=0.350..872.170 rows=651,565 loops=3)

10. 4.065 9.990 ↑ 1.0 21,773 3 / 3

Hash (cost=506.75..506.75 rows=21,775 width=8) (actual time=9.989..9.990 rows=21,773 loops=3)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,107kB
11. 5.925 5.925 ↑ 1.0 21,775 3 / 3

Seq Scan on fom_transportation_box intbox (cost=0.00..506.75 rows=21,775 width=8) (actual time=0.293..5.925 rows=21,775 loops=3)

12. 0.000 1,520.854 ↓ 3.9 59 1,394

Materialize (cost=2,459.26..64,235.20 rows=15 width=72) (actual time=0.491..1.091 rows=59 loops=1,394)

13. 395.364 1,536.187 ↓ 3.9 59 1

Gather (cost=2,459.26..64,235.13 rows=15 width=72) (actual time=684.558..1,536.187 rows=59 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
14. 1.607 1,140.823 ↓ 3.3 20 3 / 3

Hash Join (cost=1,459.26..63,233.63 rows=6 width=72) (actual time=305.898..1,140.823 rows=20 loops=3)

  • Hash Cond: (history.id = boxes.latest_fom_transportation_box_history_id)
15. 54.878 1,123.438 ↓ 4.6 2,615 3 / 3

Hash Join (cost=680.32..62,452.50 rows=567 width=64) (actual time=10.848..1,123.438 rows=2,615 loops=3)

  • Hash Cond: (history.plo_production_plan_id = prod_plan.id)
16. 1,062.824 1,062.824 ↑ 1.3 452,158 3 / 3

Parallel Seq Scan on fom_transportation_box_history history (cost=0.00..60,280.92 rows=567,988 width=48) (actual time=0.260..1,062.824 rows=452,158 loops=3)

  • Filter: (fom_pallet_id IS NOT NULL)
  • Rows Removed by Filter: 199,408
17. 0.037 5.736 ↑ 1.0 22 3 / 3

Hash (cost=680.05..680.05 rows=22 width=32) (actual time=5.735..5.736 rows=22 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
18. 5.699 5.699 ↑ 1.0 22 3 / 3

Seq Scan on production_order_planned prod_plan (cost=0.00..680.05 rows=22 width=32) (actual time=0.408..5.699 rows=22 loops=3)

  • Filter: (equipment_id = '1030700000000000091'::bigint)
  • Rows Removed by Filter: 21,486
19. 6.986 15.778 ↑ 1.0 21,773 3 / 3

Hash (cost=506.75..506.75 rows=21,775 width=24) (actual time=15.777..15.778 rows=21,773 loops=3)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,447kB
20. 8.792 8.792 ↑ 1.0 21,775 3 / 3

Seq Scan on fom_transportation_box boxes (cost=0.00..506.75 rows=21,775 width=24) (actual time=0.339..8.792 rows=21,775 loops=3)

21. 0.657 1.705 ↑ 1.0 1,535 1

Sort (cost=210.08..213.93 rows=1,543 width=8) (actual time=1.657..1.705 rows=1,535 loops=1)

  • Sort Key: pallet.id
  • Sort Method: quicksort Memory: 121kB
22. 1.048 1.048 ↓ 1.0 1,550 1

Seq Scan on fom_pallet pallet (cost=0.00..128.36 rows=1,543 width=8) (actual time=0.030..1.048 rows=1,550 loops=1)

  • Filter: (state = 0)
  • Rows Removed by Filter: 4,730
23. 0.035 0.035 ↑ 1.0 1 7

Index Scan using order_cycle_operation_pkey on order_cycle_operation oco (cost=0.29..8.31 rows=1 width=27) (actual time=0.005..0.005 rows=1 loops=7)

  • Index Cond: ((order_id = prod_plan.order_id) AND (operation_id = prod_plan.operation_id))
Planning time : 2.476 ms
Execution time : 2,801.722 ms