explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ENx2

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 12,307.745 ↓ 0.0 0 1

Update on pg_temp_93._sales_potential_earnings (cost=551,738.26..626,388.77 rows=1 width=340) (actual time=12,307.745..12,307.745 rows=0 loops=1)

  • Buffers: shared hit=4675 read=9 dirtied=11, local hit=4182125 read=475397 dirtied=156057 written=155133, temp read=71255 written=70998
  • I/O Timings: read=1262.446
2.          

Initplan (for Update)

3. 628.401 2,257.631 ↑ 1.0 1 1

Aggregate (cost=240,936.93..240,936.94 rows=1 width=32) (actual time=2,257.630..2,257.631 rows=1 loops=1)

  • Output: array_agg(ROW(s.contract_item_id, s.sale_id, s.product_id, s.price, s.uom_type_id)::gosimple.in_calculate_contract_item_rebate_rate)
  • Buffers: shared hit=51, local read=177475 dirtied=69471 written=68546
  • I/O Timings: read=488.719
4. 484.861 1,629.230 ↑ 1.8 1,371,298 1

Hash Semi Join (cost=145.01..234,906.74 rows=2,412,073 width=70) (actual time=131.566..1,629.230 rows=1,371,298 loops=1)

  • Output: s.contract_item_id, s.sale_id, s.product_id, s.price, s.uom_type_id
  • Hash Cond: (s.contract_family_id = cf.contract_family_id)
  • Buffers: shared hit=51, local read=177475 dirtied=69471 written=68546
  • I/O Timings: read=488.719
5. 1,143.095 1,143.095 ↑ 1.8 1,373,074 1

Seq Scan on pg_temp_93._sales_potential_earnings s (cost=0.00..201,595.73 rows=2,412,073 width=86) (actual time=130.263..1,143.095 rows=1,373,074 loops=1)

  • Output: s.sale_id, s.sale_number, s.sale_reporting_date, s.company_id, s.product_id, s.requested_quantity, s.allowed_quantity, s.price, s.uom_type_id, s.claimant_company_id, s.contract_family_id, s.contract_item_id, s.amount, s.contract_excluded_amount, s.tax_amount, s.currency_conversion_ind
  • Buffers: local read=177475 dirtied=69471 written=68546
  • I/O Timings: read=488.719
6. 0.364 1.274 ↓ 1.3 2,408 1

Hash (cost=120.98..120.98 rows=1,922 width=16) (actual time=1.274..1.274 rows=2,408 loops=1)

  • Output: cf.contract_family_id
  • Buckets: 4096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 145kB
  • Buffers: shared hit=51
7. 0.390 0.910 ↓ 1.3 2,408 1

