explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ALNI

Settings
# exclusive inclusive rows x rows loops node
1. 3,951.642 29,729.635 ↓ 0.0 0 1

Update on pg_temp_93._sales_potential_earnings (cost=193,900.81..210,135.39 rows=1 width=340) (actual time=29,729.635..29,729.635 rows=0 loops=1)

  • Buffers: shared hit=5,683 read=8 dirtied=11, local hit=13,740,834 read=1,694,969 dirtied=1,462,768 written=1,461,948, temp read=16,344 written=16,341
  • I/O Timings: read=4,685.356
2.          

Initplan (for Update)

3. 601.108 1,854.521 ↑ 1.0 1 1

Aggregate (cost=193,897.87..193,897.88 rows=1 width=32) (actual time=1,854.520..1,854.521 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=66 read=1, local read=142,805 dirtied=34,696 written=34,696
  • I/O Timings: read=418.128
4. 447.701 1,253.413 ↑ 1.4 1,371,298 1

Hash Semi Join (cost=145.01..189,045.69 rows=1,940,871 width=70) (actual time=126.384..1,253.413 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=66 read=1, local read=142,805 dirtied=34,696 written=34,696
  • I/O Timings: read=418.128
5. 804.079 804.079 ↑ 1.4 1,373,074 1

Seq Scan on pg_temp_93._sales_potential_earnings s (cost=0.00..162,213.71 rows=1,940,871 width=86) (actual time=124.663..804.079 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=142,805 dirtied=34,696 written=34,696
  • I/O Timings: read=417.824
6. 0.377 1.633 ↓ 1.3 2,408 1

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

  • Output: cf.contract_family_id
  • Buckets: 4,096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 145kB
  • Buffers: shared hit=50 read=1
  • I/O Timings: read=0.305
7. 0.412 1.256 ↓ 1.3 2,408 1

Nested Loop (cost=0.00..120.98 rows=1,922 width=16) (actual time=0.330..1.256 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=50 read=1
  • I/O Timings: read=0.305
8. 0.006 0.006 ↑ 1.0 1 1

Seq Scan on client_lactalis.client_settings cs (cost=0.00..1.01 rows=1 width=1) (actual time=0.006..0.006 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.838 0.838 ↓ 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.319..0.838 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=49 read=1
  • I/O Timings: read=0.305
10. 1,149.054 23,923.472 ↓ 1,371,298.0 1,371,298 1

Nested Loop (cost=2.93..16,237.50 rows=1 width=340) (actual time=5,663.353..23,923.472 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.*
  • Buffers: shared hit=5,683 read=8 dirtied=11, local hit=6,816,067 read=1,655,788 dirtied=68,365 written=1,424,919, temp read=16,344 written=16,341
  • I/O Timings: read=4,678.922
11. 6,318.842 6,318.842 ↓ 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,663.289..6,318.842 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=5,683 read=8 dirtied=11, local hit=1,405,149 read=193,601 dirtied=51,631 written=51,631, temp read=16,344 written=16,341
  • I/O Timings: read=534.598
12. 16,455.576 16,455.576 ↑ 1.0 1 1,371,298

Index Scan using idx_spe_test on pg_temp_93._sales_potential_earnings (cost=0.43..3.23 rows=1 width=220) (actual time=0.011..0.012 rows=1 loops=1,371,298)

  • 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
  • Index Cond: ((_sales_potential_earnings.sale_id = r.source_row_id) AND (_sales_potential_earnings.contract_item_id = r.contract_item_id))
  • Buffers: local hit=5,410,918 read=1,462,187 dirtied=16,734 written=1,373,288
  • I/O Timings: read=4,144.324
Planning time : 2.410 ms
Execution time : 29,767.807 ms