explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Chjv

Settings
# exclusive inclusive rows x rows loops node
1. 0.072 2.982 ↓ 5.2 31 1

Append (cost=1.55..39.33 rows=6 width=296) (actual time=0.338..2.982 rows=31 loops=1)

2. 1.232 2.136 ↓ 11.0 22 1

Nested Loop Left Join (cost=1.55..10.53 rows=2 width=296) (actual time=0.337..2.136 rows=22 loops=1)

  • Output: sm.member_guid, sm.other_member_guid, sm.mutual_id, sm.inserted_tstamp, sm.created_tstamp, NULL::bigint, NULL::bigint, NULL::bigint, NULL::timestamp with time zone, NULL::timestamp with time zone, NULL::smallint, source.calculate_is_either_member_blocked(sm.member_guid, sm.other_member_guid), NULL::boolean, NULL::boolean, NULL::boolean, NULL::boolean, NULL::boolean, (mp2.member_deleted_tstamp IS NOT NULL), (mp2.member_ghosted_tstamp IS NOT NULL), (mp.member_deleted_tstamp IS NOT NULL), (mp.member_ghosted_tstamp IS NOT NULL), NULL::boolean, NULL::boolean, NULL::smallint, mp.gender_identity_type, mp.first_name, mp.home_city_name, mp.main_image_thumbnail_url, mp.last_activity_tstamp, mp.date_of_birth, mp.legacy_profile_id, mp.is_upgraded, mp.username, mp.headline, mp.show_firstname, mp.seeking_gender
  • Inner Unique: true
3. 0.025 0.794 ↓ 11.0 22 1

Nested Loop Left Join (cost=1.43..7.78 rows=2 width=256) (actual time=0.075..0.794 rows=22 loops=1)

  • Output: sm.member_guid, sm.other_member_guid, sm.mutual_id, sm.inserted_tstamp, sm.created_tstamp, mp.member_deleted_tstamp, mp.member_ghosted_tstamp, mp.gender_identity_type, mp.first_name, mp.home_city_name, mp.main_image_thumbnail_url, mp.last_activity_tstamp, mp.date_of_birth, mp.legacy_profile_id, mp.is_upgraded, mp.username, mp.headline, mp.show_firstname, mp.seeking_gender
  • Inner Unique: true
4. 0.037 0.131 ↓ 11.0 22 1

Bitmap Heap Scan on source_v2.mutual sm (cost=1.32..5.13 rows=2 width=56) (actual time=0.045..0.131 rows=22 loops=1)

  • Output: sm.mutual_id, sm.member_guid, sm.other_member_guid, sm.inserted_tstamp, sm.created_tstamp
  • Recheck Cond: ((sm.member_guid = '3c6c4c46-a027-25b0-d4ee-8b9a773fa425'::uuid) OR (sm.other_member_guid = '3c6c4c46-a027-25b0-d4ee-8b9a773fa425'::uuid))
  • Filter: ((sm.member_guid = '3c6c4c46-a027-25b0-d4ee-8b9a773fa425'::uuid) OR ((sm.other_member_guid = '3c6c4c46-a027-25b0-d4ee-8b9a773fa425'::uuid) AND (NOT (alternatives: SubPlan 1 or hashed SubPlan 2))))
  • Heap Blocks: exact=4
5. 0.001 0.034 ↓ 0.0 0 1

BitmapOr (cost=1.32..1.32 rows=2 width=0) (actual time=0.034..0.034 rows=0 loops=1)

6. 0.022 0.022 ↓ 13.0 13 1

Bitmap Index Scan on mutual_member_guid_idx (cost=0.00..0.66 rows=1 width=0) (actual time=0.022..0.022 rows=13 loops=1)

  • Index Cond: (sm.member_guid = '3c6c4c46-a027-25b0-d4ee-8b9a773fa425'::uuid)
7. 0.011 0.011 ↓ 10.0 10 1

Bitmap Index Scan on mutual_other_member_guid_idx (cost=0.00..0.66 rows=1 width=0) (actual time=0.011..0.011 rows=10 loops=1)

  • Index Cond: (sm.other_member_guid = '3c6c4c46-a027-25b0-d4ee-8b9a773fa425'::uuid)
8.          

SubPlan (for Bitmap Heap Scan)

9. 0.060 0.060 ↓ 0.0 0 10

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

  • Index Cond: (sc_1.other_member_guid = sm.other_member_guid)
  • Filter: (sc_1.member_guid = sm.member_guid)
  • Rows Removed by Filter: 1
10. 0.000 0.000 ↓ 0.0 0

Seq Scan on source_v2.conversations sc_2 (cost=0.00..134.36 rows=2,936 width=32) (never executed)

  • Output: sc_2.member_guid, sc_2.other_member_guid
11. 0.638 0.638 ↑ 1.0 1 22

Index Scan using member_profiles_pk on source.member_profiles mp (cost=0.11..1.32 rows=1 width=216) (actual time=0.029..0.029 rows=1 loops=22)

  • Output: mp.member_deleted_tstamp, mp.member_ghosted_tstamp, mp.gender_identity_type, mp.first_name, mp.home_city_name, mp.main_image_thumbnail_url, mp.last_activity_tstamp, mp.date_of_birth, mp.legacy_profile_id, mp.is_upgraded, mp.username, mp.headline, mp.show_firstname, mp.seeking_gender, mp.member_guid
  • Index Cond: (mp.member_guid = CASE WHEN ('3c6c4c46-a027-25b0-d4ee-8b9a773fa425'::uuid IS DISTINCT FROM sm.member_guid) THEN sm.member_guid ELSE sm.other_member_guid END)
