explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Xmg7N

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 13,528.846 ↓ 0.0 0 1

Update on pg_temp_93._sales_potential_earnings (cost=767,270.57..870,979.08 rows=1 width=340) (actual time=13,528.846..13,528.846 rows=0 loops=1)

  • Buffers: shared hit=2011 read=3413 dirtied=8, local hit=4182486 read=614036 dirtied=156010 written=156010, temp read=71257 written=71000
  • I/O Timings: read=1824.236
2.          

Initplan (for Update)

3. 603.165 2,409.436 ↑ 1.0 1 1

Aggregate (cost=335,025.41..335,025.42 rows=1 width=32) (actual time=2,409.435..2,409.436 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=246823 dirtied=69480 written=69479
  • I/O Timings: read=700.957
4. 458.730 1,806.271 ↑ 2.4 1,371,298 1

Hash Semi Join (cost=144.53..326,638.94 rows=3,354,586 width=70) (actual time=133.117..1,806.271 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=246823 dirtied=69480 written=69479
  • I/O Timings: read=700.957
5. 1,346.225 1,346.225 ↑ 2.4 1,373,074 1

Seq Scan on pg_temp_93._sales_potential_earnings s (cost=0.00..280,368.86 rows=3,354,586 width=86) (actual time=131.771..1,346.225 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=246823 dirtied=69480 written=69479
  • I/O Timings: read=700.957
6. 0.373 1.316 ↓ 1.3 2,408 1

Hash (cost=120.63..120.63 rows=1,912 width=16) (actual time=1.316..1.316 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.399 0.943 ↓ 1.3 2,408 1

Nested Loop (cost=0.00..120.63 rows=1,912 width=16) (actual time=0.025..0.943 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.008..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.535 0.535 ↓ 1.2 2,408 1

Seq Scan on client_lactalis.contract_family cf (cost=0.00..87.46 rows=1,979 width=20) (actual time=0.011..0.535 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. 2,624.568 11,601.758 ↓ 1,371,298.0 1,371,298 1

Hash Join (cost=432,245.15..535,953.66 rows=1 width=340) (actual time=8,439.046..11,601.758 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=2011 read=3413 dirtied=8, local hit=1405149 read=544442 dirtied=86415 written=87438, temp read=71257 written=71000
  • I/O Timings: read=1738.833
11. 7,050.458 7,050.458 ↓ 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=6,512.237..7,050.458 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=2011 read=3413 dirtied=8, local hit=1405149 read=297619 dirtied=86415 written=87438, temp read=16371 written=16368
  • I/O Timings: read=978.528
12. 691.929 1,926.732 ↑ 2.4 1,373,074 1

Hash (cost=280,368.86..280,368.86 rows=3,354,586 width=220) (actual time=1,926.732..1,926.732 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=246823, temp written=31778
  • I/O Timings: read=760.305
13. 1,234.803 1,234.803 ↑ 2.4 1,373,074 1

Seq Scan on pg_temp_93._sales_potential_earnings (cost=0.00..280,368.86 rows=3,354,586 width=220) (actual time=117.125..1,234.803 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=246823
  • I/O Timings: read=760.305
Planning time : 1.213 ms
Execution time : 13,563.034 ms