explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OVs2

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Aggregate (cost=130,982.32..130,982.33 rows=1 width=8) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Sort (cost=130,933.27..130,941.44 rows=3,270 width=1,320) (actual rows= loops=)

  • Sort Key: umm.received_date DESC
3. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=125,499.59..130,709.68 rows=3,270 width=1,320) (actual rows= loops=)

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

CTE user_mail_message_detail_tmp

5. 0.000 0.000 ↓ 0.0

Append (cost=9.00..13,228.45 rows=94,743 width=18) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Merge Join (cost=9.00..3,833.67 rows=61,309 width=18) (actual rows= loops=)

  • Merge Cond: (umcan.user_mail_message_id = umcon.user_mail_message_id)
7. 0.000 0.000 ↓ 0.0

Index Scan using user_mail_candidate_pkey on user_mail_candidate umcan (cost=0.29..1,159.08 rows=46,502 width=10) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Index Only Scan using user_mail_contact_pkey on user_mail_contact umcon (cost=0.29..1,739.19 rows=96,200 width=8) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=37.70..4,067.18 rows=7,220 width=18) (actual rows= loops=)

  • Hash Cond: (umcon_1.contact_id = con_1.id)
  • Filter: ((umcon_1.contact_id IS NULL) OR (con_1.deleted_timestamp IS NOT NULL))
10. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=0.58..3,868.11 rows=61,309 width=14) (actual rows= loops=)

  • Merge Cond: (umcan_1.user_mail_message_id = umcon_1.user_mail_message_id)
11. 0.000 0.000 ↓ 0.0

Index Scan using user_mail_candidate_pkey on user_mail_candidate umcan_1 (cost=0.29..1,159.08 rows=46,502 width=10) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Index Only Scan using user_mail_contact_pkey on user_mail_contact umcon_1 (cost=0.29..1,739.19 rows=96,200 width=8) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Hash (cost=28.72..28.72 rows=672 width=12) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Seq Scan on contact con_1 (cost=0.00..28.72 rows=672 width=12) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Merge Right Join (cost=7.15..4,236.17 rows=1 width=18) (actual rows= loops=)

  • Merge Cond: (umcan_2.user_mail_message_id = umcon_2.user_mail_message_id)
  • Filter: (umcan_2.candidate_id IS NULL)
16. 0.000 0.000 ↓ 0.0

Index Only Scan using user_mail_candidate_pkey on user_mail_candidate umcan_2 (cost=0.29..878.56 rows=46,502 width=8) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Index Scan using user_mail_contact_pkey on user_mail_contact umcon_2 (cost=0.29..2,414.63 rows=96,200 width=10) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Seq Scan on user_mail_others umo (cost=0.00..406.13 rows=26,213 width=18) (actual rows= loops=)

19.          

CTE attach_files_tmp

20. 0.000 0.000 ↓ 0.0

HashAggregate (cost=106.21..118.02 rows=1,181 width=12) (actual rows= loops=)

  • Group Key: uma.user_mail_message_id
21. 0.000 0.000 ↓ 0.0

Seq Scan on user_mail_attachment uma (cost=0.00..90.47 rows=3,147 width=4) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=112,114.74..116,834.32 rows=3,270 width=12) (actual rows= loops=)

  • Hash Cond: (can.contact_id = can_con.id)
  • Filter: (can_con.deleted_timestamp IS NULL)
23. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=112,077.62..116,787.47 rows=3,707 width=16) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=112,077.33..114,940.08 rows=3,707 width=16) (actual rows= loops=)

  • Hash Cond: (ummd.contact_id = con.id)
  • Filter: (con.deleted_timestamp IS NULL)
  • -> Hash Join (cost=112,040.21..114891.86 rows=4,202 width=20):