explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sHv9

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 28,735.132 ↓ 0.0 0 1

Update on pg_temp_93._sales_potential_earnings (cost=493,704.26..626,290.77 rows=1 width=340) (actual time=28,735.132..28,735.132 rows=0 loops=1)

  • Buffers: shared hit=1155701 read=34707 dirtied=34, local hit=7500441 read=1826089 dirtied=658618 written=658617, temp read=65124 written=79137
  • I/O Timings: read=10357.039
2.          

Initplan (for Update)

3. 1.173 1,109.239 ↑ 1.0 1 1

Aggregate (cost=155,402.18..155,402.19 rows=1 width=32) (actual time=1,109.239..1,109.239 rows=1 loops=1)

  • Output: array_agg(ROW(s.contract_item_id, s.sale_id, s.product_id, s.company_id, s.price, s.uom_type_id)::gosimple.in_calculate_contract_item_growth_rate)
  • Buffers: shared hit=66, local read=101113 dirtied=90 written=91
  • I/O Timings: read=584.172
4. 316.693 1,108.066 ↑ 3.1 1,776 1

Hash Join (cost=79.00..155,388.28 rows=5,556 width=86) (actual time=410.061..1,108.066 rows=1,776 loops=1)

  • Output: s.contract_item_id, s.sale_id, s.product_id, s.company_id, s.price, s.uom_type_id
  • Inner Unique: true
  • Hash Cond: (s.contract_family_id = cf2.contract_family_id)
  • Buffers: shared hit=66, local read=101113 dirtied=90 written=91
  • I/O Timings: read=584.172
5. 790.867 790.867 ↑ 3.1 1,373,074 1

Seq Scan on pg_temp_93._sales_potential_earnings s (cost=0.00..144,024.43 rows=4,291,143 width=102) (actual time=297.150..790.867 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=101113 dirtied=90 written=91
  • I/O Timings: read=584.172
6. 0.007 0.506 ↑ 1.0 3 1

Hash (cost=78.96..78.96 rows=3 width=16) (actual time=0.506..0.506 rows=3 loops=1)

  • Output: cf2.contract_family_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=50
7. 0.499 0.499 ↑ 1.0 3 1

Seq Scan on client_lactalis.contract_family cf2 (cost=0.00..78.96 rows=3 width=16) (actual time=0.052..0.499 rows=3 loops=1)

  • Output: cf2.contract_family_id
  • Filter: (cf2.contract_type = ANY ('{GROWTH_STANDARD,GROWTH_AUTOPAY}'::enum.contract_type[]))
  • Rows Removed by Filter: 2408
  • Buffers: shared hit=50
8. 274.282 28,730.708 ↓ 1,776.0 1,776 1

Hash Join (cost=338,302.07..470,888.59 rows=1 width=340) (actual time=28,455.377..28,730.708 rows=1,776 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=1155701 read=34707 dirtied=34, local hit=7496836 read=1826043 dirtied=658527 written=658617, temp read=65124 written=79137
  • I/O Timings: read=10357.015
9. 26,834.496 26,834.496 ↑ 2.8 1,776 1

Function Scan on gosimple.calculate_contract_item_growth_rebate_rate r (cost=2.50..52.50 rows=5,000 width=152) (actual time=26,833.380..26,834.496 rows=1,776 loops=1)

  • Output: r.rate, r.*, r.source_row_id, r.contract_item_id
  • Function Call: gosimple.calculate_contract_item_growth_rebate_rate($0)
  • Buffers: shared hit=1155701 read=34707 dirtied=34, local hit=7496836 read=1724930 dirtied=658527 written=658617, temp read=32272 written=46795
  • I/O Timings: read=9974.005
10. 786.002 1,621.930 ↑ 3.1 1,373,074 1

Hash (cost=144,024.43..144,024.43 rows=4,291,143 width=220) (actual time=1,621.930..1,621.930 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: 256 Memory Usage: 1328kB
  • Buffers: local read=101113, temp written=31832
  • I/O Timings: read=383.010
11. 835.928 835.928 ↑ 3.1 1,373,074 1

Seq Scan on pg_temp_93._sales_potential_earnings (cost=0.00..144,024.43 rows=4,291,143 width=220) (actual time=146.389..835.928 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=101113
  • I/O Timings: read=383.010
Planning time : 3.907 ms
Execution time : 28,735.500 ms