explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TheY

Settings
# exclusive inclusive rows x rows loops node
1. 7.893 1,061.747 ↑ 1.5 552 1

Sort (cost=125,572.63..125,574.73 rows=840 width=891) (actual time=1,061.480..1,061.747 rows=552 loops=1)

  • Sort Key: ps.sequence, ps.number, pbs.sequence, (COALESCE(parent.sequence, psms.sequence)), pspg.sequence NULLS FIRST
  • Sort Method: quicksort Memory: 261kB
  • Buffers: shared hit=136,593
2.          

CTE xx

3. 0.017 0.169 ↓ 3.0 6 1

Unique (cost=18.51..18.53 rows=2 width=129) (actual time=0.147..0.169 rows=6 loops=1)

  • Buffers: shared hit=48
4. 0.027 0.152 ↓ 4.5 9 1

Sort (cost=18.51..18.52 rows=2 width=129) (actual time=0.146..0.152 rows=9 loops=1)

  • Sort Key: pss.processing_spec_site_id, pss.processing_spec_number, psmg.group_name
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=48
5. 0.006 0.125 ↓ 4.5 9 1

Nested Loop (cost=0.69..18.50 rows=2 width=129) (actual time=0.048..0.125 rows=9 loops=1)

  • Buffers: shared hit=48
6. 0.010 0.092 ↑ 1.1 9 1

Nested Loop (cost=0.55..16.33 rows=10 width=133) (actual time=0.041..0.092 rows=9 loops=1)

  • Buffers: shared hit=33
7. 0.012 0.055 ↑ 1.1 9 1

Nested Loop (cost=0.28..12.77 rows=10 width=15) (actual time=0.034..0.055 rows=9 loops=1)

  • Buffers: shared hit=6
8. 0.003 0.031 ↑ 1.0 1 1

Nested Loop (cost=0.00..3.94 rows=1 width=4) (actual time=0.024..0.031 rows=1 loops=1)

  • Join Filter: (psh.uid = sched.production_schedule_header_uid)
  • Buffers: shared hit=3
9. 0.012 0.012 ↑ 1.0 1 1

Seq Scan on production_schedule_header psh (cost=0.00..1.01 rows=1 width=4) (actual time=0.011..0.012 rows=1 loops=1)

  • Filter: ((boning_plan_snapshot_uid IS NULL) AND ((site_id)::text = 'BEENLH'::text) AND (type = 'C'::bpchar))
  • Buffers: shared hit=1
10. 0.016 0.016 ↑ 1.0 1 1

Seq Scan on production_schedule sched (cost=0.00..2.92 rows=1 width=8) (actual time=0.010..0.016 rows=1 loops=1)

  • Filter: ((level_date = '2020-09-16'::date) AND (is_active = 'Y'::bpchar))
  • Rows Removed by Filter: 60
  • Buffers: shared hit=2
11. 0.012 0.012 ↑ 1.2 9 1

Index Scan using pss_musthave_production_schedule_fkey_idx on production_schedule_spec pss (cost=0.28..8.72 rows=11 width=19) (actual time=0.007..0.012 rows=9 loops=1)

  • Index Cond: (production_schedule_uid = sched.uid)
  • Buffers: shared hit=3
12. 0.027 0.027 ↑ 1.0 1 9

Index Scan using psmg_musthave_production_schedule_spec_fkey_idx on production_schedule_marshalling_group psmg (cost=0.28..0.35 rows=1 width=126) (actual time=0.002..0.003 rows=1 loops=9)

  • Index Cond: (production_schedule_spec_uid = pss.uid)
  • Buffers: shared hit=27
13. 0.027 0.027 ↑ 2.0 1 9

Index Only Scan using psmgs_musthave_production_schedule_marshalling_group_fkey_idx on ps_marshalling_group_schedule psmgs (cost=0.14..0.20 rows=2 width=4) (actual time=0.002..0.003 rows=1 loops=9)

  • Index Cond: (production_schedule_marshalling_group_uid = psmg.uid)
  • Heap Fetches: 9
  • Buffers: shared hit=15
14. 12.174 1,053.854 ↑ 1.5 552 1

