explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yLoQ

Settings
# exclusive inclusive rows x rows loops node
1. 7,838.728 46,710.030 ↑ 1.7 1,984,308 1

WindowAgg (cost=2,210,028.18..2,321,991.43 rows=3,445,023 width=312) (actual time=36,995.074..46,710.030 rows=1,984,308 loops=1)

  • Output: gen_random_uuid(), se.sale_number, s.client_id, se.contract_family_id, (sum(se.amount)), NULL::uuid, false, first_value(se.sale_id) OVER (?), first_value(se.contract_item_id) OVER (?), se.claimant_company_id, se.currency_conversion_ind, (sum(se.tax_amount)), (sum(se.requested_quantity)), (sum(se.allowed_quantity)), se.sale_id, se.contract_item_id, se.created, (lower(s.active_range))
  • Buffers: shared hit=18 read=181799, local read=7 written=7, temp read=215230 written=215388
  • I/O Timings: read=3659.907
2. 8,957.507 38,871.302 ↑ 1.7 1,984,308 1

Sort (cost=2,210,028.18..2,218,640.74 rows=3,445,023 width=247) (actual time=36,995.009..38,871.302 rows=1,984,308 loops=1)

  • Output: se.sale_number, s.client_id, se.contract_family_id, se.claimant_company_id, se.currency_conversion_ind, se.sale_id, se.contract_item_id, se.created, (lower(s.active_range)), (sum(se.amount)), (sum(se.tax_amount)), (sum(se.requested_quantity)), (sum(se.allowed_quantity))
  • Sort Key: se.sale_number, se.contract_family_id, se.created DESC, (lower(s.active_range)) DESC
  • Sort Method: external merge Disk: 299368kB
  • Buffers: shared hit=18 read=181799, local read=7 written=7, temp read=215230 written=215388
  • I/O Timings: read=3659.907
3. 3,582.123 29,913.795 ↑ 1.7 1,984,308 1

GroupAggregate (cost=1,123,140.34..1,321,229.17 rows=3,445,023 width=247) (actual time=23,336.402..29,913.795 rows=1,984,308 loops=1)

  • Output: se.sale_number, s.client_id, se.contract_family_id, se.claimant_company_id, se.currency_conversion_ind, se.sale_id, se.contract_item_id, se.created, (lower(s.active_range)), sum(se.amount), sum(se.tax_amount), sum(se.requested_quantity), sum(se.allowed_quantity)
  • Group Key: se.contract_family_id, se.sale_number, se.sale_id, se.contract_item_id, se.created, (lower(s.active_range)), s.client_id, se.claimant_company_id, se.currency_conversion_ind
  • Buffers: shared hit=9 read=181799, local read=7 written=7, temp read=155002 written=155090
  • I/O Timings: read=3659.907
4. 14,904.278 26,331.672 ↑ 1.2 2,842,114 1

Sort (cost=1,123,140.34..1,131,752.90 rows=3,445,023 width=139) (actual time=23,336.380..26,331.672 rows=2,842,114 loops=1)

  • Output: se.sale_number, s.client_id, se.contract_family_id, se.claimant_company_id, se.currency_conversion_ind, se.sale_id, se.contract_item_id, se.created, (lower(s.active_range)), se.amount, se.tax_amount, se.requested_quantity, se.allowed_quantity
  • Sort Key: se.contract_family_id, se.sale_number, se.sale_id, se.contract_item_id, se.created, (lower(s.active_range)), s.client_id, se.claimant_company_id, se.currency_conversion_ind
  • Sort Method: external merge Disk: 435248kB
  • Buffers: shared hit=9 read=181799, local read=7 written=7, temp read=155002 written=155090
  • I/O Timings: read=3659.907
5. 1,170.131 11,427.394 ↑ 1.2 2,842,114 1

Hash Join (cost=44,855.24..431,153.33 rows=3,445,023 width=139) (actual time=1,592.887..11,427.394 rows=2,842,114 loops=1)

  • Output: se.sale_number, s.client_id, se.contract_family_id, se.claimant_company_id, se.currency_conversion_ind, se.sale_id, se.contract_item_id, se.created, lower(s.active_range), se.amount, se.tax_amount, se.requested_quantity, se.allowed_quantity
  • Hash Cond: (se.contract_family_id = xcf.contract_family_id)
  • Buffers: shared hit=4 read=181799, local read=7 written=7, temp read=54030 written=54016
  • I/O Timings: read=3659.907
6. 4,548.010 10,255.417 ↑ 1.0 3,321,081 1

Hash Join (cost=44,819.35..375,135.82 rows=3,445,023 width=145) (actual time=1,591.013..10,255.417 rows=3,321,081 loops=1)

  • Output: se.sale_number, se.contract_family_id, se.amount, se.sale_id, se.contract_item_id, se.claimant_company_id, se.currency_conversion_ind, se.tax_amount, se.requested_quantity, se.allowed_quantity, se.created, s.client_id, s.active_range
  • Inner Unique: true
  • Hash Cond: (se.sale_id = s.sale_id)
  • Buffers: shared hit=4 read=181799, temp read=54030 written=54016
  • I/O Timings: read=3659.469
7. 4,117.015 4,117.015 ↑ 1.0 3,321,081 1

Seq Scan on client_lactalis.sale_earning se (cost=0.00..193,858.23 rows=3,445,023 width=115) (actual time=0.324..4,117.015 rows=3,321,081 loops=1)

  • Output: se.sale_number, se.contract_family_id, se.amount, se.sale_id, se.contract_item_id, se.claimant_company_id, se.currency_conversion_ind, se.tax_amount, se.requested_quantity, se.allowed_quantity, se.created
  • Buffers: shared hit=2 read=159406
  • I/O Timings: read=2981.145
8. 459.864 1,590.392 ↓ 1.0 716,770 1

Hash (cost=29,561.82..29,561.82 rows=716,682 width=46) (actual time=1,590.392..1,590.392 rows=716,770 loops=1)

  • Output: s.client_id, s.active_range, s.sale_id
  • Buckets: 131072 Batches: 8 Memory Usage: 7903kB
  • Buffers: shared hit=2 read=22393, temp written=5054
  • I/O Timings: read=678.323
9. 1,130.528 1,130.528 ↓ 1.0 716,770 1

Seq Scan on client_lactalis.sale s (cost=0.00..29,561.82 rows=716,682 width=46) (actual time=0.009..1,130.528 rows=716,770 loops=1)

  • Output: s.client_id, s.active_range, s.sale_id
  • Buffers: shared hit=2 read=22393
  • I/O Timings: read=678.323
10. 0.575 1.846 ↑ 1.0 1,284 1

Hash (cost=19.84..19.84 rows=1,284 width=16) (actual time=1.846..1.846 rows=1,284 loops=1)

  • Output: xcf.contract_family_id
  • Buckets: 2048 Batches: 1 Memory Usage: 77kB
  • Buffers: local read=7 written=7
  • I/O Timings: read=0.438
11. 1.271 1.271 ↑ 1.0 1,284 1

Seq Scan on pg_temp_93._contract_family xcf (cost=0.00..19.84 rows=1,284 width=16) (actual time=0.727..1.271 rows=1,284 loops=1)

  • Output: xcf.contract_family_id
  • Buffers: local read=7 written=7
  • I/O Timings: read=0.438
Planning time : 23.707 ms
Execution time : 47,013.743 ms