explain.depesz.com

PostgreSQL's explain analyze made readable

Result: d4xb

Settings
# exclusive inclusive rows x rows loops node
1. 5.062 1,569.493 ↓ 11,026.0 11,026 1

Unique (cost=26,255.96..26,256.01 rows=1 width=169) (actual time=1,562.607..1,569.493 rows=11,026 loops=1)

2. 44.446 1,564.431 ↓ 11,026.0 11,026 1

Sort (cost=26,255.96..26,255.96 rows=1 width=169) (actual time=1,562.607..1,564.431 rows=11,026 loops=1)

  • Sort Key: mrp_bom_line.id, mrp_bom_line_to_purchase_order_line.mrp_bom_line_id, (COALESCE(calc_bom_yield(mrp_bom_line.id), '0'::double precision)), mrp_bom.bom_type, (COALESCE(vendor_material.price, '0'::double precision)), (COALESCE(product_product.weight, 0.00000)), (COALESCE(vendor_material.currency_id, 0)), mrp_bom_line_to_purchase_order_line.legacy, ews_mrp_job_orders.name, (COALESCE(mrp_bom_line.product_qty, '0'::numeric)), (((COALESCE(get_total_customer_purchase_qty(mrp_bom_line.bom_id), '0'::bigint))::numeric * COALESCE(mrp_bom_line.product_qty, '0'::numeric))), (COALESCE(mrp_bom_line.stock_length, '0'::double precision)), (COALESCE(mrp_bom_line.stock_width, '0'::double precision)), (COALESCE(mrp_bom_line.bom_length, '0'::double precision)), (COALESCE(mrp_bom_line.bom_width, '0'::double precision)), (COALESCE(res_partner.name, 'NULL'::character varying)), product_uom.name, product_formula_type.name, (COALESCE(mrp_bom_line.bom_detail_id, ''::character varying)), (COALESCE(mrp_bom_line.material_otp, 'NULL'::character varying)), ews_mrp_release_orders.id
  • Sort Method: quicksort Memory: 3271kB
3. 1,201.583 1,519.985 ↓ 11,026.0 11,026 1

Nested Loop Left Join (cost=23,252.95..26,255.95 rows=1 width=169) (actual time=56.081..1,519.985 rows=11,026 loops=1)

  • Join Filter: (vendor_material.vendor_id = res_partner.id)
  • Rows Removed by Join Filter: 56004
4. 6.485 263.277 ↓ 11,025.0 11,025 1

Nested Loop Left Join (cost=23,252.67..26,255.06 rows=1 width=165) (actual time=55.683..263.277 rows=11,025 loops=1)

5. 14.094 234.742 ↓ 11,025.0 11,025 1

Nested Loop Left Join (cost=23,252.39..26,254.75 rows=1 width=161) (actual time=55.681..234.742 rows=11,025 loops=1)

6. 3.348 209.623 ↓ 11,025.0 11,025 1

Nested Loop Left Join (cost=23,252.25..26,254.59 rows=1 width=133) (actual time=55.676..209.623 rows=11,025 loops=1)

7. 10.150 184.225 ↓ 11,025.0 11,025 1

Nested Loop Left Join (cost=23,252.12..26,254.43 rows=1 width=132) (actual time=55.671..184.225 rows=11,025 loops=1)

8. 12.719 152.025 ↓ 11,025.0 11,025 1

Nested Loop Left Join (cost=23,251.83..26,254.11 rows=1 width=114) (actual time=55.664..152.025 rows=11,025 loops=1)

9. 3.755 106.231 ↓ 11,025.0 11,025 1

Nested Loop Left Join (cost=23,251.55..26,253.80 rows=1 width=112) (actual time=55.652..106.231 rows=11,025 loops=1)

10. 21.679 69.401 ↓ 11,025.0 11,025 1

