explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Uk9t

Settings
# exclusive inclusive rows x rows loops node
1. 5,065.671 6,862.807 ↓ 274.3 1,371,298 1

Function Scan on gosimple.calculate_contract_item_direct_rebate_rate r (cost=193,900.38..193,950.38 rows=5,000 width=81) (actual time=6,686.666..6,862.807 rows=1,371,298 loops=1)

  • Output: r.contract_item_id, r.source_row_id, r.product_id, r.rate, r.explicit_zero_rate_ind
  • Function Call: gosimple.calculate_contract_item_direct_rebate_rate($0)
  • Buffers: shared hit=5730 read=12 dirtied=13, local hit=1405149 read=193601 dirtied=27133 written=27133, temp read=16349 written=16346
  • I/O Timings: read=582.598
2.          

Initplan (for Function Scan)

3. 607.415 1,797.136 ↑ 1.0 1 1

Aggregate (cost=193,897.87..193,897.88 rows=1 width=32) (actual time=1,797.135..1,797.136 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=67, local read=142805 dirtied=10198 written=10198
  • I/O Timings: read=457.670
4. 469.740 1,189.721 ↑ 1.4 1,371,298 1

Hash Semi Join (cost=145.01..189,045.69 rows=1,940,871 width=70) (actual time=133.221..1,189.721 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=67, local read=142805 dirtied=10198 written=10198
  • I/O Timings: read=457.670
5. 718.645 718.645 ↑ 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=131.789..718.645 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=142805 dirtied=10198 written=10198
  • I/O Timings: read=457.670
6. 0.381 1.336 ↓ 1.3 2,408 1

Hash (cost=120.98..120.98 rows=1,922 width=16) (actual time=1.336..1.336 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.400 0.955 ↓ 1.3 2,408 1

Nested Loop (cost=0.00..120.98 rows=1,922 width=16) (actual time=0.023..0.955 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.007 0.007 ↑ 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.007 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.548 0.548 ↓ 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.011..0.548 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
Planning time : 1.513 ms
Execution time : 6,974.746 ms