explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9iCx

Settings
# exclusive inclusive rows x rows loops node
1. 0.016 2,230.858 ↓ 2.0 10 1

Merge Left Join (cost=264,303.42..264,303.47 rows=5 width=46) (actual time=2,230.843..2,230.858 rows=10 loops=1)

  • Merge Cond: ((chiller.id)::text = (carccnt.id)::text)
  • Buffers: shared hit=250,682 read=10,857
2.          

CTE chiller

3. 2.147 265.950 ↓ 2.0 10 1

HashAggregate (cost=261,332.26..261,332.31 rows=5 width=7) (actual time=265.945..265.950 rows=10 loops=1)

  • Group Key: i.location_id
  • Buffers: shared hit=31,659 read=10,857
4. 3.766 263.803 ↑ 5.9 2,582 1

Hash Left Join (cost=81.17..261,294.09 rows=15,267 width=7) (actual time=181.746..263.803 rows=2,582 loops=1)

  • Hash Cond: (((cs.sort_group_id)::text = (pscg.sort_group_id)::text) AND (COALESCE(al.is_same_day_boning, 'N'::bpchar) = pscg.is_same_day_boning) AND (c.kill_date = pscg.kill_date))
  • Buffers: shared hit=31,659 read=10,857
5. 5.775 258.432 ↑ 5.9 2,582 1

Nested Loop Left Join (cost=1.28..260,870.68 rows=15,267 width=24) (actual time=180.118..258.432 rows=2,582 loops=1)

  • Buffers: shared hit=31,605 read=10,857
6. 9.132 242.329 ↑ 5.9 2,582 1

Nested Loop (cost=0.86..253,937.75 rows=15,267 width=29) (actual time=180.103..242.329 rows=2,582 loops=1)

  • Join Filter: ((i.create_date = cs.carcase_kill_date) AND ((i.create_location_id)::text = (cs.carcase_kill_location)::text) AND (i.carcase_number = cs.carcase_number) AND (i.carcase_side_number = cs.carcase_side_number))
  • Rows Removed by Join Filter: 2,582
  • Buffers: shared hit=21,265 read=10,857
7. 35.650 220.287 ↑ 9.3 2,582 1

Nested Loop (cost=0.43..238,196.51 rows=24,088 width=54) (actual time=180.080..220.287 rows=2,582 loops=1)

  • Buffers: shared hit=10,838 read=10,857
8. 137.249 137.249 ↓ 1.0 47,388 1

Seq Scan on inventory i (cost=0.00..16,431.58 rows=46,895 width=26) (actual time=85.543..137.249 rows=47,388 loops=1)

  • Filter: (status = 'A'::bpchar)
  • Rows Removed by Filter: 361,445
  • Buffers: shared hit=464 read=10,857
9. 47.388 47.388 ↓ 0.0 0 47,388

Index Scan using carcase_pkey on carcase c (cost=0.43..4.72 rows=1 width=28) (actual time=0.001..0.001 rows=0 loops=47,388)

  • Index Cond: ((kill_date = i.create_date) AND ((kill_location)::text = (i.create_location_id)::text) AND (number = i.carcase_number))
  • Buffers: shared hit=10,374
10. 12.910 12.910 ↓ 2.0 2 2,582

Index Scan using idx_cs_carcase on carcase_side cs (cost=0.43..0.63 rows=1 width=24) (actual time=0.004..0.005 rows=2 loops=2,582)

  • Index Cond: ((carcase_kill_date = c.kill_date) AND ((carcase_kill_location)::text = (c.kill_location)::text) AND (carcase_number = c.number))
  • Buffers: shared hit=10,427
11. 10.328 10.328 ↑ 1.0 1 2,582

Index Scan using advice_line_pkey on advice_line al (cost=0.42..0.44 rows=1 width=21) (actual time=0.003..0.004 rows=1 loops=2,582)

  • Index Cond: ((advice_number = c.advice_number) AND ((advice_season)::text = (c.advice_season)::text) AND (line = c.advice_line_line))
  • Buffers: shared hit=10,340
12. 0.058 1.605 ↑ 2.2 117 1

