explain.depesz.com

PostgreSQL's explain analyze made readable

Result: P2RI : Optimization for: plan #OVs2

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 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=)

2.          

CTE attach_files_tmp

3. 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
4. 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=)

5. 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=)

6. 0.000 0.000 ↓ 0.0

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

7. 0.000 0.000 ↓ 0.0

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

8. 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)
9. 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=)

10. 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=)

11. 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=)

12. 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)
13. 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=)

14. 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)
15. 0.000 0.000 ↓ 0.0

Hash Join (cost=112,040.21..114,891.86 rows=4,202 width=20) (actual rows= loops=)

  • Hash Cond: (ummd.user_mail_message_id = umm.id)
16. 0.000 0.000 ↓ 0.0

CTE Scan on user_mail_message_detail_tmp ummd (cost=0.00..2,605.43 rows=93,798 width=12) (actual rows= loops=)

  • Filter: ((status <> '-1'::integer) AND (COALESCE((status)::integer, '-1'::integer) <> '-1'::integer))
17. 0.000 0.000 ↓ 0.0

Hash (cost=110,506.97..110,506.97 rows=122,659 width=12) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on user_mail_message umm (cost=7,603.92..110,506.97 rows=122,659 width=12) (actual rows= loops=)

  • Recheck Cond: ((user_account_id = 28,996) AND ((folder)::text = 'inbox'::text))
19. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on user_mail_message__message_id__ukey (cost=0.00..7,573.26 rows=122,659 width=0) (actual rows= loops=)

  • Index Cond: ((user_account_id = 28,996) AND ((folder)::text = 'inbox'::text))
20. 0.000 0.000 ↓ 0.0

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

21. 0.000 0.000 ↓ 0.0

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

22. 0.000 0.000 ↓ 0.0

Index Scan using candidate_pkey on candidate can (cost=0.29..0.50 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (id = ummd.candidate_id)
23. 0.000 0.000 ↓ 0.0

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

24. 0.000 0.000 ↓ 0.0

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

25. 0.000 0.000 ↓ 0.0

Hash (cost=23.62..23.62 rows=1,181 width=4) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

CTE Scan on attach_files_tmp (cost=0.00..23.62 rows=1,181 width=4) (actual rows= loops=)