Nested Loop Left Join (cost=16.02..125,513.30 rows=840 width=891) (actual time=3.459..1,053.854 rows=552 loops=1)

  • Join Filter: (SubPlan 3)
  • Rows Removed by Join Filter: 2,196
  • Buffers: shared hit=136,593
15. 1.679 59.780 ↓ 39.3 550 1

Nested Loop Left Join (cost=16.02..245.59 rows=14 width=375) (actual time=0.575..59.780 rows=550 loops=1)

  • Buffers: shared hit=12,968
16. 1.740 55.901 ↓ 39.3 550 1

Nested Loop Left Join (cost=15.75..241.22 rows=14 width=347) (actual time=0.569..55.901 rows=550 loops=1)

  • Buffers: shared hit=11,653
17. 1.367 51.411 ↓ 39.3 550 1

Nested Loop Left Join (cost=15.61..238.72 rows=14 width=350) (actual time=0.559..51.411 rows=550 loops=1)

  • Buffers: shared hit=10,645
18. 1.618 46.744 ↓ 39.3 550 1

Nested Loop Left Join (cost=15.33..232.79 rows=14 width=333) (actual time=0.551..46.744 rows=550 loops=1)

  • Buffers: shared hit=9,331
19. 1.625 42.926 ↓ 39.3 550 1

Hash Left Join (cost=15.19..228.49 rows=14 width=333) (actual time=0.545..42.926 rows=550 loops=1)

  • Hash Cond: (psms.processing_spec_number = ps.number)
  • Buffers: shared hit=8,231
20. 1.891 41.086 ↓ 39.3 550 1

Nested Loop Left Join (cost=2.25..215.36 rows=14 width=302) (actual time=0.323..41.086 rows=550 loops=1)

  • Buffers: shared hit=8,222
21. 1.678 30.395 ↓ 39.3 550 1

Nested Loop Left Join (cost=1.68..152.76 rows=14 width=238) (actual time=0.295..30.395 rows=550 loops=1)

  • Buffers: shared hit=5,595
22. 2.009 25.417 ↓ 39.3 550 1

Nested Loop Left Join (cost=1.39..145.32 rows=14 width=241) (actual time=0.280..25.417 rows=550 loops=1)

  • Join Filter: ((pol.site_id)::text = (psms.processing_spec_site_id)::text)
  • Buffers: shared hit=4,284
23. 1.303 20.108 ↓ 39.3 550 1

Nested Loop Left Join (cost=1.10..95.97 rows=14 width=195) (actual time=0.260..20.108 rows=550 loops=1)

  • Buffers: shared hit=2,951
24. 1.437 16.605 ↓ 39.3 550 1

Nested Loop Left Join (cost=0.83..91.34 rows=14 width=186) (actual time=0.254..16.605 rows=550 loops=1)

  • Buffers: shared hit=1,637
25. 4.107 9.668 ↓ 39.3 550 1

Nested Loop Left Join (cost=0.56..85.05 rows=14 width=175) (actual time=0.248..9.668 rows=550 loops=1)

  • Join Filter: (pspg.child_processing_spec_make_sequence_uid = psms.uid)
  • Rows Removed by Join Filter: 5,490
  • Buffers: shared hit=325
26. 0.679 2.811 ↓ 39.3 550 1

Nested Loop (cost=0.28..67.00 rows=14 width=163) (actual time=0.179..2.811 rows=550 loops=1)

  • Buffers: shared hit=294
27. 0.182 0.182 ↓ 3.0 6 1

CTE Scan on xx (cost=0.00..0.04 rows=2 width=160) (actual time=0.148..0.182 rows=6 loops=1)

  • Buffers: shared hit=48
28. 1.950 1.950 ↓ 6.6 92 6

Index Scan using psm_sequence_musthave_processing_spec_fkey_idx on processing_spec_make_sequence psms (cost=0.28..33.34 rows=14 width=45) (actual time=0.018..0.325 rows=92 loops=6)

  • Index Cond: (((processing_spec_site_id)::text = (xx.processing_spec_site_id)::text) AND (processing_spec_number = xx.processing_spec_number))
  • Filter: (status = 'A'::bpchar)
  • Rows Removed by Filter: 26
  • Buffers: shared hit=246
29. 2.696 2.750 ↓ 5.0 10 550

