explain.depesz.com

PostgreSQL's explain analyze made readable

Result: i4ff

Settings
# exclusive inclusive rows x rows loops node
1. 8.376 1,151.277 ↓ 1.7 547 1

Nested Loop Left Join (cost=704.73..40,542.16 rows=323 width=166) (actual time=69.715..1,151.277 rows=547 loops=1)

  • Buffers: shared hit=128,409 read=250
2.          

CTE active_production_schedule

3. 0.046 2.226 ↑ 1.0 9 1

Nested Loop (cost=31.13..34.66 rows=9 width=290) (actual time=2.168..2.226 rows=9 loops=1)

  • Join Filter: (ps_1.production_schedule_header_uid = psh.uid)
  • Buffers: shared hit=13
4. 0.027 0.027 ↑ 1.0 1 1

Seq Scan on production_schedule_header psh (cost=0.00..1.01 rows=1 width=35) (actual time=0.026..0.027 rows=1 loops=1)

  • Filter: (((site_id)::text = 'BEENLH'::text) AND (type = 'C'::bpchar))
  • Buffers: shared hit=1
5. 0.152 2.153 ↑ 1.0 9 1

Hash Join (cost=31.13..33.54 rows=9 width=255) (actual time=2.135..2.153 rows=9 loops=1)

  • Hash Cond: (psmgs.production_schedule_marshalling_group_uid = psmg.uid)
  • Buffers: shared hit=12
6. 0.089 0.089 ↓ 1.2 116 1

Seq Scan on ps_marshalling_group_schedule psmgs (cost=0.00..1.96 rows=96 width=31) (actual time=0.009..0.089 rows=116 loops=1)

  • Buffers: shared hit=1
7. 0.038 1.912 ↑ 4.5 13 1

