explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8jBU

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 0.208 ↑ 1.0 2 1

Append (cost=0.33..7.98 rows=2 width=81) (actual time=0.143..0.208 rows=2 loops=1)

  • Buffers: shared hit=26
2.          

CTE cte

3. 0.003 0.003 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=1)

  • Output: '0238732d-056c-fb9d-64ef-e558880140b4'::uuid, '142af7e8-b95a-5c26-c2f3-0e7034831ca2'::uuid
4.          

CTE cte2

5. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.001 rows=1 loops=1)

  • Output: '142af7e8-b95a-5c26-c2f3-0e7034831ca2'::uuid, '0238732d-056c-fb9d-64ef-e558880140b4'::uuid
6. 0.003 0.143 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.31..3.96 rows=1 width=81) (actual time=0.142..0.143 rows=1 loops=1)

  • Output: cte.member_guid, cte.other_member_guid, c.latest_message_sent_tstamp, c.is_priority, c.is_either_member_blocked, c.is_other_member_ghosted, c.conversation_depth, c.is_latest_message_sender, mp.gender_identity_type, mp.seeking_gender, mp.date_of_birth, mp.is_upgraded, mp.home_country_id, mp.legacy_profile_id, mr.restricted_to_gender_identity, mr.restricted_to_country, mr.minimum_msg_length, mr.minimum_age, mr.maximum_age, mr.restricted_to_members_with_profile_image, mr.restricted_to_upgraded_members
  • Inner Unique: true
  • Buffers: shared hit=13
7. 0.011 0.120 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.20..2.64 rows=1 width=69) (actual time=0.120..0.120 rows=1 loops=1)

  • Output: cte.member_guid, cte.other_member_guid, c.latest_message_sent_tstamp, c.is_priority, c.is_either_member_blocked, c.is_other_member_ghosted, c.conversation_depth, c.is_latest_message_sender, mp.gender_identity_type, mp.seeking_gender, mp.date_of_birth, mp.is_upgraded, mp.home_country_id, mp.legacy_profile_id
  • Inner Unique: true
  • Buffers: shared hit=9
8. 0.006 0.095 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.08..1.31 rows=1 width=48) (actual time=0.094..0.095 rows=1 loops=1)

  • Output: cte.member_guid, cte.other_member_guid, c.latest_message_sent_tstamp, c.is_priority, c.is_either_member_blocked, c.is_other_member_ghosted, c.conversation_depth, c.is_latest_message_sender
  • Inner Unique: true
  • Buffers: shared hit=4
9. 0.008 0.008 ↑ 1.0 1 1

CTE Scan on cte (cost=0.00..0.02 rows=1 width=32) (actual time=0.007..0.008 rows=1 loops=1)

  • Output: cte.member_guid, cte.other_member_guid
10. 0.081 0.081 ↑ 1.0 1 1

Index Scan using conversations_pk on projection.conversations c (cost=0.08..1.29 rows=1 width=48) (actual time=0.081..0.081 rows=1 loops=1)

  • Output: c.member_guid, c.other_member_guid, c.conversation_legacy_id, c.latest_message_type_id, c.conversation_depth, c.is_either_member_blocked, c.is_latest_message_sender, c.has_new_message, c.is_conversation_removed, c.is_other_member_deleted, c.is_other_member_ghosted, c.is_priority, c.latest_message_sent_tstamp
  • Index Cond: ((c.member_guid = cte.member_guid) AND (c.other_member_guid = cte.other_member_guid))
  • Buffers: shared hit=4
11. 0.014 0.014 ↑ 1.0 1 1

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

  • Output: mp.member_guid, mp.inserted_tstamp, mp.date_of_birth, mp.legacy_profile_id, mp.gender_identity_type, mp.seeking_gender, mp.education, mp.body_type, mp.home_state_id, mp.home_country_id, mp.is_voice_chat_enabled, mp.username, mp.first_name, mp.home_city_name, mp.headline, mp.main_image_thumbnail_url, mp.interests, mp.is_upgraded, mp.last_activity_tstamp, mp.member_ghosted_tstamp, mp.member_deleted_tstamp
  • Index Cond: (mp.member_guid = cte.member_guid)
  • Buffers: shared hit=5
12. 0.020 0.020 ↓ 0.0 0 1

Index Scan using member_messaging_restrictions_pk on source.member_messaging_restrictions mr (cost=0.11..1.32 rows=1 width=28) (actual time=0.020..0.020 rows=0 loops=1)

  • Output: mr.member_guid, mr.maximum_distance_km, mr.restricted_to_gender_identity, mr.restricted_to_country, mr.minimum_msg_length, mr.minimum_age, mr.maximum_age, mr.restricted_to_members_with_profile_image, mr.restricted_to_upgraded_members
  • Index Cond: (mr.member_guid = cte.member_guid)
  • Buffers: shared hit=4
