explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZhWd

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Unique (cost=5,792,169.28..5,802,707.68 rows=131,730 width=290) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Sort (cost=5,792,169.28..5,792,498.61 rows=131,730 width=290) (actual rows= loops=)

  • Sort Key: p1."PO Hdr Purchasing Document Number", p1."PO Item Number", p1.po_header_title_order_type, p1."PO Hdr Doc Date", p1."PO Hdr Country Name", p1."PO Hdr Country Key", p1."Supplier Name", p1."PO Item Acc Ass Project Definition", p1."PO Item Acc Ass Project Definition Description", p1."PO Item Acc Ass Project Definition Id", p1."PO Item Acc Ass Profit Center", p1."PO Item Acc Ass Category", p1."PO Item Creation Date", p1."PO Item Company Code", p1."PO Item Net Price", p1."PO Item PO Qty", p1."PO Item Currency", p1."PO Item Delivery Address Country Key", p1."PO Item Deletion Indicator", p1."Final Invoice Indicator", p1."Delivery Complete Indicator", p1."PO Item Schedule Line Delivery Date", ((p1."Goods receipt Qty")::integer), ((p1."Invoice receipt Qty")::integer), p1."CONV DATE YYYYMM", p1."PO Currency", p1."PMx Vendor ID", p1."PO Item Per Price Unit", p1."PO Item Acc Ass Cost Center", p1."PO Item Acc Ass Wbs Element", (CASE WHEN ((p1."PO Currency")::text = 'USD'::text) THEN '1'::numeric ELSE p16.gl_mor_rate END)
3. 0.000 0.000 ↓ 0.0

Merge Right Join (cost=5,778,430.04..5,780,967.47 rows=131,730 width=290) (actual rows= loops=)

  • Merge Cond: ((p16.dateyyyymm = p1."CONV DATE YYYYMM") AND ((p16.gl_conv_from_currency)::text = (p1."PO Currency")::text))
4. 0.000 0.000 ↓ 0.0

Sort (cost=14,132.02..14,166.74 rows=13,887 width=58) (actual rows= loops=)

  • Sort Key: p16.dateyyyymm, p16.gl_conv_from_currency
5. 0.000 0.000 ↓ 0.0

Subquery Scan on p16 (cost=12,516.86..13,176.50 rows=13,887 width=58) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

HashAggregate (cost=12,516.86..13,037.63 rows=13,887 width=58) (actual rows= loops=)

  • Group Key: uniform_gl_fx_rates_t.gl_mor_rate, uniform_gl_fx_rates_t.gl_conv_from_currency, ("substring"(((((((("substring"(uniform_gl_fx_rates_t.gl_conv_period, 0, 4) || '/'::text) || '1'::text) || '/'::text) || "substring"(uniform_gl_fx_rates_t.gl_conv_period, 5, 4)))::date)::character varying)::text, 0, 8))
7. 0.000 0.000 ↓ 0.0

Gather (cost=1,000.00..12,412.23 rows=13,951 width=58) (actual rows= loops=)

  • Workers Planned: 2
8. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on uniform_gl_fx_rates_t (cost=0.00..9,793.34 rows=5,813 width=58) (actual rows= loops=)

  • Filter: ((gl_conv_to_currency)::text = 'USD'::text)
9. 0.000 0.000 ↓ 0.0

Sort (cost=5,764,298.02..5,764,627.34 rows=131,730 width=314) (actual rows= loops=)

  • Sort Key: p1."CONV DATE YYYYMM", p1."PO Currency
10. 0.000 0.000 ↓ 0.0