Hash Right Join (cost=23,251.27..26,253.28 rows=1 width=111) (actual time=55.641..69.401 rows=11,025 loops=1)

  • Hash Cond: (mrp_bom_line_to_purchase_order_line.mrp_bom_line_id = mrp_bom_line.id)
  • Filter: ((mrp_bom_line_to_purchase_order_line.mrp_bom_line_id IS NULL) OR ((NOT mrp_bom_line_to_purchase_order_line.legacy) AND (get_total_customer_purchase_qty(mrp_bom_line.bom_id) > mrp_bom_line_to_purchase_order_line.po_qty)))
  • Rows Removed by Filter: 13682
11. 7.234 7.234 ↑ 1.0 46,633 1

Seq Scan on mrp_bom_line_to_purchase_order_line (cost=0.00..1,060.33 rows=46,633 width=9) (actual time=0.277..7.234 rows=46,633 loops=1)

12. 9.487 40.488 ↑ 1.3 21,468 1

Hash (cost=22,447.13..22,447.13 rows=27,611 width=106) (actual time=40.488..40.488 rows=21,468 loops=1)

  • Buckets: 32768 Batches: 2 Memory Usage: 1767kB
13. 5.710 31.001 ↑ 1.3 21,468 1

Nested Loop (cost=0.42..22,447.13 rows=27,611 width=106) (actual time=0.059..31.001 rows=21,468 loops=1)

14. 2.539 2.539 ↑ 1.0 632 1

Seq Scan on ews_mrp_release_orders (cost=0.00..1,377.94 rows=632 width=12) (actual time=0.046..2.539 rows=632 loops=1)

  • Filter: (release_state_id = ANY ('{3,4}'::integer[]))
  • Rows Removed by Filter: 5523
15. 22.752 22.752 ↑ 1.3 34 632

Index Scan using mrp_bom_line_bom_id_index on mrp_bom_line (cost=0.42..32.90 rows=44 width=98) (actual time=0.005..0.036 rows=34 loops=632)

  • Index Cond: (bom_id = ews_mrp_release_orders.bom_id)
  • Filter: active
  • Rows Removed by Filter: 1
16. 33.075 33.075 ↑ 1.0 1 11,025

Index Scan using ews_mrp_job_orders_pkey on ews_mrp_job_orders (cost=0.28..0.50 rows=1 width=9) (actual time=0.002..0.003 rows=1 loops=11,025)

  • Index Cond: (id = ews_mrp_release_orders.release_order_id)
17. 33.075 33.075 ↑ 1.0 1 11,025

Index Scan using mrp_bom_pkey on mrp_bom (cost=0.29..0.31 rows=1 width=6) (actual time=0.003..0.003 rows=1 loops=11,025)

  • Index Cond: (id = mrp_bom_line.bom_id)
18. 22.050 22.050 ↑ 1.0 1 11,025

Index Scan using res_partner_pkey on res_partner (cost=0.29..0.31 rows=1 width=22) (actual time=0.002..0.002 rows=1 loops=11,025)

  • Index Cond: (id = mrp_bom_line.vendor)
19. 22.050 22.050 ↑ 1.0 1 11,025

Index Scan using product_uom_pkey on product_uom (cost=0.14..0.16 rows=1 width=9) (actual time=0.001..0.002 rows=1 loops=11,025)

  • Index Cond: (id = mrp_bom_line.product_uom)
20. 11.025 11.025 ↑ 1.0 1 11,025

Index Scan using product_formula_type_pkey on product_formula_type (cost=0.13..0.15 rows=1 width=36) (actual time=0.001..0.001 rows=1 loops=11,025)

  • Index Cond: (id = mrp_bom_line.formula_id)
21. 22.050 22.050 ↑ 1.0 1 11,025

Index Scan using product_product_pkey on product_product (cost=0.28..0.30 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=11,025)

  • Index Cond: (id = mrp_bom_line.product_id)
22. 55.125 55.125 ↓ 3.0 6 11,025

Index Scan using index_vendor_material_product_id on vendor_material (cost=0.29..0.35 rows=2 width=20) (actual time=0.002..0.005 rows=6 loops=11,025)

  • Index Cond: (product_product.id = product_id)
Planning time : 3.101 ms
Execution time : 1,570.381 ms