explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Edt7

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 26,137.743 ↓ 0.0 0 1

Update on pg_temp_93._sales_potential_earnings (cost=584,021.31..846,629.61 rows=1 width=358) (actual time=26,137.743..26,137.743 rows=0 loops=1)

  • Buffers: shared hit=1373561 read=3399 dirtied=11, local hit=4184665 read=756243 dirtied=155940 written=155940, temp read=116169 written=115978
  • I/O Timings: read=9540.225
2.          

Initplan (for Update)

3. 853.363 7,900.459 ↑ 1.0 1 1

Aggregate (cost=248,190.62..248,190.63 rows=1 width=32) (actual time=7,900.458..7,900.459 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: local hit=1 read=212148 dirtied=69473 written=68450
  • I/O Timings: read=6111.212
4. 7,047.096 7,047.096 ↑ 2.1 1,373,074 1

Seq Scan on pg_temp_93._sales_potential_earnings s (cost=0.00..240,982.29 rows=2,883,329 width=70) (actual time=129.053..7,047.096 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 hit=1 read=212148 dirtied=69473 written=68450
  • I/O Timings: read=6111.212
5. 702.506 24,030.488 ↓ 1,371,298.0 1,371,298 1

Nested Loop (cost=335,830.69..598,438.99 rows=1 width=358) (actual time=18,983.996..24,030.488 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 * x.requested_quantity), _sales_potential_earnings.contract_excluded_amount, _sales_potential_earnings.tax_amount, _sales_potential_earnings.currency_conversion_ind, _sales_potential_earnings.ctid, x.ctid, cf.ctid, cs.ctid, r.*
  • 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=1373561 read=3399 dirtied=11, local hit=1406971 read=687307 dirtied=86431 written=88541, temp read=116169 written=115978
  • I/O Timings: read=9448.336
6. 2,073.632 21,956.684 ↓ 1,371,298.0 1,371,298 1

Hash Join (cost=335,830.69..598,437.96 rows=1 width=361) (actual time=18,983.971..21,956.684 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, _sales_potential_earnings.contract_excluded_amount, _sales_potential_earnings.tax_amount, _sales_potential_earnings.currency_conversion_ind, _sales_potential_earnings.ctid, x.requested_quantity, x.ctid, cf.ctid, cf.contract_type, r.rate, r.*
  • Hash Cond: ((_sales_potential_earnings.sale_id = r.source_row_id) AND (_sales_potential_earnings.contract_item_id = r.contract_item_id))
  • Buffers: shared hit=2263 read=3399 dirtied=11, local hit=1406971 read=687307 dirtied=86431 written=88541, temp read=116169 written=115978
  • I/O Timings: read=9448.336
7. 1,300.854 1,300.854 ↑ 2.1 1,373,074 1

Seq Scan on pg_temp_93._sales_potential_earnings (cost=0.00..240,982.29 rows=2,883,329 width=220) (actual time=304.592..1,300.854 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=212149 dirtied=1 written=2111
  • I/O Timings: read=812.873
8. 1,125.825 18,582.198 ↓ 1,371,298.0 1,371,298 1

Hash (cost=335,830.67..335,830.67 rows=1 width=205) (actual time=18,582.198..18,582.198 rows=1,371,298 loops=1)

  • Output: x.requested_quantity, x.ctid, x.sale_id, x.contract_item_id, cf.ctid, cf.contract_type, r.rate, r.*, r.source_row_id, r.contract_item_id
  • Buckets: 65536 (originally 1024) Batches: 64 (originally 1) Memory Usage: 7681kB
  • Buffers: shared hit=2247 read=3399 dirtied=11, local hit=1406971 read=475158 dirtied=86430 written=86430, temp read=50957 written=82582
  • I/O Timings: read=8635.463
9. 1,404.047 17,456.373 ↓ 1,371,298.0 1,371,298 1

Hash Join (cost=309,988.66..335,830.67 rows=1 width=205) (actual time=15,525.680..17,456.373 rows=1,371,298 loops=1)

  • Output: x.requested_quantity, x.ctid, x.sale_id, x.contract_item_id, cf.ctid, cf.contract_type, r.rate, r.*, r.source_row_id, r.contract_item_id
  • Hash Cond: ((r.source_row_id = x.sale_id) AND (r.contract_item_id = x.contract_item_id))
  • Buffers: shared hit=2247 read=3399 dirtied=11, local hit=1406971 read=475158 dirtied=86430 written=86430, temp read=50957 written=50892
  • I/O Timings: read=8635.463
10. 12,398.152 12,398.152 ↓ 274.6 1,373,074 1

Function Scan on gosimple.calculate_contract_item_direct_rebate_rate r (cost=2.50..52.50 rows=5,000 width=152) (actual time=11,871.310..12,398.152 rows=1,373,074 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=2197 read=3399 dirtied=11, local hit=1406970 read=263010 dirtied=86430 written=86430, temp read=16382 written=16379
  • I/O Timings: read=6257.158
11. 457.064 3,654.174 ↑ 1.8 1,371,298 1

Hash (cost=248,677.14..248,677.14 rows=2,475,535 width=53) (actual time=3,654.174..3,654.174 rows=1,371,298 loops=1)

  • Output: x.requested_quantity, x.ctid, x.sale_id, x.contract_item_id, cf.ctid, cf.contract_type
  • Buckets: 131072 Batches: 32 Memory Usage: 4726kB
  • Buffers: shared hit=50, local hit=1 read=212148, temp written=12312
  • I/O Timings: read=2378.305
12. 397.394 3,197.110 ↑ 1.8 1,371,298 1

Hash Join (cost=112.19..248,677.14 rows=2,475,535 width=53) (actual time=119.949..3,197.110 rows=1,371,298 loops=1)

  • Output: x.requested_quantity, x.ctid, x.sale_id, x.contract_item_id, cf.ctid, cf.contract_type
  • Inner Unique: true
  • Hash Cond: (x.contract_family_id = cf.contract_family_id)
  • Buffers: shared hit=50, local hit=1 read=212148
  • I/O Timings: read=2378.305
13. 2,798.694 2,798.694 ↑ 2.1 1,373,074 1

Seq Scan on pg_temp_93._sales_potential_earnings x (cost=0.00..240,982.29 rows=2,883,329 width=59) (actual time=118.887..2,798.694 rows=1,373,074 loops=1)

  • Output: x.requested_quantity, x.ctid, x.contract_family_id, x.sale_id, x.contract_item_id
  • Buffers: local hit=1 read=212148
  • I/O Timings: read=2378.305
14. 0.426 1.022 ↓ 1.2 2,408 1

Hash (cost=87.46..87.46 rows=1,979 width=26) (actual time=1.022..1.022 rows=2,408 loops=1)

  • Output: cf.ctid, cf.contract_family_id, cf.contract_type
  • Buckets: 4096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 174kB
  • Buffers: shared hit=50
15. 0.596 0.596 ↓ 1.2 2,408 1

Seq Scan on client_lactalis.contract_family cf (cost=0.00..87.46 rows=1,979 width=26) (actual time=0.014..0.596 rows=2,408 loops=1)

  • Output: cf.ctid, cf.contract_family_id, cf.contract_type
  • 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
16. 1,371.298 1,371.298 ↑ 1.0 1 1,371,298

Seq Scan on client_lactalis.client_settings cs (cost=0.00..1.01 rows=1 width=7) (actual time=0.001..0.001 rows=1 loops=1,371,298)

  • Output: cs.ctid, cs.use_sale_off_invoice_reduction
  • Buffers: shared hit=1371298
Planning time : 4.323 ms
Execution time : 26,165.120 ms