Subquery Scan on p1 (cost=5,741,569.83..5,753,096.21 rows=131,730 width=314) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Unique (cost=5,741,569.83..5,751,778.91 rows=131,730 width=344) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Sort (cost=5,741,569.83..5,741,899.16 rows=131,730 width=344) (actual rows= loops=)

  • Sort Key: driver_t.po_number, driver_t.item_number, header_t.po_header_title_order_type, (to_date((((("substring"((header_t.po_header_title_doc_date)::text, 1, 4) || '-'::text) || "substring"((header_t.po_header_title_doc_date)::text, 5, 2)) || '-'::text) || "substring"((header_t.po_header_title_doc_date)::text, 7, 2)), 'YYYY-MM-DD'::text)), header_t.po_header_address_country_country_name, header_t.po_header_address_country_country_key, header_t.po_header_partners_name, header_t.po_header_delivery_invoice_currency, header_t.po_header_title_vendor, header_t.po_header_company_code, assignment_t.po_item_project_definition, assignment_t.po_item_project_definition_description, assignment_t.po_item_project_definition_id, assignment_t."PO Item Acc Assignment Profit Center", assignment_t."PO Item Acc Assignment Cost Center", assignment_t.po_item_account_assignment_wbs_element, (CASE WHEN (item_t.po_item_overview_a_account_assignment_category IS NULL) THEN ''::text WHEN ((item_t.po_item_overview_a_account_assignment_category)::text = 'A'::text) THEN 'A Asset'::text WHEN ((item_t.po_item_overview_a_account_assignment_category)::text = 'B'::text) THEN 'B MTS prod./sales ord.'::text WHEN ((item_t.po_item_overview_a_account_assignment_category)::text = 'C'::text) THEN 'C Sales Order'::text WHEN ((item_t.po_item_overview_a_account_assignment_category)::text = 'D'::text) THEN 'D Indiv.cust./project'::text WHEN ((item_t.po_item_overview_a_account_assignment_category)::text = 'E'::text) THEN 'E Ind. Cust. w. KD-CO'::text WHEN ((item_t.po_item_overview_a_account_assignment_category)::text = 'F'::text) THEN 'F Order'::text WHEN ((item_t.po_item_overview_a_account_assignment_category)::text = 'G'::text) THEN 'G MTS prod./project'::text WHEN ((item_t.po_item_overview_a_account_assignment_category)::text = 'K'::text) THEN 'K Cost center'::text WHEN ((item_t.po_item_overview_a_account_assignment_category)::text = 'M'::text) THEN 'M Ind. Cust. w/o KD-CO'::text WHEN ((item_t.po_item_overview_a_account_assignment_category)::text = 'N'::text) THEN 'N Network'::text WHEN ((item_t.po_item_overview_a_account_assignment_category)::text = 'P'::text) THEN 'P Project'::text WHEN ((item_t.po_item_overview_a_account_assignment_category)::text = 'Q'::text) THEN 'Q Proj. make-to-order'::text WHEN ((item_t.po_item_overview_a_account_assignment_category)::text = 'R'::text) THEN 'R Real Estate'::text WHEN ((item_t.po_item_overview_a_account_assignment_category)::text = 'T'::text) THEN 'T All new aux.acc.ass'::text WHEN ((item_t.po_item_overview_a_account_assignment_category)::text = 'U'::text) THEN 'U Unknown'::text WHEN ((item_t.po_item_overview_a_account_assignment_category)::text = 'X'::text) THEN 'X All aux.acct.assgts.'::text WHEN ((item_t.po_item_overview_a_account_assignment_category)::text = 'Y'::text) THEN 'Y '::text WHEN ((item_t.po_item_overview_a_account_assignment_category)::text = 'Z'::text) THEN 'Z Proj.- GR Non Val'::text ELSE NULL::text END), ((item_t.po_item_pol_creation_date)::date), item_t.po_item_account_assignment_cocode, item_t.po_item_overview_net_price, (round(((item_t.po_item_overview_po_qty)::integer)::double precision)), item_t.po_item_overview_currency, item_t.po_item_delivery_address_country_country_key, item_t.po_item_deletion_indicator, item_t.po_item_overview_per_price_unit, item_t.po_item_invoice_final_invoice, item_t.po_item_delivery_deliv_compl, lines_t.po_item_schedule_line_item_delivery_date, (sum(CASE WHEN ((pmx_04a20_purchase_order_item_history_t.transaction_event_type_purchase_order_history)::text = '1'::text) THEN pmx_04a20_purchase_order_item_history_t.purchase_order_history_qty ELSE '0'::numeric END)), (sum(CASE WHEN ((pmx_04a20_purchase_order_item_history_t.transaction_event_type_purchase_order_history)::text = '2'::text) THEN pmx_04a20_purchase_order_item_history_t.purchase_order_history_qty ELSE '0'::numeric END))
13. 0.000 0.000 ↓ 0.0

