explain.depesz.com

PostgreSQL's explain analyze made readable

Result: milW

Settings
# exclusive inclusive rows x rows loops node
1. 7,503.948 68,512.779 ↓ 132.0 1,479,356 1

GroupAggregate (cost=1,204,380.13..1,204,968.45 rows=11,206 width=264) (actual time=56,277.403..68,512.779 rows=1,479,356 loops=1)

  • Group Key: se.contract_family_id, se.sale_number, xee.contract_item_id, xee.sale_id, s.client_id, se.claimant_company_id, se.currency_conversion_ind
  • Buffers: shared hit=13460050 read=529528 written=320, local read=7, temp read=523439 written=523518
2. 16,518.031 61,008.831 ↓ 253.6 2,842,114 1

Sort (cost=1,204,380.13..1,204,408.15 rows=11,206 width=123) (actual time=56,277.332..61,008.831 rows=2,842,114 loops=1)

  • Sort Key: se.contract_family_id, se.sale_number, xee.contract_item_id, xee.sale_id, s.client_id, se.claimant_company_id, se.currency_conversion_ind
  • Sort Method: external merge Disk: 376344kB
  • Buffers: shared hit=13460050 read=529528 written=320, local read=7, temp read=523439 written=523518
3. 1,177.332 44,490.800 ↓ 253.6 2,842,114 1

Hash Join (cost=972,841.21..1,203,626.42 rows=11,206 width=123) (actual time=31,597.252..44,490.8 rows=2,842,114 loops=1)

  • Buffers: shared hit=13460039 read=529528 written=320, local read=7, temp read=435554 written=435530
4. 313.949 43,312.691 ↓ 296.4 3,321,081 1

Gather (cost=972,805.32..1,203,436.45 rows=11,206 width=139) (actual time=31,596.46..43,312.691 rows=3,321,081 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=13460039 read=529528 written=320, temp read=435554 written=435530
5. 857.312 42,998.742 ↓ 237.1 1,107,027 3 / 3

Nested Loop (cost=971,805.32..1,201,315.85 rows=4,669 width=139) (actual time=31,402.043..42,998.742 rows=1,107,027 loops=3)

  • Buffers: shared hit=13460039 read=529528 written=320, temp read=435554 written=435530
6. 4,194.253 36,606.295 ↓ 237.1 1,107,027 3 / 3

Hash Join (cost=971,804.89..1,199,195.05 rows=4,669 width=139) (actual time=31,397.782..36,606.295 rows=1,107,027 loops=3)

  • Buffers: shared hit=180136 read=524987 written=320, temp read=435554 written=435530
7. 1,017.514 1,017.514 ↑ 1.3 1,107,027 3 / 3

Seq Scan on sale_earning se (cost=0..173,762.2 rows=1,435,420 width=91) (actual time=0.119..1,017.514 rows=1,107,027 loops=3)

  • Buffers: shared read=159408
8. 1,243.912 31,394.528 ↓ 5.6 1,943,188 3 / 3

Hash (cost=962,599.38..962,599.38 rows=344,501 width=70) (actual time=31,394.528..31,394.528 rows=1,943,188 loops=3)

  • Buffers: shared hit=180096 read=365579 written=320, temp read=309402 written=369534
9. 447.365 30,150.616 ↓ 5.6 1,943,188 3 / 3

Subquery Scan on xee (cost=933,316.8..962,599.38 rows=344,501 width=70) (actual time=22,214.548..30,150.616 rows=1,943,188 loops=3)

  • Buffers: shared hit=180096 read=365579 written=320, temp read=309402 written=309564
10. 692.425 29,703.251 ↓ 5.6 1,943,188 3 / 3

Unique (cost=933,316.8..959,154.37 rows=344,501 width=86) (actual time=22,214.545..29,703.251 rows=1,943,188 loops=3)

  • Buffers: shared hit=180096 read=365579 written=320, temp read=309402 written=309564
11. 19,287.719 29,010.826 ↑ 1.0 3,321,081 3 / 3

Sort (cost=933,316.8..941,929.32 rows=3,445,009 width=86) (actual time=22,214.543..29,010.826 rows=3,321,081 loops=3)

  • Sort Key: xee_1.sale_number, xee_1.contract_family_id, xee_1.created DESC, (lower(s_1.active_range)) DESC
  • Sort Method: external merge Disk: 321536kB
  • Buffers: shared hit=180096 read=365579 written=320, temp read=309402 written=309564
12. 4,372.187 9,723.107 ↑ 1.0 3,321,081 3 / 3

Hash Join (cost=43,420.59..347,306.41 rows=3,445,009 width=86) (actual time=1,467.725..9,723.107 rows=3,321,081 loops=3)

  • Buffers: shared hit=180043 read=365579 written=320, temp read=117834 written=117792
13. 3,884.297 3,884.297 ↑ 1.0 3,321,081 3 / 3

Seq Scan on sale_earning xee_1 (cost=0..193,858.09 rows=3,445,009 width=78) (actual time=0.021..3,884.297 rows=3,321,081 loops=3)

  • Buffers: shared hit=135038 read=343186
14. 393.405 1,466.623 ↓ 1.0 716,770 3 / 3

Hash (cost=29,561.93..29,561.93 rows=716,693 width=30) (actual time=1,466.623..1,466.623 rows=716,770 loops=3)

  • Buffers: shared hit=44792 read=22393 written=320, temp written=11487
15. 1,073.218 1,073.218 ↓ 1.0 716,770 3 / 3

Seq Scan on sale s_1 (cost=0..29,561.93 rows=716,693 width=30) (actual time=0.024..1,073.218 rows=716,770 loops=3)

  • Buffers: shared hit=44792 read=22393 written=320
16. 5,535.135 5,535.135 ↑ 1.0 1 3,321,081 / 3

Index Scan using sale_pkey on sale s (cost=0.42..0.45 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=3,321,081)

  • Index Cond: (s.sale_id = se.sale_id)
  • Buffers: shared hit=13279903 read=4541
17. 0.277 0.777 ↑ 1.0 1,284 1

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

  • Buffers: local read=7
18. 0.500 0.500 ↑ 1.0 1,284 1

Seq Scan on _contract_family xcf (cost=0..19.84 rows=1,284 width=16) (actual time=0.222..0.5 rows=1,284 loops=1)

  • Buffers: local read=7
Planning time : 28.175 ms
Execution time : 68,701.777 ms