explain.depesz.com

PostgreSQL's explain analyze made readable

Result: r659

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 25,938.444 ↓ 0.0 0 1

Update on pg_temp_93._sales_potential_earnings (cost=311,439.59..487,571.93 rows=1 width=358) (actual time=25,938.444..25,938.444 rows=0 loops=1)

  • Buffers: shared hit=1187949 read=80 dirtied=9, local hit=7499452 read=1927764 dirtied=659084 written=658994, temp read=32269 written=46792
  • I/O Timings: read=7232.946
2.          

Initplan (for Update)

3. 0.610 853.457 ↑ 1.0 1 1

Aggregate (cost=155,334.58..155,334.59 rows=1 width=32) (actual time=853.457..853.457 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 hit=1 read=101068 written=44
  • I/O Timings: read=374.704
4. 326.091 852.847 ↑ 3.1 1,776 1

Hash Join (cost=79.00..155,320.69 rows=5,554 width=86) (actual time=267.813..852.847 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 hit=1 read=101068 written=44
  • I/O Timings: read=374.704
5. 526.388 526.388 ↑ 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=136.288..526.388 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=101068 written=44
  • I/O Timings: read=374.704
6. 0.004 0.368 ↑ 1.0 3 1

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

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

Seq Scan on client_lactalis.contract_family cf2 (cost=0.00..78.96 rows=3 width=16) (actual time=0.043..0.364 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. 1.865 25,935.483 ↓ 1,776.0 1,776 1

Nested Loop (cost=156,105.00..332,237.35 rows=1 width=358) (actual time=25,276.695..25,935.483 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 * 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.*
  • Buffers: shared hit=1187949 read=80 dirtied=9, local hit=7495833 read=1927715 dirtied=658992 written=658994, temp read=32269 written=46792
  • I/O Timings: read=7232.368
9. 191.965 25,933.618 ↓ 1,776.0 1,776 1

Hash Join (cost=156,105.00..332,236.32 rows=1 width=357) (actual time=25,276.673..25,933.618 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, _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, 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=1186173 read=80 dirtied=9, local hit=7495833 read=1927715 dirtied=658992 written=658994, temp read=32269 written=46792
  • I/O Timings: read=7232.368
10. 715.057 715.057 ↑ 3.1 1,373,074 1

Seq Scan on pg_temp_93._sales_potential_earnings (cost=0.00..143,961.75 rows=4,289,275 width=220) (actual time=131.429..715.057 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 hit=1 read=101068 written=2
  • I/O Timings: read=349.698
11. 0.674 25,026.596 ↓ 1,776.0 1,776 1

Hash (cost=156,104.99..156,104.99 rows=1 width=201) (actual time=25,026.596..25,026.596 rows=1,776 loops=1)

  • Output: x.requested_quantity, x.ctid, x.sale_id, x.contract_item_id, cf.ctid, r.rate, r.*, r.source_row_id, r.contract_item_id
  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 374kB
  • Buffers: shared hit=1186173 read=80 dirtied=9, local hit=7495832 read=1826647 dirtied=658992 written=658992, temp read=32269 written=46792
  • I/O Timings: read=6882.669
12. 1.143 25,025.922 ↓ 1,776.0 1,776 1

Merge Join (cost=156,025.82..156,104.99 rows=1 width=201) (actual time=25,024.294..25,025.922 rows=1,776 loops=1)

  • Output: x.requested_quantity, x.ctid, x.sale_id, x.contract_item_id, cf.ctid, r.rate, r.*, r.source_row_id, r.contract_item_id
  • Merge Cond: ((x.sale_id = r.source_row_id) AND (x.contract_item_id = r.contract_item_id))
  • Buffers: shared hit=1186173 read=80 dirtied=9, local hit=7495832 read=1826647 dirtied=658992 written=658992, temp read=32269 written=46792
  • I/O Timings: read=6882.669
13. 0.915 807.011 ↑ 3.1 1,776 1

Sort (cost=155,666.13..155,680.01 rows=5,554 width=49) (actual time=806.736..807.011 rows=1,776 loops=1)

  • Output: x.requested_quantity, x.ctid, x.sale_id, x.contract_item_id, cf.ctid
  • Sort Key: x.sale_id, x.contract_item_id
  • Sort Method: quicksort Memory: 298kB
  • Buffers: shared hit=50, local hit=1 read=101068 dirtied=44
  • I/O Timings: read=344.679
14. 138.354 806.096 ↑ 3.1 1,776 1

Hash Join (cost=79.00..155,320.69 rows=5,554 width=49) (actual time=242.475..806.096 rows=1,776 loops=1)

  • Output: x.requested_quantity, x.ctid, x.sale_id, x.contract_item_id, cf.ctid
  • Inner Unique: true
  • Hash Cond: (x.contract_family_id = cf.contract_family_id)
  • Buffers: shared hit=50, local hit=1 read=101068 dirtied=44
  • I/O Timings: read=344.679
15. 667.377 667.377 ↑ 3.1 1,373,074 1

Seq Scan on pg_temp_93._sales_potential_earnings x (cost=0.00..143,961.75 rows=4,289,275 width=59) (actual time=130.384..667.377 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=101068 dirtied=44
  • I/O Timings: read=344.679
16. 0.004 0.365 ↑ 1.0 3 1

Hash (cost=78.96..78.96 rows=3 width=22) (actual time=0.364..0.365 rows=3 loops=1)

  • Output: cf.ctid, cf.contract_family_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=50
17. 0.361 0.361 ↑ 1.0 3 1

Seq Scan on client_lactalis.contract_family cf (cost=0.00..78.96 rows=3 width=22) (actual time=0.043..0.361 rows=3 loops=1)

  • Output: cf.ctid, cf.contract_family_id
  • Filter: (cf.contract_type = ANY ('{GROWTH_STANDARD,GROWTH_AUTOPAY}'::enum.contract_type[]))
  • Rows Removed by Filter: 2408
  • Buffers: shared hit=50
18. 0.973 24,217.768 ↑ 2.8 1,776 1

Sort (cost=359.69..372.19 rows=5,000 width=152) (actual time=24,217.547..24,217.768 rows=1,776 loops=1)

  • Output: r.rate, r.*, r.source_row_id, r.contract_item_id
  • Sort Key: r.source_row_id, r.contract_item_id
  • Sort Method: quicksort Memory: 520kB
  • Buffers: shared hit=1186123 read=80 dirtied=9, local hit=7495831 read=1725579 dirtied=658948 written=658992, temp read=32269 written=46792
  • I/O Timings: read=6537.990
19. 24,216.795 24,216.795 ↑ 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=24,216.073..24,216.795 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=1186123 read=80 dirtied=9, local hit=7495831 read=1725579 dirtied=658948 written=658992, temp read=32269 written=46792
  • I/O Timings: read=6537.990
20. 0.000 0.000 ↑ 1.0 1 1,776

Seq Scan on client_lactalis.client_settings cs (cost=0.00..1.01 rows=1 width=6) (actual time=0.000..0.000 rows=1 loops=1,776)

  • Output: cs.ctid
  • Buffers: shared hit=1776
Planning time : 1.406 ms
Execution time : 25,938.709 ms