explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bnd7

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 2.430 ↓ 5.1 36 1

Append (cost=1.50..39.61 rows=7 width=255) (actual time=0.123..2.430 rows=36 loops=1)

  • Buffers: shared hit=574
2. 0.015 0.396 ↓ 2.0 10 1

Nested Loop Left Join (cost=1.50..31.52 rows=5 width=255) (actual time=0.122..0.396 rows=10 loops=1)

  • Output: sc.member_guid, sc.other_member_guid, NULL::timestamp with time zone, 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, 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
  • Buffers: shared hit=98
3. 0.020 0.191 ↓ 2.0 10 1

Nested Loop (cost=1.39..24.89 rows=5 width=62) (actual time=0.084..0.191 rows=10 loops=1)

  • Output: 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, sc.member_guid, sc.other_member_guid
  • Inner Unique: true
  • Buffers: shared hit=48
4. 0.040 0.075 ↑ 1.0 12 1

Bitmap Heap Scan on source_v2.conversations sc (cost=1.33..10.29 rows=12 width=40) (actual time=0.045..0.075 rows=12 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 = '66964ee7-4dca-55ab-9431-450b2e74b9ca'::uuid) OR (sc.other_member_guid = '66964ee7-4dca-55ab-9431-450b2e74b9ca'::uuid))
  • Heap Blocks: exact=10
  • Buffers: shared hit=14
5. 0.000 0.035 ↓ 0.0 0 1

BitmapOr (cost=1.33..1.33 rows=12 width=0) (actual time=0.035..0.035 rows=0 loops=1)

  • Buffers: shared hit=4
6. 0.023 0.023 ↑ 1.0 7 1

Bitmap Index Scan on conversations_member_guid_idx (cost=0.00..0.67 rows=7 width=0) (actual time=0.022..0.023 rows=7 loops=1)

  • Index Cond: (sc.member_guid = '66964ee7-4dca-55ab-9431-450b2e74b9ca'::uuid)
  • Buffers: shared hit=2
7. 0.012 0.012 ↑ 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.012..0.012 rows=5 loops=1)

  • Index Cond: (sc.other_member_guid = '66964ee7-4dca-55ab-9431-450b2e74b9ca'::uuid)
  • Buffers: shared hit=2
8. 0.096 0.096 ↑ 1.0 1 12

Index Scan using conversations_pk on projection_v2.conversations pc (cost=0.06..1.22 rows=1 width=38) (actual time=0.008..0.008 rows=1 loops=12)

  • 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)
  • Buffers: shared hit=34
9. 0.190 0.190 ↑ 1.0 1 10

Index Scan using member_profiles_pk on source.member_profiles mp (cost=0.11..1.32 rows=1 width=201) (actual time=0.019..0.019 rows=1 loops=10)

  • Output: 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 ('66964ee7-4dca-55ab-9431-450b2e74b9ca'::uuid IS DISTINCT FROM sc.member_guid) THEN sc.member_guid ELSE sc.other_member_guid END)
  • Buffers: shared hit=50
10. 1.458 2.025 ↓ 13.0 26 1

Nested Loop Left Join (cost=1.55..7.99 rows=2 width=255) (actual time=0.441..2.025 rows=26 loops=1)

  • Output: sm.member_guid, sm.other_member_guid, sm.created_tstamp, 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_1.member_deleted_tstamp IS NOT NULL), (mp_1.member_ghosted_tstamp IS NOT NULL), NULL::boolean, NULL::boolean, 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
  • Buffers: shared hit=476
11. 0.034 0.437 ↓ 13.0 26 1

Nested Loop Left Join (cost=1.43..5.24 rows=2 width=241) (actual time=0.071..0.437 rows=26 loops=1)

  • Output: sm.member_guid, sm.other_member_guid, sm.created_tstamp, mp_1.member_deleted_tstamp, mp_1.member_ghosted_tstamp, 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
  • Buffers: shared hit=138
12. 0.025 0.065 ↓ 13.0 26 1

Bitmap Heap Scan on source_v2.mutual sm (cost=1.32..2.59 rows=2 width=40) (actual time=0.046..0.065 rows=26 loops=1)

  • Output: sm.mutual_id, sm.member_guid, sm.other_member_guid, sm.inserted_tstamp, sm.created_tstamp
  • Recheck Cond: ((sm.member_guid = '66964ee7-4dca-55ab-9431-450b2e74b9ca'::uuid) OR (sm.other_member_guid = '66964ee7-4dca-55ab-9431-450b2e74b9ca'::uuid))
  • Heap Blocks: exact=4
  • Buffers: shared hit=8
13. 0.000 0.040 ↓ 0.0 0 1

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

  • Buffers: shared hit=4
14. 0.027 0.027 ↓ 32.0 32 1

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

  • Index Cond: (sm.member_guid = '66964ee7-4dca-55ab-9431-450b2e74b9ca'::uuid)
  • Buffers: shared hit=2
15. 0.013 0.013 ↓ 13.0 13 1

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

  • Index Cond: (sm.other_member_guid = '66964ee7-4dca-55ab-9431-450b2e74b9ca'::uuid)
  • Buffers: shared hit=2
16. 0.338 0.338 ↑ 1.0 1 26

Index Scan using member_profiles_pk on source.member_profiles mp_1 (cost=0.11..1.32 rows=1 width=217) (actual time=0.013..0.013 rows=1 loops=26)

  • Output: mp_1.member_deleted_tstamp, mp_1.member_ghosted_tstamp, 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 ('66964ee7-4dca-55ab-9431-450b2e74b9ca'::uuid IS DISTINCT FROM sm.member_guid) THEN sm.member_guid ELSE sm.other_member_guid END)
  • Buffers: shared hit=130
17. 0.130 0.130 ↑ 1.0 1 26

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=26)

  • Output: mp2.member_deleted_tstamp, mp2.member_ghosted_tstamp, mp2.member_guid
  • Index Cond: (mp2.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)
  • Buffers: shared hit=130
Planning time : 1.192 ms