explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fNjq

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 0.882 ↓ 2.8 14 1

Append (cost=1.50..34.67 rows=5 width=296) (actual time=0.077..0.882 rows=14 loops=1)

2. 0.009 0.328 ↓ 2.2 9 1

Nested Loop Left Join (cost=1.50..28.71 rows=4 width=296) (actual time=0.077..0.328 rows=9 loops=1)

3. 0.017 0.166 ↓ 2.2 9 1

Nested Loop (cost=1.39..23.41 rows=4 width=88) (actual time=0.050..0.166 rows=9 loops=1)

4. 0.057 0.083 ↑ 1.0 11 1

Bitmap Heap Scan on conversations sc (cost=1.33..9.47 rows=11 width=40) (actual time=0.033..0.083 rows=11 loops=1)

  • Recheck Cond: ((member_guid = '66964ee7-4dca-55ab-9431-450b2e74b9ca'::uuid) OR (other_member_guid = '66964ee7-4dca-55ab-9431-450b2e74b9ca'::uuid))
  • Heap Blocks: exact=9
5. 0.001 0.026 ↓ 0.0 0 1

BitmapOr (cost=1.33..1.33 rows=11 width=0) (actual time=0.026..0.026 rows=0 loops=1)

6. 0.015 0.015 ↑ 1.0 6 1

Bitmap Index Scan on conversations_member_guid_idx (cost=0.00..0.67 rows=6 width=0) (actual time=0.015..0.015 rows=6 loops=1)

  • Index Cond: (member_guid = '66964ee7-4dca-55ab-9431-450b2e74b9ca'::uuid)
7. 0.010 0.010 ↑ 1.0 5 1

Bitmap Index Scan on conversations_other_member_guid_idx (cost=0.00..0.66 rows=5 width=0) (actual time=0.010..0.010 rows=5 loops=1)

  • Index Cond: (other_member_guid = '66964ee7-4dca-55ab-9431-450b2e74b9ca'::uuid)
8. 0.066 0.066 ↑ 1.0 1 11

Index Scan using conversations_pk on conversations pc (cost=0.06..1.27 rows=1 width=56) (actual time=0.006..0.006 rows=1 loops=11)

  • Index Cond: (conversation_id = sc.conversation_id)
9. 0.153 0.153 ↑ 1.0 1 9

Index Scan using member_profiles_pk on member_profiles mp (cost=0.11..1.32 rows=1 width=200) (actual time=0.017..0.017 rows=1 loops=9)

  • Index Cond: (member_guid = CASE WHEN ('66964ee7-4dca-55ab-9431-450b2e74b9ca'::uuid IS DISTINCT FROM sc.member_guid) THEN sc.member_guid ELSE sc.other_member_guid END)
10. 0.410 0.550 ↓ 5.0 5 1

Nested Loop Left Join (cost=1.53..5.88 rows=1 width=296) (actual time=0.314..0.550 rows=5 loops=1)

11. 0.005 0.105 ↓ 5.0 5 1

Nested Loop Left Join (cost=1.42..4.51 rows=1 width=256) (actual time=0.040..0.105 rows=5 loops=1)

12. 0.017 0.045 ↓ 5.0 5 1

Bitmap Heap Scan on mutual sm (cost=1.30..3.18 rows=1 width=56) (actual time=0.022..0.045 rows=5 loops=1)

  • Recheck Cond: ((member_guid = '66964ee7-4dca-55ab-9431-450b2e74b9ca'::uuid) OR (other_member_guid = '66964ee7-4dca-55ab-9431-450b2e74b9ca'::uuid))
  • Filter: ((member_guid = '66964ee7-4dca-55ab-9431-450b2e74b9ca'::uuid) OR ((other_member_guid = '66964ee7-4dca-55ab-9431-450b2e74b9ca'::uuid) AND (NOT (alternatives: SubPlan 1 or hashed SubPlan 2))))
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=2
13. 0.001 0.016 ↓ 0.0 0 1

BitmapOr (cost=1.30..1.30 rows=1 width=0) (actual time=0.016..0.016 rows=0 loops=1)

14. 0.009 0.009 ↓ 6.0 6 1

Bitmap Index Scan on mutual_member_guid_idx (cost=0.00..0.65 rows=1 width=0) (actual time=0.009..0.009 rows=6 loops=1)

  • Index Cond: (member_guid = '66964ee7-4dca-55ab-9431-450b2e74b9ca'::uuid)
15. 0.006 0.006 ↓ 3.0 3 1

Bitmap Index Scan on mutual_other_member_guid_idx (cost=0.00..0.65 rows=1 width=0) (actual time=0.006..0.006 rows=3 loops=1)

  • Index Cond: (other_member_guid = '66964ee7-4dca-55ab-9431-450b2e74b9ca'::uuid)
16.          

SubPlan (for Bitmap Heap Scan)

17. 0.012 0.012 ↓ 0.0 0 2

Index Scan using conversations_other_member_guid_idx on conversations sc_1 (cost=0.06..1.27 rows=1 width=0) (actual time=0.006..0.006 rows=0 loops=2)

  • Index Cond: (other_member_guid = sm.other_member_guid)
  • Filter: (member_guid = sm.member_guid)
  • Rows Removed by Filter: 2
18. 0.000 0.000 ↓ 0.0 0

Seq Scan on conversations sc_2 (cost=0.00..133.67 rows=2,867 width=32) (never executed)

19. 0.055 0.055 ↑ 1.0 1 5

Index Scan using member_profiles_pk on member_profiles mp_1 (cost=0.11..1.32 rows=1 width=216) (actual time=0.011..0.011 rows=1 loops=5)

  • Index Cond: (member_guid = CASE WHEN ('66964ee7-4dca-55ab-9431-450b2e74b9ca'::uuid IS DISTINCT FROM sm.member_guid) THEN sm.member_guid ELSE sm.other_member_guid END)
20. 0.035 0.035 ↑ 1.0 1 5

Index Scan using member_profiles_pk on member_profiles mp2 (cost=0.11..1.32 rows=1 width=32) (actual time=0.007..0.007 rows=1 loops=5)

  • Index Cond: (member_guid = CASE WHEN ('66964ee7-4dca-55ab-9431-450b2e74b9ca'::uuid IS DISTINCT FROM sm.member_guid) THEN sm.other_member_guid ELSE sm.member_guid END)
Planning time : 1.103 ms