12. 0.110 0.110 ↑ 1.0 1 22

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

  • Output: mp2.member_deleted_tstamp, mp2.member_ghosted_tstamp, mp2.member_guid
  • Index Cond: (mp2.member_guid = CASE WHEN ('3c6c4c46-a027-25b0-d4ee-8b9a773fa425'::uuid IS DISTINCT FROM sm.member_guid) THEN sm.other_member_guid ELSE sm.member_guid END)
13. 0.014 0.774 ↓ 2.2 9 1

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

  • Output: sc.member_guid, sc.other_member_guid, NULL::bigint, NULL::timestamp with time zone, NULL::timestamp with time zone, pc.conversation_id, pc.member_conversation_legacy_id, pc.other_member_conversation_legacy_id, pc.member_latest_message_sent_tstamp, pc.other_member_latest_message_sent_tstamp, pc.conversation_depth, pc.is_either_member_blocked, pc.is_member_latest_message_sender, pc.member_has_new_message, pc.other_member_has_new_message, pc.is_conversation_removed_by_member, pc.is_conversation_removed_by_other_member, pc.is_member_deleted, pc.is_member_ghosted, pc.is_other_member_deleted, pc.is_other_member_ghosted, pc.member_has_priority_message, pc.other_member_has_priority_message, pc.latest_message_type_id, mp_1.gender_identity_type, mp_1.first_name, mp_1.home_city_name, mp_1.main_image_thumbnail_url, mp_1.last_activity_tstamp, mp_1.date_of_birth, mp_1.legacy_profile_id, mp_1.is_upgraded, mp_1.username, mp_1.headline, mp_1.show_firstname, mp_1.seeking_gender
  • Inner Unique: true
14. 0.013 0.274 ↓ 2.2 9 1

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

  • Output: pc.conversation_id, pc.member_conversation_legacy_id, pc.other_member_conversation_legacy_id, pc.member_latest_message_sent_tstamp, pc.other_member_latest_message_sent_tstamp, pc.conversation_depth, pc.is_either_member_blocked, pc.is_member_latest_message_sender, pc.member_has_new_message, pc.other_member_has_new_message, pc.is_conversation_removed_by_member, pc.is_conversation_removed_by_other_member, pc.is_member_deleted, pc.is_member_ghosted, pc.is_other_member_deleted, pc.is_other_member_ghosted, pc.member_has_priority_message, pc.other_member_has_priority_message, pc.latest_message_type_id, sc.member_guid, sc.other_member_guid
  • Inner Unique: true
15. 0.141 0.161 ↑ 1.1 10 1

Bitmap Heap Scan on source_v2.conversations sc (cost=1.33..9.47 rows=11 width=40) (actual time=0.024..0.161 rows=10 loops=1)

  • Output: sc.conversation_id, sc.member_guid, sc.other_member_guid, sc.member_removed_tstamp, sc.other_member_removed_tstamp, sc.member_read_tstamp, sc.other_member_read_tstamp, sc.inserted_tstamp
  • Recheck Cond: ((sc.member_guid = '3c6c4c46-a027-25b0-d4ee-8b9a773fa425'::uuid) OR (sc.other_member_guid = '3c6c4c46-a027-25b0-d4ee-8b9a773fa425'::uuid))
  • Heap Blocks: exact=6
16. 0.001 0.020 ↓ 0.0 0 1

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

17. 0.017 0.017 ↑ 1.0 10 1

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

  • Index Cond: (sc.member_guid = '3c6c4c46-a027-25b0-d4ee-8b9a773fa425'::uuid)
18. 0.002 0.002 ↑ 1.0 1 1

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

  • Index Cond: (sc.other_member_guid = '3c6c4c46-a027-25b0-d4ee-8b9a773fa425'::uuid)
19. 0.100 0.100 ↑ 1.0 1 10

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

  • Output: pc.conversation_id, pc.member_conversation_legacy_id, pc.other_member_conversation_legacy_id, pc.member_latest_message_sent_tstamp, pc.other_member_latest_message_sent_tstamp, pc.latest_message_type_id, pc.conversation_depth, pc.is_either_member_blocked, pc.is_member_latest_message_sender, pc.member_has_new_message, pc.other_member_has_new_message, pc.is_conversation_removed_by_member, pc.is_conversation_removed_by_other_member, pc.is_member_deleted, pc.is_member_ghosted, pc.is_other_member_deleted, pc.is_other_member_ghosted, pc.member_has_priority_message, pc.other_member_has_priority_message
  • Index Cond: (pc.conversation_id = sc.conversation_id)
20. 0.486 0.486 ↑ 1.0 1 9

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

  • Output: mp_1.gender_identity_type, mp_1.first_name, mp_1.home_city_name, mp_1.main_image_thumbnail_url, mp_1.last_activity_tstamp, mp_1.date_of_birth, mp_1.legacy_profile_id, mp_1.is_upgraded, mp_1.username, mp_1.headline, mp_1.show_firstname, mp_1.seeking_gender, mp_1.member_guid
  • Index Cond: (mp_1.member_guid = CASE WHEN ('3c6c4c46-a027-25b0-d4ee-8b9a773fa425'::uuid IS DISTINCT FROM sc.member_guid) THEN sc.member_guid ELSE sc.other_member_guid END)
Planning time : 1.332 ms