explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 32Ar

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.004 3,161.242 ↑ 1.0 1 1

Aggregate (cost=455,353.85..455,353.86 rows=1 width=8) (actual time=3,161.242..3,161.242 rows=1 loops=1)

2. 0.017 3,161.238 ↑ 129.1 43 1

Sort (cost=455,270.58..455,284.46 rows=5,551 width=1,320) (actual time=3,161.237..3,161.238 rows=43 loops=1)

  • Sort Key: umm.received_date DESC
  • Sort Method: quicksort Memory: 27kB
3. 35.800 3,161.221 ↑ 129.1 43 1

Hash Right Join (cost=442,725.79..451,681.34 rows=5,551 width=1,320) (actual time=2,787.344..3,161.221 rows=43 loops=1)

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

CTE user_mail_message_detail_tmp

5. 96.836 1,506.676 ↓ 1.6 2,129,228 1

Append (cost=1.72..192,375.15 rows=1,299,142 width=18) (actual time=0.130..1,506.676 rows=2,129,228 loops=1)

6. 65.711 226.543 ↑ 44.1 7,779 1

Merge Join (cost=1.72..58,034.71 rows=342,992 width=18) (actual time=0.130..226.543 rows=7,779 loops=1)

  • Merge Cond: (umcon.user_mail_message_id = umcan.user_mail_message_id)
7. 51.213 51.213 ↑ 1.0 340,300 1

Index Only Scan using user_mail_contact_pkey on user_mail_contact umcon (cost=0.42..14,160.73 rows=340,300 width=8) (actual time=0.013..51.213 rows=340,300 loops=1)

  • Heap Fetches: 340,300
8. 109.619 109.619 ↓ 1.0 906,324 1

Index Scan using user_mail_candidate_pkey on user_mail_candidate umcan (cost=0.42..37,335.90 rows=904,942 width=10) (actual time=0.006..109.619 rows=906,324 loops=1)

9. 115.168 765.648 ↓ 13.6 898,954 1