13. 0.001 0.064 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.31..3.96 rows=1 width=81) (actual time=0.063..0.064 rows=1 loops=1)

  • Output: cte2.member_guid, cte2.other_member_guid, c_1.latest_message_sent_tstamp, c_1.is_priority, c_1.is_either_member_blocked, c_1.is_other_member_ghosted, c_1.conversation_depth, c_1.is_latest_message_sender, mp_1.gender_identity_type, mp_1.seeking_gender, mp_1.date_of_birth, mp_1.is_upgraded, mp_1.home_country_id, mp_1.legacy_profile_id, mr_1.restricted_to_gender_identity, mr_1.restricted_to_country, mr_1.minimum_msg_length, mr_1.minimum_age, mr_1.maximum_age, mr_1.restricted_to_members_with_profile_image, mr_1.restricted_to_upgraded_members
  • Inner Unique: true
  • Buffers: shared hit=13
14. 0.003 0.035 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.20..2.64 rows=1 width=69) (actual time=0.034..0.035 rows=1 loops=1)

  • Output: cte2.member_guid, cte2.other_member_guid, c_1.latest_message_sent_tstamp, c_1.is_priority, c_1.is_either_member_blocked, c_1.is_other_member_ghosted, c_1.conversation_depth, c_1.is_latest_message_sender, mp_1.gender_identity_type, mp_1.seeking_gender, mp_1.date_of_birth, mp_1.is_upgraded, mp_1.home_country_id, mp_1.legacy_profile_id
  • Inner Unique: true
  • Buffers: shared hit=9
15. 0.003 0.018 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.08..1.31 rows=1 width=48) (actual time=0.018..0.018 rows=1 loops=1)

  • Output: cte2.member_guid, cte2.other_member_guid, c_1.latest_message_sent_tstamp, c_1.is_priority, c_1.is_either_member_blocked, c_1.is_other_member_ghosted, c_1.conversation_depth, c_1.is_latest_message_sender
  • Inner Unique: true
  • Buffers: shared hit=4
16. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on cte2 (cost=0.00..0.02 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=1)

  • Output: cte2.member_guid, cte2.other_member_guid
17. 0.013 0.013 ↑ 1.0 1 1

Index Scan using conversations_pk on projection.conversations c_1 (cost=0.08..1.29 rows=1 width=48) (actual time=0.013..0.013 rows=1 loops=1)

  • Output: c_1.member_guid, c_1.other_member_guid, c_1.conversation_legacy_id, c_1.latest_message_type_id, c_1.conversation_depth, c_1.is_either_member_blocked, c_1.is_latest_message_sender, c_1.has_new_message, c_1.is_conversation_removed, c_1.is_other_member_deleted, c_1.is_other_member_ghosted, c_1.is_priority, c_1.latest_message_sent_tstamp
  • Index Cond: ((c_1.member_guid = cte2.member_guid) AND (c_1.other_member_guid = cte2.other_member_guid))
  • Buffers: shared hit=4
18. 0.014 0.014 ↑ 1.0 1 1

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

  • Output: mp_1.member_guid, mp_1.inserted_tstamp, mp_1.date_of_birth, mp_1.legacy_profile_id, mp_1.gender_identity_type, mp_1.seeking_gender, mp_1.education, mp_1.body_type, mp_1.home_state_id, mp_1.home_country_id, mp_1.is_voice_chat_enabled, mp_1.username, mp_1.first_name, mp_1.home_city_name, mp_1.headline, mp_1.main_image_thumbnail_url, mp_1.interests, mp_1.is_upgraded, mp_1.last_activity_tstamp, mp_1.member_ghosted_tstamp, mp_1.member_deleted_tstamp
  • Index Cond: (mp_1.member_guid = cte2.member_guid)
  • Buffers: shared hit=5
19. 0.028 0.028 ↓ 0.0 0 1

Index Scan using member_messaging_restrictions_pk on source.member_messaging_restrictions mr_1 (cost=0.11..1.32 rows=1 width=28) (actual time=0.028..0.028 rows=0 loops=1)

  • Output: mr_1.member_guid, mr_1.maximum_distance_km, mr_1.restricted_to_gender_identity, mr_1.restricted_to_country, mr_1.minimum_msg_length, mr_1.minimum_age, mr_1.maximum_age, mr_1.restricted_to_members_with_profile_image, mr_1.restricted_to_upgraded_members
  • Index Cond: (mr_1.member_guid = cte2.member_guid)
  • Buffers: shared hit=4