Hash (cost=75.45..75.45 rows=254 width=11) (actual time=1.605..1.605 rows=117 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
  • Buffers: shared hit=54
13. 0.118 1.547 ↑ 2.2 117 1

Nested Loop (cost=20.45..75.45 rows=254 width=11) (actual time=1.312..1.547 rows=117 loops=1)

  • Buffers: shared hit=54
14. 0.341 1.338 ↑ 4.1 13 1

Hash Join (cost=20.17..38.07 rows=53 width=4) (actual time=1.301..1.338 rows=13 loops=1)

  • Hash Cond: (psmg.production_schedule_spec_uid = pss.uid)
  • Buffers: shared hit=13
15. 0.301 0.301 ↑ 1.1 801 1

Seq Scan on production_schedule_marshalling_group psmg (cost=0.00..14.00 rows=900 width=8) (actual time=0.007..0.301 rows=801 loops=1)

  • Buffers: shared hit=5
16. 0.007 0.696 ↑ 4.1 13 1

Hash (cost=19.50..19.50 rows=53 width=4) (actual time=0.696..0.696 rows=13 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=8
17. 0.335 0.689 ↑ 4.1 13 1

Hash Join (cost=3.10..19.50 rows=53 width=4) (actual time=0.647..0.689 rows=13 loops=1)

  • Hash Cond: (pss.production_schedule_uid = ps.uid)
  • Buffers: shared hit=8
18. 0.329 0.329 ↓ 1.1 799 1

Seq Scan on production_schedule_spec pss (cost=0.00..13.18 rows=718 width=8) (actual time=0.005..0.329 rows=799 loops=1)

  • Buffers: shared hit=6
19. 0.007 0.025 ↑ 1.0 6 1

Hash (cost=3.03..3.03 rows=6 width=4) (actual time=0.025..0.025 rows=6 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=2
20. 0.018 0.018 ↑ 1.0 6 1

Seq Scan on production_schedule ps (cost=0.00..3.03 rows=6 width=4) (actual time=0.011..0.018 rows=6 loops=1)

  • Filter: (is_active = 'Y'::bpchar)
  • Rows Removed by Filter: 76
  • Buffers: shared hit=2
21. 0.091 0.091 ↓ 1.5 9 13

Index Scan using pscg_mayhave_production_schedule_marshalling_group_fkey_idx on production_schedule_carcase_group pscg (cost=0.28..0.65 rows=6 width=15) (actual time=0.003..0.007 rows=9 loops=13)

  • Index Cond: (production_schedule_marshalling_group_uid = psmg.uid)
  • Buffers: shared hit=41
22.          

CTE carccnt

23. 0.039 1,964.814 ↓ 2.0 2 1

HashAggregate (cost=2,970.91..2,970.92 rows=1 width=7) (actual time=1,964.813..1,964.814 rows=2 loops=1)

  • Group Key: i_1.location_id
  • Buffers: shared hit=219,023
24. 6.532 1,964.775 ↓ 20.0 20 1

Nested Loop (cost=2.68..2,970.91 rows=1 width=7) (actual time=6.435..1,964.775 rows=20 loops=1)

  • Join Filter: ((i_1.create_date = cs_1.carcase_kill_date) AND ((i_1.create_location_id)::text = (cs_1.carcase_kill_location)::text) AND (i_1.carcase_number = cs_1.carcase_number) AND (i_1.carcase_side_number = cs_1.carcase_side_number) AND ((pscg_1.sort_group_id)::text = (cs_1.sort_group_id)::text))
  • Rows Removed by Join Filter: 5,144
  • Buffers: shared hit=219,023
25. 3.964 1,942.751 ↓ 2,582.0 2,582 1

Nested Loop (cost=2.25..2,970.25 rows=1 width=50) (actual time=0.130..1,942.751 rows=2,582 loops=1)

  • Buffers: shared hit=208,596
26. 4.076 12.787 ↓ 1,500.0 1,500 1

Nested Loop Left Join (cost=1.82..2,961.63 rows=1 width=24) (actual time=0.092..12.787 rows=1,500 loops=1)

  • Filter: (pscg_1.is_same_day_boning = COALESCE(al_1.is_same_day_boning, 'N'::bpchar))
  • Buffers: shared hit=6,096
27. 1.400 2.711 ↓ 6.3 1,500 1

Nested Loop (cost=1.41..2,852.95 rows=238 width=39) (actual time=0.072..2.711 rows=1,500 loops=1)

  • Buffers: shared hit=90
28. 0.006 0.062 ↑ 1.0 1 1

Nested Loop (cost=0.98..25.57 rows=1 width=11) (actual time=0.052..0.062 rows=1 loops=1)

  • Buffers: shared hit=11
29. 0.006 0.048 ↑ 1.0 1 1

Nested Loop (cost=0.69..16.79 rows=1 width=4) (actual time=0.041..0.048 rows=1 loops=1)

  • Buffers: shared hit=8
30. 0.010 0.030 ↑ 1.0 1 1

Nested Loop (cost=0.55..16.60 rows=1 width=8) (actual time=0.026..0.030 rows=1 loops=1)

  • Buffers: shared hit=6
31. 0.013 0.013 ↑ 1.0 1 1

Index Scan using production_schedule_marshalling_group_pkey on production_schedule_marshalling_group psmg_1 (cost=0.28..8.29 rows=1 width=8) (actual time=0.012..0.013 rows=1 loops=1)

  • Index Cond: (uid = 991,278)
  • Buffers: shared hit=3
32. 0.007 0.007 ↑ 1.0 1 1

Index Scan using production_schedule_spec_pkey on production_schedule_spec pss_1 (cost=0.28..8.29 rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=1)

  • Index Cond: (uid = psmg_1.production_schedule_spec_uid)
  • Buffers: shared hit=3
33. 0.012 0.012 ↑ 1.0 1 1

Index Scan using production_schedule_pkey on production_schedule ps_1 (cost=0.14..0.18 rows=1 width=4) (actual time=0.010..0.012 rows=1 loops=1)

  • Index Cond: (uid = pss_1.production_schedule_uid)
  • Filter: (is_active = 'Y'::bpchar)
  • Buffers: shared hit=2
34. 0.008 0.008 ↑ 3.0 1 1

Index Scan using pscg_mayhave_production_schedule_marshalling_group_fkey_idx on production_schedule_carcase_group pscg_1 (cost=0.28..8.75 rows=3 width=15) (actual time=0.007..0.008 rows=1 loops=1)

  • Index Cond: (production_schedule_marshalling_group_uid = 991,278)
  • Buffers: shared hit=3
35. 1.249 1.249 ↓ 1.1 1,500 1

Index Scan using carcase_pkey on carcase c_1 (cost=0.43..2,813.23 rows=1,416 width=28) (actual time=0.015..1.249 rows=1,500 loops=1)

  • Index Cond: (kill_date = pscg_1.kill_date)
  • Buffers: shared hit=79
36. 6.000 6.000 ↑ 1.0 1 1,500

Index Scan using advice_line_pkey on advice_line al_1 (cost=0.42..0.44 rows=1 width=21) (actual time=0.004..0.004 rows=1 loops=1,500)

  • Index Cond: ((advice_number = c_1.advice_number) AND ((advice_season)::text = (c_1.advice_season)::text) AND (line = c_1.advice_line_line))
  • Buffers: shared hit=6,006
37. 1,926.000 1,926.000 ↓ 2.0 2 1,500

Index Scan using inventory_ukey on inventory i_1 (cost=0.42..8.62 rows=1 width=26) (actual time=0.765..1.284 rows=2 loops=1,500)

  • Index Cond: ((create_date = c_1.kill_date) AND ((create_location_id)::text = (c_1.kill_location)::text))
  • Filter: ((status = 'A'::bpchar) AND (c_1.number = carcase_number))
  • Rows Removed by Filter: 2,768
  • Buffers: shared hit=202,500
38. 15.492 15.492 ↓ 2.0 2 2,582

Index Scan using idx_cs_carcase on carcase_side cs_1 (cost=0.43..0.63 rows=1 width=24) (actual time=0.004..0.006 rows=2 loops=2,582)

  • Index Cond: ((carcase_kill_date = c_1.kill_date) AND ((carcase_kill_location)::text = (c_1.kill_location)::text) AND (carcase_number = c_1.number))
  • Buffers: shared hit=10,427
39. 0.028 266.000 ↓ 2.0 10 1

Sort (cost=0.16..0.17 rows=5 width=38) (actual time=265.994..266.000 rows=10 loops=1)

  • Sort Key: chiller.id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=31,659 read=10,857
40. 265.972 265.972 ↓ 2.0 10 1

CTE Scan on chiller (cost=0.00..0.10 rows=5 width=38) (actual time=265.950..265.972 rows=10 loops=1)

  • Buffers: shared hit=31,659 read=10,857
41. 0.021 1,964.842 ↓ 2.0 2 1

Sort (cost=0.03..0.04 rows=1 width=46) (actual time=1,964.841..1,964.842 rows=2 loops=1)

  • Sort Key: carccnt.id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=219,023
42. 1,964.821 1,964.821 ↓ 2.0 2 1

CTE Scan on carccnt (cost=0.00..0.02 rows=1 width=46) (actual time=1,964.818..1,964.821 rows=2 loops=1)

  • Buffers: shared hit=219,023
Planning time : 21.303 ms
Execution time : 2,231.234 ms