explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ROn7 : Optimization for: Optimization for: plan #CfYm; plan #JrzP

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.050 3,143.736 ↑ 1.0 1 1

Aggregate (cost=354,023.41..354,023.42 rows=1 width=8) (actual time=3,143.736..3,143.736 rows=1 loops=1)

2. 0.357 3,143.686 ↓ 80.0 1,200 1

Sort (cost=354,023.18..354,023.22 rows=15 width=1,320) (actual time=3,143.651..3,143.686 rows=1,200 loops=1)

  • Sort Key: umm.received_date DESC
  • Sort Method: quicksort Memory: 105kB
3. 0.461 3,143.329 ↓ 80.0 1,200 1

Nested Loop Left Join (cost=345,493.16..354,022.74 rows=15 width=1,320) (actual time=2,685.536..3,143.329 rows=1,200 loops=1)

  • Filter: (can_con.deleted_timestamp IS NULL)
4.          

CTE user_mail_message_detail_tmp

5. 87.794 1,393.689 ↓ 1.7 2,055,397 1

Append (cost=1.72..180,230.25 rows=1,207,180 width=18) (actual time=0.122..1,393.689 rows=2,055,397 loops=1)

6. 60.546 215.811 ↑ 42.8 7,546 1

Merge Join (cost=1.72..55,945.17 rows=323,334 width=18) (actual time=0.122..215.811 rows=7,546 loops=1)

  • Merge Cond: (umcon.user_mail_message_id = umcan.user_mail_message_id)
7. 47.542 47.542 ↑ 1.0 321,481 1

Index Only Scan using user_mail_contact_pkey on user_mail_contact umcon (cost=0.42..13,432.75 rows=321,482 width=8) (actual time=0.009..47.542 rows=321,481 loops=1)

  • Heap Fetches: 321,481
8. 107.723 107.723 ↓ 1.0 880,314 1

Index Scan using user_mail_candidate_pkey on user_mail_candidate umcan (cost=0.42..36,278.03 rows=879,010 width=10) (actual time=0.005..107.723 rows=880,314 loops=1)

9. 186.467 710.958 ↓ 37.7 873,150 1