Materialize (cost=0.28..17.64 rows=2 width=16) (actual time=0.001..0.005 rows=10 loops=550)

  • Buffers: shared hit=31
30. 0.017 0.054 ↓ 5.0 10 1

Nested Loop Left Join (cost=0.28..17.63 rows=2 width=16) (actual time=0.010..0.054 rows=10 loops=1)

  • Buffers: shared hit=31
31. 0.007 0.007 ↓ 5.0 10 1

Seq Scan on processing_spec_packing_group pspg (cost=0.00..1.02 rows=2 width=12) (actual time=0.002..0.007 rows=10 loops=1)

  • Buffers: shared hit=1
32. 0.030 0.030 ↑ 1.0 1 10

Index Scan using processing_spec_make_sequence_pkey on processing_spec_make_sequence parent (cost=0.28..8.29 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=10)

  • Index Cond: (pspg.parent_processing_spec_make_sequence_uid = uid)
  • Buffers: shared hit=30
33. 5.500 5.500 ↑ 1.0 1 550

Index Scan using processing_spec_product_pkey on processing_spec_product psp (cost=0.28..0.44 rows=1 width=15) (actual time=0.009..0.010 rows=1 loops=550)

  • Index Cond: (uid = psms.to_processing_spec_product_uid)
  • Buffers: shared hit=1,312
34. 2.200 2.200 ↑ 1.0 1 550

Index Scan using production_order_product_make_pkey on production_order_product_make popm (cost=0.27..0.32 rows=1 width=13) (actual time=0.003..0.004 rows=1 loops=550)

  • Index Cond: (uid = psp.production_order_product_make_uid)
  • Buffers: shared hit=1,314
35. 3.300 3.300 ↑ 1.0 1 550

Index Scan using production_order_line00 on production_order_line pol (cost=0.29..3.51 rows=1 width=53) (actual time=0.005..0.006 rows=1 loops=550)

  • Index Cond: ((production_order_number = popm.production_order_number) AND ((product_id)::text = (popm.main_product_id)::text))
  • Buffers: shared hit=1,333
36. 3.300 3.300 ↑ 1.0 1 550

Index Scan using production_order_product00_idx on production_order_product pop (cost=0.29..0.52 rows=1 width=15) (actual time=0.005..0.006 rows=1 loops=550)

  • Index Cond: ((pol.production_order_number = production_order_number) AND ((pol.product_id)::text = (product_id)::text))
  • Buffers: shared hit=1,311
37. 2.328 8.800 ↑ 1.0 1 550

Nested Loop (cost=0.57..4.46 rows=1 width=69) (actual time=0.014..0.016 rows=1 loops=550)

  • Buffers: shared hit=2,627
38. 3.850 3.850 ↑ 1.0 1 550

Index Scan using product_pkey on product p (cost=0.29..3.85 rows=1 width=75) (actual time=0.006..0.007 rows=1 loops=550)

  • Index Cond: ((id)::text = (psp.product_id)::text)
  • Buffers: shared hit=1,313
39. 2.622 2.622 ↑ 1.0 1 437

Index Scan using product_revision_pkey on product_revision pr (cost=0.29..0.60 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=437)

  • Index Cond: (uid = p.latest_published_product_revision_uid)
  • Buffers: shared hit=1,314
40. 0.088 0.215 ↑ 1.0 175 1