Hash (cost=30.39..30.39 rows=59 width=224) (actual time=1.912..1.912 rows=13 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=11
8. 0.500 1.874 ↑ 4.5 13 1

Hash Join (cost=17.53..30.39 rows=59 width=224) (actual time=1.794..1.874 rows=13 loops=1)

  • Hash Cond: (psmg.production_schedule_spec_uid = pss.uid)
  • Buffers: shared hit=11
9. 0.565 0.565 ↓ 1.1 635 1

Seq Scan on production_schedule_marshalling_group psmg (cost=0.00..10.01 rows=601 width=132) (actual time=0.011..0.565 rows=635 loops=1)

  • Buffers: shared hit=4
10. 0.022 0.809 ↑ 4.6 13 1

Hash (cost=16.78..16.78 rows=60 width=92) (actual time=0.809..0.809 rows=13 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=7
11. 0.422 0.787 ↑ 4.6 13 1

Hash Join (cost=2.84..16.78 rows=60 width=92) (actual time=0.743..0.787 rows=13 loops=1)

  • Hash Cond: (pss.production_schedule_uid = ps_1.uid)
  • Buffers: shared hit=7
12. 0.326 0.326 ↓ 1.0 633 1

Seq Scan on production_schedule_spec pss (cost=0.00..11.07 rows=607 width=74) (actual time=0.006..0.326 rows=633 loops=1)

  • Buffers: shared hit=5
13. 0.013 0.039 ↑ 1.0 6 1

Hash (cost=2.76..2.76 rows=6 width=18) (actual time=0.039..0.039 rows=6 loops=1)

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

Seq Scan on production_schedule ps_1 (cost=0.00..2.76 rows=6 width=18) (actual time=0.010..0.026 rows=6 loops=1)

  • Filter: (is_active = 'Y'::bpchar)
  • Rows Removed by Filter: 55
  • Buffers: shared hit=2
15. 1.489 38.508 ↓ 1.7 547 1

Nested Loop Left Join (cost=669.49..981.03 rows=323 width=110) (actual time=25.331..38.508 rows=547 loops=1)

  • Buffers: shared hit=1,578 read=43
16. 2.035 31.549 ↓ 1.7 547 1

Hash Left Join (cost=669.20..809.33 rows=323 width=113) (actual time=25.274..31.549 rows=547 loops=1)

  • Hash Cond: (((psms.processing_spec_site_id)::text = (pol.site_id)::text) AND (popm.production_order_number = pol.production_order_number) AND ((popm.main_product_id)::text = (pol.product_id)::text))
  • Buffers: shared hit=310
17. 1.005 12.085 ↓ 1.7 547 1

Hash Left Join (cost=161.23..220.19 rows=323 width=74) (actual time=7.675..12.085 rows=547 loops=1)

  • Hash Cond: (psp.production_order_product_make_uid = popm.uid)
  • Buffers: shared hit=102
18. 1.792 10.653 ↓ 1.7 547 1

Hash Right Join (cost=145.32..199.85 rows=323 width=65) (actual time=7.223..10.653 rows=547 loops=1)

  • Hash Cond: (psp.uid = psms.to_processing_spec_product_uid)
  • Buffers: shared hit=93
19. 1.777 1.777 ↑ 1.1 889 1

Seq Scan on processing_spec_product psp (cost=0.00..49.38 rows=958 width=26) (actual time=0.010..1.777 rows=889 loops=1)

  • Filter: (status = 'A'::bpchar)
  • Rows Removed by Filter: 928
  • Buffers: shared hit=27
20. 0.669 7.084 ↓ 1.7 547 1

Hash (cost=141.28..141.28 rows=323 width=43) (actual time=7.084..7.084 rows=547 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 49kB
  • Buffers: shared hit=66
21. 1.017 6.415 ↓ 1.7 547 1

Hash Join (cost=58.14..141.28 rows=323 width=43) (actual time=3.256..6.415 rows=547 loops=1)

  • Hash Cond: (((psms.processing_spec_site_id)::text = (psr.processing_spec_site_id)::text) AND (psms.processing_spec_number = psr.processing_spec_number))
  • Buffers: shared hit=66
22. 2.267 2.267 ↑ 1.0 1,054 1

Seq Scan on processing_spec_make_sequence psms (cost=0.00..66.70 rows=1,057 width=43) (actual time=0.074..2.267 rows=1,054 loops=1)

  • Filter: ((make_type <> 'G'::bpchar) AND (status = 'A'::bpchar))
  • Rows Removed by Filter: 947
  • Buffers: shared hit=37
23. 0.027 3.131 ↑ 5.0 6 1

Hash (cost=57.69..57.69 rows=30 width=15) (actual time=3.131..3.131 rows=6 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=26
24. 0.069 3.104 ↑ 5.0 6 1

Hash Join (cost=6.50..57.69 rows=30 width=15) (actual time=2.695..3.104 rows=6 loops=1)

  • Hash Cond: (ps.latest_published_processing_spec_revision_uid = psr.uid)
  • Buffers: shared hit=26
25. 0.457 2.728 ↑ 14.7 6 1

Seq Scan on processing_spec ps (cost=0.00..50.56 rows=88 width=4) (actual time=2.334..2.728 rows=6 loops=1)

  • Filter: (((site_id)::text = 'BEENLH'::text) AND (alternatives: SubPlan 10 or hashed SubPlan 11))
  • Rows Removed by Filter: 169
  • Buffers: shared hit=22
26.          

SubPlan (for Seq Scan)

27. 0.000 0.000 ↓ 0.0 0

CTE Scan on active_production_schedule aps (cost=0.00..0.23 rows=1 width=0) (never executed)

  • Filter: ((level_date = '2020-09-16'::date) AND (processing_spec_number = ps.number))
28. 2.271 2.271 ↓ 9.0 9 1

CTE Scan on active_production_schedule aps_1 (cost=0.00..0.20 rows=1 width=4) (actual time=2.184..2.271 rows=9 loops=1)

  • Filter: (level_date = '2020-09-16'::date)
  • Buffers: shared hit=13
29. 0.127 0.307 ↓ 1.1 127 1

Hash (cost=5.11..5.11 rows=111 width=19) (actual time=0.307..0.307 rows=127 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
  • Buffers: shared hit=4
30. 0.180 0.180 ↓ 1.1 127 1

Seq Scan on processing_spec_revision psr (cost=0.00..5.11 rows=111 width=19) (actual time=0.017..0.180 rows=127 loops=1)

  • Buffers: shared hit=4
31. 0.156 0.427 ↓ 1.0 277 1

Hash (cost=12.46..12.46 rows=276 width=13) (actual time=0.427..0.427 rows=277 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 21kB
  • Buffers: shared hit=9
32. 0.271 0.271 ↓ 1.0 277 1

Seq Scan on production_order_product_make popm (cost=0.00..12.46 rows=276 width=13) (actual time=0.014..0.271 rows=277 loops=1)

  • Filter: (status = 'A'::bpchar)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=9
33. 8.885 17.429 ↑ 1.0 10,771 1

Hash (cost=317.08..317.08 rows=10,908 width=53) (actual time=17.429..17.429 rows=10,771 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 1,068kB
  • Buffers: shared hit=208
34. 8.544 8.544 ↑ 1.0 10,771 1

Seq Scan on production_order_line pol (cost=0.00..317.08 rows=10,908 width=53) (actual time=0.011..8.544 rows=10,771 loops=1)

  • Buffers: shared hit=208
35. 5.470 5.470 ↑ 1.0 1 547

Index Scan using production_order_product00_idx on production_order_product pop (cost=0.29..0.52 rows=1 width=15) (actual time=0.009..0.010 rows=1 loops=547)

  • Index Cond: ((production_order_number = pol.production_order_number) AND ((product_id)::text = (pol.product_id)::text))
  • Buffers: shared hit=1,268 read=43
36. 2.194 12.034 ↑ 1.0 1 547

Nested Loop (cost=0.57..5.35 rows=1 width=61) (actual time=0.019..0.022 rows=1 loops=547)

  • Buffers: shared hit=2,580 read=47
37. 5.470 5.470 ↑ 1.0 1 547

Index Scan using product_pkey on product p (cost=0.29..4.74 rows=1 width=61) (actual time=0.009..0.010 rows=1 loops=547)

  • Index Cond: ((id)::text = (psp.product_id)::text)
  • Buffers: shared hit=1,297 read=16
38. 4.370 4.370 ↑ 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.009..0.010 rows=1 loops=437)

  • Index Cond: (uid = p.latest_published_product_revision_uid)
  • Buffers: shared hit=1,283 read=31
39.          

SubPlan (for Nested Loop Left Join)

40. 2.735 2.735 ↑ 1.0 1 547

Index Scan using pk_material_item on material_item mi (cost=0.28..8.29 rows=1 width=6) (actual time=0.005..0.005 rows=1 loops=547)

  • Index Cond: ((id)::text = (p.outer_type)::text)
  • Buffers: shared hit=1,305 read=6
41. 2.188 2.188 ↑ 1.0 1 547

Index Scan using pk_material_item on material_item mi_1 (cost=0.28..8.29 rows=1 width=28) (actual time=0.004..0.004 rows=1 loops=547)

  • Index Cond: ((id)::text = (p.cut_wrap_1)::text)
  • Buffers: shared hit=1,308 read=7
42. 1.641 1.641 ↑ 1.0 1 547

Index Scan using pk_material_item on material_item mi_2 (cost=0.28..8.29 rows=1 width=6) (actual time=0.002..0.003 rows=1 loops=547)

  • Index Cond: ((id)::text = (p.cut_wrap_1)::text)
  • Buffers: shared hit=1,315
43. 1,072.120 1,072.120 ↓ 0.0 0 547

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

  • Filter: ((match_value)::text = 'M'::text)
  • Rows Removed by Filter: 18
  • Buffers: shared hit=118,793 read=145
44. 2.735 13.675 ↑ 1.0 1 547

Aggregate (cost=89.34..89.35 rows=1 width=516) (actual time=0.024..0.025 rows=1 loops=547)

  • Buffers: shared hit=1,530 read=2
45. 7.111 10.940 ↓ 0.0 0 547

Seq Scan on note (cost=76.93..89.33 rows=1 width=516) (actual time=0.020..0.020 rows=0 loops=547)

  • Filter: ((note_type = 'M'::bpchar) AND ((hashed SubPlan 6) OR (hashed SubPlan 7) OR (hashed SubPlan 8)))
  • Rows Removed by Filter: 4
  • Buffers: shared hit=1,530 read=2
46.          

SubPlan (for Seq Scan)

47. 1.094 1.094 ↓ 0.0 0 547

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=547)

  • Filter: (processing_spec_make_sequence_uid = psms.uid)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=547
48. 1.094 2.188 ↓ 0.0 0 547

Bitmap Heap Scan on processing_spec_product_note psmsn_1 (cost=4.21..14.37 rows=8 width=4) (actual time=0.004..0.004 rows=0 loops=547)

  • Recheck Cond: (processing_spec_product_uid = psp.uid)
  • Heap Blocks: exact=1
  • Buffers: shared hit=437 read=1
49. 1.094 1.094 ↓ 0.0 0 547

Bitmap Index Scan on processing_spec_product_note_pkey (cost=0.00..4.21 rows=8 width=0) (actual time=0.002..0.002 rows=0 loops=547)

  • Index Cond: (processing_spec_product_uid = psp.uid)
  • Buffers: shared hit=437
50. 0.547 0.547 ↓ 0.0 0 547

Seq Scan on production_order_product_make_note popmn (cost=0.00..31.25 rows=8 width=4) (actual time=0.001..0.001 rows=0 loops=547)

  • Filter: (production_order_product_uid = popm.uid)