Gather (cost=14,920.08..60,667.08 rows=66,272 width=18) (actual time=215.851..765.648 rows=898,954 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
10. 67.480 650.480 ↓ 10.9 299,651 3 / 3

Hash Left Join (cost=13,920.08..53,039.88 rows=27,613 width=18) (actual time=230.221..650.480 rows=299,651 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,466
11. 275.550 544.570 ↑ 1.2 302,117 3 / 3

Hash Left Join (cost=11,154.75..49,284.69 rows=377,059 width=14) (actual time=191.461..544.570 rows=302,117 loops=3)

  • Hash Cond: (umcan_1.user_mail_message_id = umcon_1.user_mail_message_id)
12. 79.679 79.679 ↑ 1.3 301,647 3 / 3

Parallel Seq Scan on user_mail_candidate umcan_1 (cost=0.00..9,534.59 rows=377,059 width=10) (actual time=0.019..79.679 rows=301,647 loops=3)

13. 103.404 189.341 ↑ 1.0 340,300 3 / 3

Hash (cost=5,571.00..5,571.00 rows=340,300 width=8) (actual time=189.341..189.341 rows=340,300 loops=3)

  • Buckets: 131,072 Batches: 8 Memory Usage: 2,680kB
14. 85.937 85.937 ↑ 1.0 340,300 3 / 3

Seq Scan on user_mail_contact umcon_1 (cost=0.00..5,571.00 rows=340,300 width=8) (actual time=0.412..85.937 rows=340,300 loops=3)

15. 15.154 38.430 ↑ 1.0 53,348 3 / 3

Hash (cost=2,098.48..2,098.48 rows=53,348 width=12) (actual time=38.430..38.430 rows=53,348 loops=3)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,434kB
16. 23.276 23.276 ↑ 1.0 53,348 3 / 3

Seq Scan on contact con_1 (cost=0.00..2,098.48 rows=53,348 width=12) (actual time=0.474..23.276 rows=53,348 loops=3)

17. 20.456 300.366 ↓ 332,618.0 332,618 1

Gather (cost=1,001.72..55,870.94 rows=1 width=18) (actual time=0.979..300.366 rows=332,618 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
18. 75.329 279.910 ↓ 166,309.0 166,309 2 / 2

Merge Left Join (cost=1.72..54,870.84 rows=1 width=18) (actual time=7.186..279.910 rows=166,309 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,890
19. 34.118 34.118 ↑ 1.2 170,150 2 / 2

Parallel Index Scan using user_mail_contact_pkey on user_mail_contact umcon_2 (cost=0.42..12,759.50 rows=200,176 width=10) (actual time=0.021..34.118 rows=170,150 loops=2)

20. 170.463 170.463 ↑ 1.0 902,503 2 / 2

Index Only Scan using user_mail_candidate_pkey on user_mail_candidate umcan_2 (cost=0.42..37,335.90 rows=904,942 width=8) (actual time=0.073..170.463 rows=902,503 loops=2)

  • Heap Fetches: 898,880
21. 117.283 117.283 ↑ 1.0 889,877 1

Seq Scan on user_mail_others umo (cost=0.00..13,709.77 rows=889,877 width=18) (actual time=0.056..117.283 rows=889,877 loops=1)

22.          

CTE attach_files_tmp

23. 144.784 666.063 ↓ 1.4 526,894 1

GroupAggregate (cost=135,734.63..146,962.95 rows=376,656 width=12) (actual time=439.369..666.063 rows=526,894 loops=1)

  • Group Key: uma.user_mail_message_id
24. 351.148 521.279 ↑ 1.0 994,902 1

Sort (cost=135,734.63..138,221.88 rows=994,902 width=4) (actual time=439.364..521.279 rows=994,902 loops=1)

  • Sort Key: uma.user_mail_message_id
  • Sort Method: external merge Disk: 13,624kB
25. 170.131 170.131 ↑ 1.0 994,902 1

Seq Scan on user_mail_attachment uma (cost=0.00..23,017.02 rows=994,902 width=4) (actual time=0.040..170.131 rows=994,902 loops=1)

26. 779.316 779.316 ↓ 1.4 526,894 1

CTE Scan on attach_files_tmp (cost=0.00..7,533.12 rows=376,656 width=4) (actual time=439.371..779.316 rows=526,894 loops=1)

27. 0.048 2,346.105 ↑ 129.1 43 1

Hash (cost=103,318.31..103,318.31 rows=5,551 width=12) (actual time=2,346.105..2,346.105 rows=43 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 66kB
28. 0.079 2,346.057 ↑ 129.1 43 1

Nested Loop (cost=2,766.47..103,318.31 rows=5,551 width=12) (actual time=550.091..2,346.057 rows=43 loops=1)

29. 0.094 2,345.763 ↑ 129.1 43 1

Nested Loop Left Join (cost=2,766.04..64,912.19 rows=5,551 width=4) (actual time=550.076..2,345.763 rows=43 loops=1)

  • Filter: (can_con.deleted_timestamp IS NULL)
30. 0.238 2,345.669 ↑ 139.3 43 1

Nested Loop Left Join (cost=2,765.75..62,849.84 rows=5,990 width=8) (actual time=550.067..2,345.669 rows=43 loops=1)

31. 0.384 2,344.958 ↑ 139.3 43 1

Hash Left Join (cost=2,765.33..35,260.85 rows=5,990 width=8) (actual time=550.039..2,344.958 rows=43 loops=1)

  • Hash Cond: (ummd.contact_id = con.id)
  • Filter: (con.deleted_timestamp IS NULL)
32. 2,311.342 2,311.342 ↑ 150.3 43 1

CTE Scan on user_mail_message_detail_tmp ummd (cost=0.00..32,478.55 rows=6,463 width=12) (actual time=516.480..2,311.342 rows=43 loops=1)

  • Filter: ((status <> '-1'::integer) AND (COALESCE(candidate_id, 0) = 60,498))
  • Rows Removed by Filter: 2,129,185
33. 15.563 33.232 ↑ 1.0 53,348 1

Hash (cost=2,098.48..2,098.48 rows=53,348 width=12) (actual time=33.232..33.232 rows=53,348 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,434kB
34. 17.669 17.669 ↑ 1.0 53,348 1

Seq Scan on contact con (cost=0.00..2,098.48 rows=53,348 width=12) (actual time=0.019..17.669 rows=53,348 loops=1)

35. 0.473 0.473 ↑ 1.0 1 43

Index Scan using candidate_pkey on candidate can (cost=0.42..4.61 rows=1 width=12) (actual time=0.011..0.011 rows=1 loops=43)

  • Index Cond: (id = ummd.candidate_id)
36. 0.000 0.000 ↓ 0.0 0 43

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=43)

  • Index Cond: (can.contact_id = id)
37. 0.215 0.215 ↑ 1.0 1 43

Index Scan using user_mail_message_pkey on user_mail_message umm (cost=0.43..6.92 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=43)

  • Index Cond: (id = ummd.user_mail_message_id)
Planning time : 2.312 ms
Execution time : 3,173.766 ms