explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VPxy : Optimization for: plan #32Ar

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.001 3,442.350 ↑ 2.5 4 1

Limit (cost=453,058.24..453,058.26 rows=10 width=326) (actual time=3,442.349..3,442.350 rows=4 loops=1)

2. 0.013 3,442.349 ↑ 1,387.8 4 1

Sort (cost=453,058.24..453,072.11 rows=5,551 width=326) (actual time=3,442.349..3,442.349 rows=4 loops=1)

  • Sort Key: umm.received_date DESC
  • Sort Method: quicksort Memory: 29kB
3. 37.758 3,442.336 ↑ 1,387.8 4 1

Hash Right Join (cost=443,635.79..452,882.77 rows=5,551 width=326) (actual time=3,066.142..3,442.336 rows=4 loops=1)

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

CTE user_mail_message_detail_tmp

5. 104.703 1,695.670 ↓ 1.6 2,129,228 1

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

6. 73.459 251.430 ↑ 44.1 7,779 1

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

  • Merge Cond: (umcon.user_mail_message_id = umcan.user_mail_message_id)
7. 55.535 55.535 ↑ 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.009..55.535 rows=340,300 loops=1)

  • Heap Fetches: 340,300
8. 122.436 122.436 ↓ 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.004..122.436 rows=906,324 loops=1)

9. 215.431 884.330 ↓ 13.6 898,954 1

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

  • Workers Planned: 2
  • Workers Launched: 2
10. 76.961 668.899 ↓ 10.9 299,651 3 / 3

Hash Left Join (cost=13,920.08..53,039.88 rows=27,613 width=18) (actual time=222.905..668.899 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. 291.548 554.456 ↑ 1.2 302,117 3 / 3

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

  • Hash Cond: (umcan_1.user_mail_message_id = umcon_1.user_mail_message_id)
12. 79.844 79.844 ↑ 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.022..79.844 rows=301,647 loops=3)

13. 99.002 183.064 ↑ 1.0 340,300 3 / 3

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

  • Buckets: 131,072 Batches: 8 Memory Usage: 2,680kB
14. 84.062 84.062 ↑ 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.368..84.062 rows=340,300 loops=3)

15. 14.083 37.482 ↑ 1.0 53,348 3 / 3

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

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,434kB
16. 23.399 23.399 ↑ 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.513..23.399 rows=53,348 loops=3)

17. 22.294 332.965 ↓ 332,618.0 332,618 1

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

  • Workers Planned: 1
  • Workers Launched: 1
18. 84.128 310.671 ↓ 166,309.0 166,309 2 / 2

Merge Left Join (cost=1.72..54,870.84 rows=1 width=18) (actual time=10.827..310.671 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. 37.355 37.355 ↑ 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.025..37.355 rows=170,150 loops=2)

20. 189.188 189.188 ↑ 1.0 903,606 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.090..189.188 rows=903,606 loops=2)

  • Heap Fetches: 901,132
21. 122.242 122.242 ↑ 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.098..122.242 rows=889,877 loops=1)

22.          

CTE attach_files_tmp

23. 155.219 659.609 ↓ 1.4 526,894 1

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

  • Group Key: uma.user_mail_message_id
24. 341.567 504.390 ↑ 1.0 994,902 1

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

  • Sort Key: uma.user_mail_message_id
  • Sort Method: external merge Disk: 13,624kB
25. 162.823 162.823 ↑ 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.070..162.823 rows=994,902 loops=1)

26. 778.043 778.043 ↓ 1.4 526,894 1

CTE Scan on attach_files_tmp (cost=0.00..7,533.12 rows=376,656 width=12) (actual time=417.690..778.043 rows=526,894 loops=1)

27. 0.016 2,626.535 ↑ 1,387.8 4 1

Hash (cost=104,228.31..104,228.31 rows=5,551 width=538) (actual time=2,626.535..2,626.535 rows=4 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 69kB
28. 0.009 2,626.519 ↑ 1,387.8 4 1

Nested Loop (cost=3,183.47..104,228.31 rows=5,551 width=538) (actual time=743.869..2,626.519 rows=4 loops=1)

29. 0.007 2,626.486 ↑ 1,387.8 4 1

Nested Loop Left Join (cost=3,183.04..65,822.19 rows=5,551 width=88) (actual time=743.862..2,626.486 rows=4 loops=1)

  • Filter: (can_con.deleted_timestamp IS NULL)
30. 0.023 2,626.475 ↑ 1,497.5 4 1

Nested Loop Left Join (cost=3,182.75..63,759.84 rows=5,990 width=66) (actual time=743.859..2,626.475 rows=4 loops=1)

31. 1.401 2,626.376 ↑ 1,497.5 4 1

Hash Left Join (cost=3,182.33..36,170.85 rows=5,990 width=44) (actual time=743.842..2,626.376 rows=4 loops=1)

  • Hash Cond: (ummd.contact_id = con.id)
  • Filter: (con.deleted_timestamp IS NULL)
32. 2,594.439 2,594.439 ↑ 1,615.8 4 1

CTE Scan on user_mail_message_detail_tmp ummd (cost=0.00..32,478.55 rows=6,463 width=18) (actual time=713.011..2,594.439 rows=4 loops=1)

  • Filter: ((status <> '-1'::integer) AND (COALESCE(candidate_id, 0) = 82,954))
  • Rows Removed by Filter: 2,129,224
33. 16.633 30.536 ↑ 1.0 53,348 1

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

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

Seq Scan on contact con (cost=0.00..2,098.48 rows=53,348 width=38) (actual time=0.016..13.903 rows=53,348 loops=1)

35. 0.076 0.076 ↑ 1.0 1 4

Index Scan using candidate_pkey on candidate can (cost=0.42..4.61 rows=1 width=30) (actual time=0.019..0.019 rows=1 loops=4)

  • Index Cond: (id = ummd.candidate_id)
36. 0.004 0.004 ↓ 0.0 0 4

Index Scan using contact__pkey on contact can_con (cost=0.29..0.33 rows=1 width=34) (actual time=0.001..0.001 rows=0 loops=4)

  • Index Cond: (can.contact_id = id)
37. 0.024 0.024 ↑ 1.0 1 4

Index Scan using user_mail_message_pkey on user_mail_message umm (cost=0.43..6.92 rows=1 width=454) (actual time=0.006..0.006 rows=1 loops=4)

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