explain.depesz.com

PostgreSQL's explain analyze made readable

Result: noJG

Settings
# exclusive inclusive rows x rows loops node
1. 0.054 315.236 ↓ 200.0 200 1

Limit (cost=0.20..2.75 rows=1 width=175) (actual time=0.908..315.236 rows=200 loops=1)

  • Output: mm.member_guid, mm.legacy_user_id, mm.legacy_profile_id, mm.last_activity_utc, mm.date_of_birth, mm.gender, mm.home_country_id, mm.intent_type, mm.headline, mmpi.member_name, mmpi.home_city_name, mmpi.main_image_thumbnail_url, (((_st_distance('0101000020E610000048E17A14AEC75EC0A4703D0AD7A34840'::geography, mm.home_long_lat_geo, '0'::double precision, true) / '1000'::double precision))::integer), ((mm.profile_completed_utc <-> '2020-09-11 17:00:00'::timestamp without time zone))
  • Buffers: shared hit=47,992
2. 1.811 315.182 ↓ 200.0 200 1

Nested Loop (cost=0.20..2.75 rows=1 width=175) (actual time=0.907..315.182 rows=200 loops=1)

  • Output: mm.member_guid, mm.legacy_user_id, mm.legacy_profile_id, mm.last_activity_utc, mm.date_of_birth, mm.gender, mm.home_country_id, mm.intent_type, mm.headline, mmpi.member_name, mmpi.home_city_name, mmpi.main_image_thumbnail_url, ((_st_distance('0101000020E610000048E17A14AEC75EC0A4703D0AD7A34840'::geography, mm.home_long_lat_geo, '0'::double precision, true) / '1000'::double precision))::integer, (mm.profile_completed_utc <-> '2020-09-11 17:00:00'::timestamp without time zone)
  • Inner Unique: true
  • Buffers: shared hit=47,992
3. 310.984 310.984 ↓ 217.0 217 1

Index Scan using mm_long_range_hllg_dob_last_activity_profile_completed_gist on matching.matching_member mm (cost=0.08..1.37 rows=1 width=109) (actual time=0.758..310.984 rows=217 loops=1)

  • Output: mm.member_guid, mm.legacy_user_id, mm.legacy_profile_id, mm.last_activity_utc, mm.date_of_birth, mm.gender, mm.home_country_id, mm.intent_type, mm.headline, mm.home_long_lat_geo, mm.profile_completed_utc
  • Index Cond: ((mm.home_long_lat_geo && '0101000020E610000048E17A14AEC75EC0A4703D0AD7A34840'::geography) AND (mm.date_of_birth >= '1970-01-01'::date) AND (mm.date_of_birth <= '2002-01-01'::date) AND (mm.profile_completed_utc IS NOT NULL))
  • Order By: (mm.profile_completed_utc <-> '2020-09-11 17:00:00'::timestamp without time zone)
  • Filter: ((mm.has_main_image IS TRUE) AND (mm.member_guid <> 'a009b356-a794-6dbe-d097-8e905321daa1'::uuid) AND (mm.gender = 1) AND (mm.seeking_gender = 0) AND ((mm.last_activity_utc)::date >= '2020-08-01'::date) AND ('0101000020E610000048E17A14AEC75EC0A4703D0AD7A34840'::geography && _st_expand(mm.home_long_lat_geo, '500000'::double precision)) AND _st_dwithin(mm.home_long_lat_geo, '0101000020E610000048E17A14AEC75EC0A4703D0AD7A34840'::geography, '500000'::double precision, false))
  • Rows Removed by Filter: 38,400
  • Buffers: shared hit=46,900
4. 2.387 2.387 ↑ 1.0 1 217

Index Scan using mmpi_mobile_long_lat_updated_date_idx on matching.matching_member_presentation_info mmpi (cost=0.11..1.32 rows=1 width=102) (actual time=0.011..0.011 rows=1 loops=217)

  • Output: mmpi.member_guid, mmpi.legacy_user_id, mmpi.legacy_profile_id, mmpi.home_longitude, mmpi.home_latitude, mmpi.inserted_tstamp, mmpi.last_edited, mmpi.date_of_birth, mmpi.gender, mmpi.seeking_gender, mmpi.member_ip, mmpi.member_name, mmpi.home_country_code, mmpi.home_state_code, mmpi.home_zipcode, mmpi.home_city_name, mmpi.main_image_thumbnail_url, mmpi.mobile_longitude, mmpi.mobile_latitude, mmpi.mobile_long_lat_updated_date
  • Index Cond: (mmpi.member_guid = mm.member_guid)
  • Filter: (mmpi.member_name IS NOT NULL)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=1,089
Planning time : 1.412 ms
Execution time : 315.366 ms