Nested Loop (cost=0.00..120.98 rows=1,922 width=16) (actual time=0.024..0.910 rows=2,408 loops=1)

  • Output: cf.contract_family_id
  • Join Filter: ((cf.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD,OFF_INVOICE}'::enum.contract_type[])) OR ((cf.contract_type = 'OFF_INVOICE'::enum.contract_type) AND (NOT cs.use_sale_off_invoice_reduction)))
  • Buffers: shared hit=51
8. 0.009 0.009 ↑ 1.0 1 1

Seq Scan on client_lactalis.client_settings cs (cost=0.00..1.01 rows=1 width=1) (actual time=0.009..0.009 rows=1 loops=1)

  • Output: cs.client_id, cs.invoice_contact_id, cs.invoice_email_address, cs.fiscal_calendar_ind, cs.client_description, cs.address_1, cs.address_2, cs.address_3, cs.address_4, cs.city, cs.region, cs.postal_code, cs.country, cs.logo, cs.timezone, cs.reporting_role_name, cs.use_sales_data_pricegen, cs.lookback_period, cs.pricegen_period_type, cs.average_sale_price_change_tolerance, cs.brand_logo, cs.claim_tolerance_amount, cs.checkrun_deduction_timeframe_buffer_days, cs.location_claimant_enforce_eligible_distributor, cs.feed_company_buying_grouping_ind, cs.feed_company_contract_visibility_grouping_ind, cs.feed_company_parent_grouping_ind, cs.feed_product_grouping_ind, cs.feed_sale_extended_price_tolerance, cs.feed_product_default_price_ind, cs.zero_rate_auto_reject, cs.minimum_password_entropy, cs.feed_company_claim_visibility_grouping_ind, cs.price_tier_list, cs.default_claim_uom_type_id, cs.feed_case_insensitive_identifiers, cs.claim_tolerance_ind, cs.feed_company_email_ind, cs.pivot_currency, cs.feed_company_agg_grouping_ind, cs.operator_request_ind, cs.lumpsum_product_allocation_ind, cs.earnings_catch_up_interval, cs.claim_rate_scale, cs.off_invoice_claim_generation, cs.eclaim_ind, cs.contract_forecast_ind, cs.use_sale_off_invoice_reduction, cs.accrual_period, cs.forecast_period, cs.accrual_reporting_calendar, cs.contract_forecast_sales_lookback_gap_interval, cs.contract_forecast_sales_lookback_period_interval, cs.pricegen_strategy, cs.accrual_forecast_ind, cs.unipro_supplier_number, cs.unipro_manufacturer_duns_number, cs.ima_supplier_number
  • Buffers: shared hit=1
9. 0.511 0.511 ↓ 1.2 2,408 1

Seq Scan on client_lactalis.contract_family cf (cost=0.00..87.65 rows=1,989 width=20) (actual time=0.010..0.511 rows=2,408 loops=1)

  • Output: cf.contract_family_id, cf.client_id, cf.contractee_company_id, cf.contractee_grouping_id, cf.creator_id, cf.contract_sequence, cf.cost_basis_type, cf.contract_type, cf.contract_title, cf.contract_template_id, cf.payment_calendar_id, cf.catch_all, cf.accrual_quantity_source, cf.import_contract_number, cf.accrual_lumpsum_spread
  • Filter: ((cf.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD,OFF_INVOICE}'::enum.contract_type[])) OR (cf.contract_type = 'OFF_INVOICE'::enum.contract_type))
  • Rows Removed by Filter: 3
  • Buffers: shared hit=50
10. 1,980.099 10,325.314 ↓ 1,371,298.0 1,371,298 1

Hash Join (cost=310,801.33..385,451.84 rows=1 width=340) (actual time=7,834.515..10,325.314 rows=1,371,298 loops=1)

  • Output: _sales_potential_earnings.sale_id, _sales_potential_earnings.sale_number, _sales_potential_earnings.sale_reporting_date, _sales_potential_earnings.company_id, _sales_potential_earnings.product_id, _sales_potential_earnings.requested_quantity, _sales_potential_earnings.allowed_quantity, _sales_potential_earnings.price, _sales_potential_earnings.uom_type_id, _sales_potential_earnings.claimant_company_id, _sales_potential_earnings.contract_family_id, _sales_potential_earnings.contract_item_id, (r.rate * _sales_potential_earnings.requested_quantity), _sales_potential_earnings.contract_excluded_amount, _sales_potential_earnings.tax_amount, _sales_potential_earnings.currency_conversion_ind, _sales_potential_earnings.ctid, r.*
  • Hash Cond: ((r.source_row_id = _sales_potential_earnings.sale_id) AND (r.contract_item_id = _sales_potential_earnings.contract_item_id))
  • Buffers: shared hit=4675 read=9 dirtied=11, local hit=1405149 read=405746 dirtied=86406 written=86505, temp read=71255 written=70998
  • I/O Timings: read=1175.765
11. 6,496.840 6,496.840 ↓ 274.3 1,371,298 1

Function Scan on gosimple.calculate_contract_item_direct_rebate_rate r (cost=2.50..52.50 rows=5,000 width=152) (actual time=5,986.061..6,496.840 rows=1,371,298 loops=1)

  • Output: r.rate, r.*, r.source_row_id, r.contract_item_id
  • Function Call: gosimple.calculate_contract_item_direct_rebate_rate($0)
  • Buffers: shared hit=4675 read=9 dirtied=11, local hit=1405149 read=228271 dirtied=86406 written=86505, temp read=16369 written=16366
  • I/O Timings: read=603.002
12. 782.544 1,848.375 ↑ 1.8 1,373,074 1

Hash (cost=201,595.73..201,595.73 rows=2,412,073 width=220) (actual time=1,848.375..1,848.375 rows=1,373,074 loops=1)

  • Output: _sales_potential_earnings.sale_id, _sales_potential_earnings.sale_number, _sales_potential_earnings.sale_reporting_date, _sales_potential_earnings.company_id, _sales_potential_earnings.product_id, _sales_potential_earnings.requested_quantity, _sales_potential_earnings.allowed_quantity, _sales_potential_earnings.price, _sales_potential_earnings.uom_type_id, _sales_potential_earnings.claimant_company_id, _sales_potential_earnings.contract_family_id, _sales_potential_earnings.contract_item_id, _sales_potential_earnings.contract_excluded_amount, _sales_potential_earnings.tax_amount, _sales_potential_earnings.currency_conversion_ind, _sales_potential_earnings.ctid
  • Buckets: 32768 Batches: 128 Memory Usage: 2418kB
  • Buffers: local read=177475, temp written=31778
  • I/O Timings: read=572.762
13. 1,065.831 1,065.831 ↑ 1.8 1,373,074 1

Seq Scan on pg_temp_93._sales_potential_earnings (cost=0.00..201,595.73 rows=2,412,073 width=220) (actual time=121.257..1,065.831 rows=1,373,074 loops=1)

  • Output: _sales_potential_earnings.sale_id, _sales_potential_earnings.sale_number, _sales_potential_earnings.sale_reporting_date, _sales_potential_earnings.company_id, _sales_potential_earnings.product_id, _sales_potential_earnings.requested_quantity, _sales_potential_earnings.allowed_quantity, _sales_potential_earnings.price, _sales_potential_earnings.uom_type_id, _sales_potential_earnings.claimant_company_id, _sales_potential_earnings.contract_family_id, _sales_potential_earnings.contract_item_id, _sales_potential_earnings.contract_excluded_amount, _sales_potential_earnings.tax_amount, _sales_potential_earnings.currency_conversion_ind, _sales_potential_earnings.ctid
  • Buffers: local read=177475
  • I/O Timings: read=572.762
Planning time : 1.109 ms
Execution time : 12,346.017 ms