Group (cost=5,708,632.58..5,730,368.03 rows=131,730 width=344) (actual rows= loops=)

  • Group Key: driver_t.po_number, driver_t.item_number, header_t.po_header_title_order_type, header_t.po_header_title_doc_date, header_t.po_header_address_country_country_name, header_t.po_header_address_country_country_key, header_t.po_header_delivery_invoice_currency, header_t.po_header_partners_name, header_t.po_header_title_vendor, header_t.po_header_company_code, assignment_t.po_item_project_definition, assignment_t.po_item_project_definition_description, assignment_t.po_item_project_definition_id, assignment_t."PO Item Acc Assignment Profit Center", assignment_t."PO Item Acc Assignment Cost Center", assignment_t.po_item_account_assignment_wbs_element, item_t.po_item_account_assignment_cocode, item_t.po_item_pol_creation_date, item_t.po_item_overview_net_price, item_t.po_item_overview_po_qty, item_t.po_item_overview_currency, item_t.po_item_delivery_address_country_country_key, item_t.po_item_deletion_indicator, item_t.po_item_overview_a_account_assignment_category, item_t.po_item_overview_per_price_unit, item_t.po_item_invoice_final_invoice, item_t.po_item_delivery_deliv_compl, lines_t.po_item_schedule_line_item_delivery_date, (sum(CASE WHEN ((pmx_04a20_purchase_order_item_history_t.transaction_event_type_purchase_order_history)::text = '1'::text) THEN pmx_04a20_purchase_order_item_history_t.purchase_order_history_qty ELSE '0'::numeric END)), (sum(CASE WHEN ((pmx_04a20_purchase_order_item_history_t.transaction_event_type_purchase_order_history)::text = '2'::text) THEN pmx_04a20_purchase_order_item_history_t.purchase_order_history_qty ELSE '0'::numeric END))
14. 0.000 0.000 ↓ 0.0

Sort (cost=5,708,632.58..5,708,961.90 rows=131,730 width=264) (actual rows= loops=)

  • Sort Key: driver_t.po_number, driver_t.item_number, header_t.po_header_title_order_type, header_t.po_header_title_doc_date, header_t.po_header_address_country_country_name, header_t.po_header_address_country_country_key, header_t.po_header_delivery_invoice_currency, header_t.po_header_partners_name, header_t.po_header_title_vendor, header_t.po_header_company_code, assignment_t.po_item_project_definition, assignment_t.po_item_project_definition_description, assignment_t.po_item_project_definition_id, assignment_t."PO Item Acc Assignment Profit Center", assignment_t."PO Item Acc Assignment Cost Center", assignment_t.po_item_account_assignment_wbs_element, item_t.po_item_account_assignment_cocode, item_t.po_item_pol_creation_date, item_t.po_item_overview_net_price, item_t.po_item_overview_po_qty, item_t.po_item_overview_currency, item_t.po_item_delivery_address_country_country_key, item_t.po_item_deletion_indicator, item_t.po_item_overview_a_account_assignment_category, item_t.po_item_overview_per_price_unit, item_t.po_item_invoice_final_invoice, item_t.po_item_delivery_deliv_compl, lines_t.po_item_schedule_line_item_delivery_date, (sum(CASE WHEN ((pmx_04a20_purchase_order_item_history_t.transaction_event_type_purchase_order_history)::text = '1'::text) THEN pmx_04a20_purchase_order_item_history_t.purchase_order_history_qty ELSE '0'::numeric END)), (sum(CASE WHEN ((pmx_04a20_purchase_order_item_history_t.transaction_event_type_purchase_order_history)::text = '2'::text) THEN pmx_04a20_purchase_order_item_history_t.purchase_order_history_qty ELSE '0'::numeric END))
15. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=4,596,733.41..5,697,430.77 rows=131,730 width=264) (actual rows= loops=)

  • Hash Cond: (((lines_t.po_item_schedule_line_purchasing_document_number)::text = (driver_t.po_number)::text) AND ((lines_t.po_item_schedule_line_item_number_of_purchasing_document)::text = (driver_t.item_number)::text))
16. 0.000 0.000 ↓ 0.0

Seq Scan on pmx_04a30_purchase_order_item_schedule_lines_t lines_t (cost=0.00..833,272.02 rows=13,305,402 width=20) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Hash (cost=4,596,661.78..4,596,661.78 rows=4,775 width=260) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=3,982,122.77..4,596,661.78 rows=4,775 width=260) (actual rows= loops=)

  • Hash Cond: (((assignment_t."PO Item Acc Assignment Purchasing Document Number")::text = (driver_t.po_number)::text) AND ((assignment_t."PO Item Acc Assignment Item Number of Purchasing Document")::text = (driver_t.item_number)::text))
19. 0.000 0.000 ↓ 0.0

Seq Scan on pmx_04a35_purchase_order_item_account_assignment_t assignment_t (cost=0.00..545,318.44 rows=9,223,044 width=89) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Hash (cost=3,982,119.16..3,982,119.16 rows=241 width=187) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=3,895,618.58..3,982,119.16 rows=241 width=187) (actual rows= loops=)

  • Hash Cond: (((pmx_04a20_purchase_order_item_history_t.item_purchase_doc_number)::text = (driver_t.po_number)::text) AND ((pmx_04a20_purchase_order_item_history_t.items_number)::text = (driver_t.item_number)::text))
