explain.depesz.com

PostgreSQL's explain analyze made readable

Result: o0x

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 29,750.862 ↓ 0.0 0 1

Update on pg_temp_93._sales_potential_earnings (cost=295,753.05..338,427.56 rows=1 width=340) (actual time=29,750.862..29,750.862 rows=0 loops=1)

  • Buffers: shared hit=1152807 read=33175 dirtied=16, local hit=7501786 read=1828445 dirtied=660477 written=659609, temp read=64087 written=78484
  • I/O Timings: read=11211.537
2.          

Initplan (for Update)

3. 0.610 741.345 ↑ 1.0 1 1

Aggregate (cost=118,603.11..118,603.12 rows=1 width=32) (actual time=741.345..741.345 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=50, local read=101156 written=89
  • I/O Timings: read=343.285
4. 239.890 740.735 ↑ 1.0 1,776 1

Hash Join (cost=79.00..118,598.66 rows=1,780 width=86) (actual time=243.421..740.735 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=50, local read=101156 written=89
  • I/O Timings: read=343.285
5. 500.419 500.419 ↑ 1.0 1,373,074 1

Seq Scan on pg_temp_93._sales_potential_earnings s (cost=0.00..114,904.17 rows=1,374,817 width=102) (actual time=129.018..500.419 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=101156 written=89
  • I/O Timings: read=343.285
6. 0.006 0.426 ↑ 1.0 3 1

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

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

Seq Scan on client_lactalis.contract_family cf2 (cost=0.00..78.96 rows=3 width=16) (actual time=0.044..0.420 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. 349.424 29,495.979 ↓ 1,776.0 1,776 1

Hash Join (cost=177,149.92..219,824.44 rows=1 width=340) (actual time=29,145.902..29,495.979 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=1152807 read=33175 dirtied=16, local hit=7494615 read=1826724 dirtied=658792 written=658881, temp read=64087 written=78484
  • I/O Timings: read=10983.441
9. 27,678.050 27,678.050 ↑ 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=27,677.353..27,678.050 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=1152807 read=33175 dirtied=16, local hit=7494615 read=1725568 dirtied=658792 written=658881, temp read=32273 written=46796
  • I/O Timings: read=10639.713
10. 711.873 1,468.505 ↑ 1.0 1,373,074 1

Hash (cost=114,904.17..114,904.17 rows=1,374,817 width=220) (actual time=1,468.504..1,468.505 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: 64 Memory Usage: 4540kB
  • Buffers: local read=101156, temp written=31562
  • I/O Timings: read=343.728
11. 756.632 756.632 ↑ 1.0 1,373,074 1

Seq Scan on pg_temp_93._sales_potential_earnings (cost=0.00..114,904.17 rows=1,374,817 width=220) (actual time=124.567..756.632 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=101156
  • I/O Timings: read=343.728
Planning time : 0.378 ms
Execution time : 29,751.108 ms