explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WjN5

Settings
# exclusive inclusive rows x rows loops node
1. 21,360.344 22,134.788 ↑ 2.8 1,776 1

Function Scan on gosimple.calculate_contract_item_growth_rebate_rate r (cost=155,337.09..155,387.09 rows=5,000 width=97) (actual time=22,134.616..22,134.788 rows=1,776 loops=1)

  • Output: r.contract_item_id, r.source_row_id, r.product_id, r.company_id, r.rate, r.explicit_zero_rate_ind
  • Function Call: gosimple.calculate_contract_item_growth_rebate_rate($0)
  • Buffers: shared hit=1186785 read=154 dirtied=8, local hit=7494744 read=1723491 dirtied=658668 written=658668, temp read=32273 written=46796
  • I/O Timings: read=5690.231
2.          

Initplan (for Function Scan)

3. 0.625 774.444 ↑ 1.0 1 1

Aggregate (cost=155,334.58..155,334.59 rows=1 width=32) (actual time=774.443..774.444 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=101069
  • I/O Timings: read=360.817
4. 252.213 773.819 ↑ 3.1 1,776 1

Hash Join (cost=79.00..155,320.69 rows=5,554 width=86) (actual time=267.811..773.819 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=101069
  • I/O Timings: read=360.817
5. 521.080 521.080 ↑ 3.1 1,373,074 1

Seq Scan on pg_temp_93._sales_potential_earnings s (cost=0.00..143,961.75 rows=4,289,275 width=102) (actual time=149.006..521.080 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=101069
  • I/O Timings: read=360.817
6. 0.008 0.526 ↑ 1.0 3 1

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

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

Seq Scan on client_lactalis.contract_family cf2 (cost=0.00..78.96 rows=3 width=16) (actual time=0.060..0.518 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
Planning time : 0.264 ms
Execution time : 22,134.959 ms