22. 0.000 0.000 ↓ 0.0

HashAggregate (cost=1,416,378.35..1,456,301.41 rows=2,661,537 width=80) (actual rows= loops=)

  • Group Key: pmx_04a20_purchase_order_item_history_t.item_purchase_doc_number, pmx_04a20_purchase_order_item_history_t.items_number
23. 0.000 0.000 ↓ 0.0

Seq Scan on pmx_04a20_purchase_order_item_history_t (cost=0.00..1,053,451.15 rows=24,195,147 width=23) (actual rows= loops=)

  • Filter: ((transaction_event_type_purchase_order_history)::text = ANY ('{1,2}'::text[]))
24. 0.000 0.000 ↓ 0.0

Hash (cost=2,479,236.61..2,479,236.61 rows=241 width=123) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Gather Merge (cost=2,476,384.48..2,479,236.61 rows=241 width=123) (actual rows= loops=)

  • Workers Planned: 2
26. 0.000 0.000 ↓ 0.0

Merge Join (cost=2,475,384.46..2,478,208.77 rows=100 width=123) (actual rows= loops=)

  • Merge Cond: (((item_t.po_item_overview_itm_number)::text = (driver_t.item_number)::text) AND ((item_t.item_purchase_doc_number_join)::text = (header_t.po_header_title_purchasing_document)::text))
27. 0.000 0.000 ↓ 0.0

Sort (cost=2,142,800.97..2,143,776.70 rows=390,292 width=58) (actual rows= loops=)

  • Sort Key: item_t.po_item_overview_itm_number, item_t.item_purchase_doc_number_join
28. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on pmx_04a15_purchase_order_item_t item_t (cost=0.00..2,106,554.17 rows=390,292 width=58) (actual rows= loops=)

  • Filter: (((po_item_deletion_indicator)::text <> 'L'::text) AND ((po_item_account_assignment_cocode)::text = ANY ('{P1J5,P6V6,P9AW,P6W9,P6Z7,P9BC,P9L8,P9AK,P6X7,P9AX,P0C4,P7B2,P9BB,P7F8,P0A4,P6X1,P7G1,P0A5,P0E7,PB23,P9AY,P79F,P79C,P7C4,P7F6,P9AU,P6V8,P79D,P79E,P6W7,P9AM,P6W8,PB17,P9AA,P6Y7,P9AB,P7G2,P7G4,P6Y2,P1J8,P6W2,P9AC,P6W5,P6Y3,P9AZ,P9AD,P6X5,P7A3,P9AN,P1D5,P9AS,P9AO,P6X9,P6X9,P0F3,P6X2,P1D6,PAEG,P6Z9,P9BD,P79G,P6V5,P7E2,P7G3,P1J6,P7A1,P0F4,P6Z8,P6X4,P9AE,P6Y4,P6Y6,P9AF,P9AG,P9AH,P6Y5,P6X3,P6Z5,P9BA,P6W6,P79I,P79A,P9BE,P1D4,P6X8,P9AL,P9AT,P6V7,P9AI,P1E5,P6Y8,P0C3,P9AJ,P6V1,P7F7,P6W1,P9BF,PD81,P9AR,P6V4,P0E4}'::text[])))
29. 0.000 0.000 ↓ 0.0

Sort (cost=332,572.79..332,586.45 rows=5,464 width=91) (actual rows= loops=)

  • Sort Key: driver_t.item_number, driver_t.po_number
30. 0.000 0.000 ↓ 0.0

Hash Join (cost=32,927.27..332,233.59 rows=5,464 width=91) (actual rows= loops=)

  • Hash Cond: ((header_t.po_header_title_purchasing_document)::text = (driver_t.po_number)::text)
31. 0.000 0.000 ↓ 0.0

Seq Scan on pmx_04a10_purchase_order_header_t header_t (cost=0.00..274,190.40 rows=15,458 width=74) (actual rows= loops=)

  • Filter: ("substring"((po_header_title_doc_date)::text, 1, 4) = '2020'::text)
32. 0.000 0.000 ↓ 0.0

Hash (cost=19,806.01..19,806.01 rows=1,049,701 width=17) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Seq Scan on pmx_04a00_fc_purchasing_driver_t driver_t (cost=0.00..19,806.01 rows=1,049,701 width=17) (actual rows= loops=)