explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JrzP : Optimization for: plan #CfYm

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.050 2,963.414 ↑ 1.0 1 1

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

2. 0.466 2,963.364 ↓ 80.0 1,200 1

Sort (cost=354,023.18..354,023.22 rows=15 width=1,320) (actual time=2,963.312..2,963.364 rows=1,200 loops=1)

  • Sort Key: umm.received_date DESC
  • Sort Method: quicksort Memory: 105kB
3. 1.162 2,962.898 ↓ 80.0 1,200 1

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

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

CTE user_mail_message_detail_tmp

5. 88.419 1,316.694 ↓ 1.7 2,055,397 1

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

6. 57.667 213.842 ↑ 42.8 7,546 1

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

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

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

9. 115.849 598.197 ↓ 37.7 873,150 1

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

  • Workers Planned: 2
  • Workers Launched: 2
10. 51.695 482.348 ↓ 30.1 291,050 3 / 3

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

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

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

13. 70.233 125.489 ↑ 1.0 321,482 3 / 3

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

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

15. 9.429 24.970 ↑ 1.0 52,880 3 / 3

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

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

17. 17.245 302.465 ↓ 314,033.0 314,033 1

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

  • Workers Planned: 1
  • Workers Launched: 1
18. 77.327 285.220 ↓ 157,017.0 157,017 2 / 2

Merge Left Join (cost=1.72..52,959.28 rows=1 width=18) (actual time=7.456..285.220 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. 33.981 33.981 ↑ 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.023..33.981 rows=160,741 loops=2)

20. 173.912 173.912 ↑ 1.0 877,280 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.313..173.912 rows=877,280 loops=2)

  • Heap Fetches: 874,483
21. 113.771 113.771 ↑ 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.037..113.771 rows=860,668 loops=1)

22.          

CTE attach_files_tmp

23. 142.084 618.875 ↓ 1.4 493,275 1

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

  • Group Key: uma.user_mail_message_id
24. 321.419 476.791 ↑ 1.0 930,168 1

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

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

26. 0.150 2,960.536 ↓ 80.0 1,200 1

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

27. 39.121 2,957.986 ↓ 80.0 1,200 1

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

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

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

29. 0.388 2,191.258 ↓ 80.0 1,200 1

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

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

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

31. 129.612 2,178.367 ↓ 80.0 1,200 1

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

  • Hash Cond: (ummd.contact_id = con.id)
32. 2,048.719 2,048.719 ↓ 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.166..2,048.719 rows=2,054,002 loops=1)

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

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

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

  • Index Cond: (company_id = 14,699)
  • Filter: (deleted_timestamp IS NULL)
  • Rows Removed by Filter: 1
35. 12.000 12.000 ↑ 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.010..0.010 rows=1 loops=1,200)

  • Index Cond: (id = ummd.user_mail_message_id)
36. 2.400 2.400 ↓ 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.002..0.002 rows=0 loops=1,200)

  • Index Cond: (id = ummd.candidate_id)
37. 1.200 1.200 ↓ 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.001..0.001 rows=0 loops=1,200)

  • Index Cond: (can.contact_id = id)
Planning time : 2.172 ms
Execution time : 2,976.121 ms