explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0EAk

Settings
# exclusive inclusive rows x rows loops node
1. 914.630 72,708.705 ↑ 1.3 99,045 1

Group (cost=5,708,632.58..5,730,368.03 rows=131,730 width=344) (actual time=71,756.042..72,708.705 rows=99,045 loops=1)

  • 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))
2. 7,768.765 71,794.075 ↓ 5.1 676,474 1

Sort (cost=5,708,632.58..5,708,961.90 rows=131,730 width=264) (actual time=71,756.004..71,794.075 rows=676,474 loops=1)

  • 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))
  • Sort Method: quicksort Memory: 267,778kB
3. 3,870.227 64,025.310 ↓ 5.1 676,474 1

Hash Right Join (cost=4,596,733.41..5,697,430.77 rows=131,730 width=264) (actual time=62,089.271..64,025.310 rows=676,474 loops=1)

  • 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))
4. 2,335.308 2,335.308 ↑ 1.0 13,305,067 1

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 time=0.016..2,335.308 rows=13,305,067 loops=1)

5. 437.466 57,819.775 ↓ 141.7 676,474 1

Hash (cost=4,596,661.78..4,596,661.78 rows=4,775 width=260) (actual time=57,819.775..57,819.775 rows=676,474 loops=1)

  • Buckets: 1,048,576 (originally 8192) Batches: 1 (originally 1) Memory Usage: 166,359kB
6. 2,773.265 57,382.309 ↓ 141.7 676,474 1

Hash Right Join (cost=3,982,122.77..4,596,661.78 rows=4,775 width=260) (actual time=56,835.697..57,382.309 rows=676,474 loops=1)

  • 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))
7. 1,560.180 1,560.180 ↑ 1.0 9,222,652 1

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 time=0.011..1,560.180 rows=9,222,652 loops=1)

8. 249.239 53,048.864 ↓ 1,725.8 415,906 1

Hash (cost=3,982,119.16..3,982,119.16 rows=241 width=187) (actual time=53,048.864..53,048.864 rows=415,906 loops=1)

  • Buckets: 524,288 (originally 1024) Batches: 1 (originally 1) Memory Usage: 75,939kB
9. 2,364.810 52,799.625 ↓ 1,725.8 415,906 1

Hash Right Join (cost=3,895,618.58..3,982,119.16 rows=241 width=187) (actual time=42,882.250..52,799.625 rows=415,906 loops=1)

  • 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))
10. 23,380.340 30,237.325 ↓ 3.2 8,392,419 1

HashAggregate (cost=1,416,378.35..1,456,301.41 rows=2,661,537 width=80) (actual time=22,684.686..30,237.325 rows=8,392,419 loops=1)

  • Group Key: pmx_04a20_purchase_order_item_history_t.item_purchase_doc_number, pmx_04a20_purchase_order_item_history_t.items_number
11. 6,856.985 6,856.985 ↓ 1.0 24,243,794 1

Seq Scan on pmx_04a20_purchase_order_item_history_t (cost=0.00..1,053,451.15 rows=24,195,147 width=23) (actual time=0.020..6,856.985 rows=24,243,794 loops=1)

  • Filter: ((transaction_event_type_purchase_order_history)::text = ANY ('{1,2}'::text[]))
  • Rows Removed by Filter: 2,366,806
12. 229.381 20,197.490 ↓ 1,725.8 415,906 1

Hash (cost=2,479,236.61..2,479,236.61 rows=241 width=123) (actual time=20,197.490..20,197.490 rows=415,906 loops=1)

  • Buckets: 524,288 (originally 1024) Batches: 1 (originally 1) Memory Usage: 71,751kB
13. 616.229 19,968.109 ↓ 1,725.8 415,906 1

Gather Merge (cost=2,476,384.48..2,479,236.61 rows=241 width=123) (actual time=19,210.095..19,968.109 rows=415,906 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
14. 209.127 19,351.880 ↓ 1,386.3 138,635 3 / 3

Merge Join (cost=2,475,384.46..2,478,208.77 rows=100 width=123) (actual time=19,086.367..19,351.880 rows=138,635 loops=3)

  • 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))
15. 610.826 15,679.965 ↑ 4.7 82,697 3 / 3

Sort (cost=2,142,800.97..2,143,776.70 rows=390,292 width=58) (actual time=15,662.961..15,679.965 rows=82,697 loops=3)

  • Sort Key: item_t.po_item_overview_itm_number, item_t.item_purchase_doc_number_join
  • Sort Method: quicksort Memory: 14,395kB
16. 15,069.139 15,069.139 ↑ 4.7 82,697 3 / 3

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 time=0.160..15,069.139 rows=82,697 loops=3)

  • 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[])))
  • Rows Removed by Filter: 4,334,682
17. 1,689.758 3,462.788 ↓ 72.5 395,925 3 / 3

Sort (cost=332,572.79..332,586.45 rows=5,464 width=91) (actual time=3,410.242..3,462.788 rows=395,925 loops=3)

  • Sort Key: driver_t.item_number, driver_t.po_number
  • Sort Method: quicksort Memory: 68,567kB
18. 185.307 1,773.030 ↓ 71.3 389,311 3 / 3

Hash Join (cost=32,927.27..332,233.59 rows=5,464 width=91) (actual time=641.073..1,773.030 rows=389,311 loops=3)

  • Hash Cond: ((header_t.po_header_title_purchasing_document)::text = (driver_t.po_number)::text)
19. 1,209.633 1,209.633 ↓ 12.4 191,095 3 / 3

Seq Scan on pmx_04a10_purchase_order_header_t header_t (cost=0.00..274,190.40 rows=15,458 width=74) (actual time=0.211..1,209.633 rows=191,095 loops=3)

  • Filter: ("substring"((po_header_title_doc_date)::text, 1, 4) = '2020'::text)
  • Rows Removed by Filter: 2,899,147
20. 239.525 378.090 ↑ 1.0 1,049,701 3 / 3

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

  • Buckets: 2,097,152 Batches: 1 Memory Usage: 66,614kB
21. 138.565 138.565 ↑ 1.0 1,049,701 3 / 3

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

Planning time : 6.614 ms
Execution time : 72,999.214 ms