Gather (cost=14,277.15..53,600.89 rows=23,177 width=18) (actual time=186.449..710.958 rows=873,150 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
10. 59.003 524.491 ↓ 30.1 291,050 3 / 3

Hash Left Join (cost=13,277.15..50,283.19 rows=9,657 width=18) (actual time=180.454..524.491 rows=291,050 loops=3)

  • Hash Cond: (umcon_1.contact_id = con_1.id)
  • Filter: ((umcon_1.contact_id IS NULL) OR (con_1.deleted_timestamp IS NOT NULL))
  • Rows Removed by Filter: 2,388
11. 228.990 437.949 ↑ 1.2 293,438 3 / 3

Hash Left Join (cost=10,537.34..46,581.89 rows=366,254 width=14) (actual time=152.625..437.949 rows=293,438 loops=3)

  • Hash Cond: (umcan_1.user_mail_message_id = umcon_1.user_mail_message_id)
12. 58.391 58.391 ↑ 1.3 293,003 3 / 3

Parallel Seq Scan on user_mail_candidate umcan_1 (cost=0.00..9,261.54 rows=366,254 width=10) (actual time=0.019..58.391 rows=293,003 loops=3)

13. 82.889 150.568 ↑ 1.0 321,482 3 / 3

Hash (cost=5,262.82..5,262.82 rows=321,482 width=8) (actual time=150.568..150.568 rows=321,482 loops=3)

  • Buckets: 131,072 Batches: 8 Memory Usage: 2,590kB
14. 67.679 67.679 ↑ 1.0 321,482 3 / 3

Seq Scan on user_mail_contact umcon_1 (cost=0.00..5,262.82 rows=321,482 width=8) (actual time=0.500..67.679 rows=321,482 loops=3)

15. 10.488 27.539 ↑ 1.0 52,880 3 / 3

Hash (cost=2,078.80..2,078.80 rows=52,880 width=12) (actual time=27.539..27.539 rows=52,880 loops=3)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,388kB
16. 17.051 17.051 ↑ 1.0 52,880 3 / 3

Seq Scan on contact con_1 (cost=0.00..2,078.80 rows=52,880 width=12) (actual time=0.613..17.051 rows=52,880 loops=3)

17. 17.803 273.670 ↓ 314,033.0 314,033 1

Gather (cost=1,001.72..53,959.38 rows=1 width=18) (actual time=0.895..273.670 rows=314,033 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
18. 67.750 255.867 ↓ 157,017.0 157,017 2 / 2

Merge Left Join (cost=1.72..52,959.28 rows=1 width=18) (actual time=10.541..255.867 rows=157,017 loops=2)

  • Merge Cond: (umcon_2.user_mail_message_id = umcan_2.user_mail_message_id)
  • Filter: (umcan_2.candidate_id IS NULL)
  • Rows Removed by Filter: 3,773
19. 30.804 30.804 ↑ 1.2 160,741 2 / 2

Parallel Index Scan using user_mail_contact_pkey on user_mail_contact umcon_2 (cost=0.42..12,109.00 rows=189,107 width=10) (actual time=0.024..30.804 rows=160,741 loops=2)

20. 157.313 157.313 ↑ 1.0 873,504 2 / 2

Index Only Scan using user_mail_candidate_pkey on user_mail_candidate umcan_2 (cost=0.42..36,278.03 rows=879,010 width=8) (actual time=0.105..157.313 rows=873,504 loops=2)

  • Heap Fetches: 866,847
21. 105.456 105.456 ↑ 1.0 860,668 1

Seq Scan on user_mail_others umo (cost=0.00..13,259.68 rows=860,668 width=18) (actual time=0.069..105.456 rows=860,668 loops=1)

22.          

CTE attach_files_tmp

23. 171.002 677.208 ↓ 1.4 493,275 1

GroupAggregate (cost=126,444.50..137,004.71 rows=358,395 width=12) (actual time=405.095..677.208 rows=493,275 loops=1)

  • Group Key: uma.user_mail_message_id
24. 353.691 506.206 ↑ 1.0 930,168 1

Sort (cost=126,444.50..128,769.92 rows=930,168 width=4) (actual time=405.089..506.206 rows=930,168 loops=1)

  • Sort Key: uma.user_mail_message_id
  • Sort Method: external merge Disk: 12,736kB
25. 152.515 152.515 ↑ 1.0 930,168 1

Seq Scan on user_mail_attachment uma (cost=0.00..21,512.68 rows=930,168 width=4) (actual time=0.029..152.515 rows=930,168 loops=1)

26. 0.673 3,142.868 ↓ 80.0 1,200 1

Nested Loop Left Join (cost=28,257.92..36,782.61 rows=15 width=12) (actual time=2,685.532..3,142.868 rows=1,200 loops=1)

27. 49.551 3,140.995 ↓ 80.0 1,200 1

Hash Right Join (cost=28,257.50..36,769.41 rows=15 width=12) (actual time=2,685.511..3,140.995 rows=1,200 loops=1)

  • Hash Cond: (attach_files_tmp.user_mail_message_id = umm.id)
28. 819.222 819.222 ↓ 1.4 493,275 1

CTE Scan on attach_files_tmp (cost=0.00..7,167.90 rows=358,395 width=4) (actual time=405.097..819.222 rows=493,275 loops=1)

29. 0.407 2,272.222 ↓ 80.0 1,200 1

Hash (cost=28,257.31..28,257.31 rows=15 width=16) (actual time=2,272.222..2,272.222 rows=1,200 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 73kB
30. 0.344 2,271.815 ↓ 80.0 1,200 1

Nested Loop (cost=11.34..28,257.31 rows=15 width=16) (actual time=86.101..2,271.815 rows=1,200 loops=1)

31. 132.999 2,267.871 ↓ 80.0 1,200 1

Hash Join (cost=10.91..28,228.83 rows=15 width=8) (actual time=86.085..2,267.871 rows=1,200 loops=1)

  • Hash Cond: (ummd.contact_id = con.id)
32. 2,134.844 2,134.844 ↓ 5.1 2,054,002 1

CTE Scan on user_mail_message_detail_tmp ummd (cost=0.00..27,161.55 rows=402,393 width=12) (actual time=0.152..2,134.844 rows=2,054,002 loops=1)

  • Filter: (status > '-1'::integer)
  • Rows Removed by Filter: 1,395
33. 0.004 0.028 ↓ 10.5 21 1

Hash (cost=10.89..10.89 rows=2 width=4) (actual time=0.028..0.028 rows=21 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
34. 0.024 0.024 ↓ 10.5 21 1

Index Scan using index_contact_company on contact con (cost=0.29..10.89 rows=2 width=4) (actual time=0.009..0.024 rows=21 loops=1)

  • Index Cond: (company_id = 14,699)
  • Filter: (deleted_timestamp IS NULL)
  • Rows Removed by Filter: 1
35. 3.600 3.600 ↑ 1.0 1 1,200

Index Scan using user_mail_message_pkey on user_mail_message umm (cost=0.43..1.90 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=1,200)

  • Index Cond: (id = ummd.user_mail_message_id)
36. 1.200 1.200 ↓ 0.0 0 1,200

Index Scan using candidate_pkey on candidate can (cost=0.42..0.88 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=1,200)

  • Index Cond: (id = ummd.candidate_id)
37. 0.000 0.000 ↓ 0.0 0 1,200

Index Scan using contact__pkey on contact can_con (cost=0.29..0.33 rows=1 width=12) (actual time=0.000..0.000 rows=0 loops=1,200)

  • Index Cond: (can.contact_id = id)
Planning time : 2.090 ms
Execution time : 3,156.896 ms