Hash (cost=10.75..10.75 rows=175 width=31) (actual time=0.215..0.215 rows=175 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 19kB
  • Buffers: shared hit=9
41. 0.127 0.127 ↑ 1.0 175 1

Seq Scan on processing_spec ps (cost=0.00..10.75 rows=175 width=31) (actual time=0.005..0.127 rows=175 loops=1)

  • Buffers: shared hit=9
42. 2.200 2.200 ↑ 1.0 1 550

Index Scan using processing_spec_revision_pkey on processing_spec_revision psr (cost=0.14..0.30 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=550)

  • Index Cond: (uid = ps.latest_published_processing_spec_revision_uid)
  • Buffers: shared hit=1,100
43. 3.300 3.300 ↑ 1.0 1 550

Index Scan using cut_pkey on cut to_cut (cost=0.28..0.41 rows=1 width=23) (actual time=0.005..0.006 rows=1 loops=550)

  • Index Cond: ((id)::text = (psms.to_product_cut_id)::text)
  • Buffers: shared hit=1,314
44. 2.750 2.750 ↑ 1.0 1 550

Index Scan using primal_boning_sequence_pkey on primal_boning_sequence pbs (cost=0.14..0.17 rows=1 width=80) (actual time=0.004..0.005 rows=1 loops=550)

  • Index Cond: (((site_id)::text = (psms.processing_spec_site_id)::text) AND ((primal_cut_id)::text = (psms.primal_cut_id)::text))
  • Buffers: shared hit=1,008
45. 2.200 2.200 ↑ 1.0 1 550

Index Scan using pk_material_item on material_item m1 (cost=0.28..0.30 rows=1 width=33) (actual time=0.004..0.004 rows=1 loops=550)

  • Index Cond: ((id)::text = (p.cut_wrap_1)::text)
  • Buffers: shared hit=1,315
46. 1.095 1.100 ↑ 30.0 4 550

Materialize (cost=0.00..11.80 rows=120 width=520) (actual time=0.001..0.002 rows=4 loops=550)

  • Buffers: shared hit=1
47. 0.005 0.005 ↑ 30.0 4 1

Seq Scan on note (cost=0.00..11.20 rows=120 width=520) (actual time=0.003..0.005 rows=4 loops=1)

  • Buffers: shared hit=1
48.          

SubPlan (for Nested Loop Left Join)

49. 952.200 952.200 ↓ 0.0 0 552

Function Scan on rme_validate_raw_material_eligibility_relationship (cost=0.25..12.75 rows=5 width=0) (actual time=1.725..1.725 rows=0 loops=552)

  • Filter: ((match_value)::text = 'M'::text)
  • Rows Removed by Filter: 17
  • Buffers: shared hit=117,924
50. 2.200 28.600 ↓ 0.0 0 2,200

HashAggregate (cost=72.98..73.22 rows=24 width=4) (actual time=0.013..0.013 rows=0 loops=2,200)

  • Group Key: psmsn.note_uid
  • Buffers: shared hit=5,700
51. 13.200 26.400 ↓ 0.0 0 2,200

Append (cost=0.00..72.92 rows=24 width=4) (actual time=0.012..0.012 rows=0 loops=2,200)

  • Buffers: shared hit=5,700
52. 4.400 4.400 ↓ 0.0 0 2,200

Seq Scan on processing_spec_make_sequence_note psmsn (cost=0.00..31.25 rows=8 width=4) (actual time=0.002..0.002 rows=0 loops=2,200)

  • Filter: (processing_spec_make_sequence_uid = psms.uid)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=2,200
53. 2.200 4.400 ↓ 0.0 0 2,200

Bitmap Heap Scan on processing_spec_product_note pspn (cost=4.21..14.37 rows=8 width=4) (actual time=0.002..0.002 rows=0 loops=2,200)

  • Recheck Cond: (processing_spec_product_uid = psp.uid)
  • Heap Blocks: exact=4
  • Buffers: shared hit=1,752
54. 2.200 2.200 ↓ 0.0 0 2,200

Bitmap Index Scan on processing_spec_product_note_pkey (cost=0.00..4.21 rows=8 width=0) (actual time=0.001..0.001 rows=0 loops=2,200)

  • Index Cond: (processing_spec_product_uid = psp.uid)
  • Buffers: shared hit=1,748
55. 2.200 4.400 ↓ 0.0 0 2,200

Bitmap Heap Scan on production_order_product_make_note popmn (cost=16.90..27.06 rows=8 width=4) (actual time=0.002..0.002 rows=0 loops=2,200)

  • Recheck Cond: (production_order_product_uid = popm.uid)
  • Buffers: shared hit=1,748
56. 2.200 2.200 ↓ 0.0 0 2,200

Bitmap Index Scan on production_order_product_make_note_pkey (cost=0.00..16.90 rows=8 width=0) (actual time=0.001..0.001 rows=0 loops=2,200)

  • Index Cond: (production_order_product_uid = popm.uid)
  • Buffers: shared hit=1,748
Planning time : 8.315 ms
Execution time : 1,062.774 ms