explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CfYm

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.048 2,965.393 ↑ 1.0 1 1

Aggregate (cost=356,158.38..356,158.39 rows=1 width=8) (actual time=2,965.393..2,965.393 rows=1 loops=1)

2. 0.309 2,965.345 ↓ 27.3 1,200 1

Sort (cost=356,157.72..356,157.83 rows=44 width=1,320) (actual time=2,965.313..2,965.345 rows=1,200 loops=1)

  • Sort Key: umm.received_date DESC
  • Sort Method: quicksort Memory: 105kB
3. 38.392 2,965.036 ↓ 27.3 1,200 1

Hash Right Join (cost=347,644.11..356,156.08 rows=44 width=1,320) (actual time=2,611.695..2,965.036 rows=1,200 loops=1)

  • Hash Cond: (attach_files_tmp.user_mail_message_id = umm.id)
4.          

CTE user_mail_message_detail_tmp

5. 89.505 1,386.646 ↓ 1.7 2,055,397 1

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

6. 64.621 230.748 ↑ 42.8 7,546 1

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

  • Merge Cond: (umcon.user_mail_message_id = umcan.user_mail_message_id)
7. 52.870 52.870 ↑ 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.010..52.870 rows=321,481 loops=1)

  • Heap Fetches: 321,481
8. 113.257 113.257 ↓ 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..113.257 rows=880,314 loops=1)

9. 124.984 640.080 ↓ 37.7 873,150 1

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

  • Workers Planned: 2
  • Workers Launched: 2
10. 48.562 515.096 ↓ 30.1 291,050 3 / 3

Hash Left Join (cost=13,277.15..50,283.19 rows=9,657 width=18) (actual time=181.181..515.096 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. 225.492 429.751 ↑ 1.2 293,438 3 / 3

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

  • Hash Cond: (umcan_1.user_mail_message_id = umcon_1.user_mail_message_id)
12. 62.130 62.130 ↑ 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.016..62.130 rows=293,003 loops=3)

13. 82.430 142.129 ↑ 1.0 321,482 3 / 3

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

  • Buckets: 131,072 Batches: 8 Memory Usage: 2,590kB
14. 59.699 59.699 ↑ 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.453..59.699 rows=321,482 loops=3)

15. 12.224 36.783 ↑ 1.0 52,880 3 / 3

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

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,388kB
16. 24.559 24.559 ↑ 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=2.904..24.559 rows=52,880 loops=3)

17. 17.035 310.569 ↓ 314,033.0 314,033 1

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

  • Workers Planned: 1
  • Workers Launched: 1
18. 77.882 293.534 ↓ 157,017.0 157,017 2 / 2

Merge Left Join (cost=1.72..52,959.28 rows=1 width=18) (actual time=10.233..293.534 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. 34.790 34.790 ↑ 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.025..34.790 rows=160,741 loops=2)

20. 180.862 180.862 ↑ 1.0 877,006 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.103..180.862 rows=877,006 loops=2)

  • Heap Fetches: 873,896
21. 115.744 115.744 ↑ 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.099..115.744 rows=860,668 loops=1)

22.          

CTE attach_files_tmp

23. 136.558 596.995 ↓ 1.4 493,275 1

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

  • Group Key: uma.user_mail_message_id
24. 319.187 460.437 ↑ 1.0 930,168 1

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

  • Sort Key: uma.user_mail_message_id
  • Sort Method: external merge Disk: 12,736kB
25. 141.250 141.250 ↑ 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.033..141.250 rows=930,168 loops=1)

26. 704.057 704.057 ↓ 1.4 493,275 1

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

27. 0.374 2,222.587 ↓ 27.3 1,200 1

Hash (cost=30,408.61..30,408.61 rows=44 width=12) (actual time=2,222.587..2,222.587 rows=1,200 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 68kB
28. 0.543 2,222.213 ↓ 27.3 1,200 1

Nested Loop (cost=12.05..30,408.61 rows=44 width=12) (actual time=80.597..2,222.213 rows=1,200 loops=1)

29. 0.547 2,218.070 ↓ 27.3 1,200 1

Nested Loop Left Join (cost=11.62..30,367.61 rows=44 width=4) (actual time=80.563..2,218.070 rows=1,200 loops=1)

  • Filter: (can_con.deleted_timestamp IS NULL)
30. 0.582 2,217.523 ↓ 26.7 1,200 1

Nested Loop Left Join (cost=11.33..30,352.08 rows=45 width=8) (actual time=80.560..2,217.523 rows=1,200 loops=1)

31. 125.983 2,214.541 ↓ 26.7 1,200 1

Hash Join (cost=10.91..30,325.72 rows=45 width=8) (actual time=80.544..2,214.541 rows=1,200 loops=1)

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

CTE Scan on user_mail_message_detail_tmp ummd (cost=0.00..27,161.55 rows=1,201,144 width=12) (actual time=0.155..2,088.462 rows=2,054,002 loops=1)

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
34. 0.090 0.090 ↓ 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.073..0.090 rows=21 loops=1)

  • Index Cond: (company_id = 14,699)
  • Filter: (deleted_timestamp IS NULL)
  • Rows Removed by Filter: 1
35. 2.400 2.400 ↓ 0.0 0 1,200

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

  • Index Cond: (id = ummd.candidate_id)
36. 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)
37. 3.600 3.600 ↑ 1.0 1 1,200

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

  • Index Cond: (id = ummd.user_mail_message_id)
Planning time : 2.402 ms
Execution time : 2,977.989 ms