explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mIls

Settings
# exclusive inclusive rows x rows loops node
1. 427.302 21,165.604 ↓ 500.0 500 1

Subquery Scan on s (cost=49,947.43..49,947.82 rows=1 width=44) (actual time=20,760.562..21,165.604 rows=500 loops=1)

  • Filter: ((s.user_distance)::double precision >= s.own_distance)
2. 0.159 20,738.302 ↓ 500.0 500 1

Limit (cost=49,947.43..49,947.43 rows=1 width=200) (actual time=20,737.935..20,738.302 rows=500 loops=1)

3. 18.045 20,738.143 ↓ 500.0 500 1

Sort (cost=49,947.43..49,947.43 rows=1 width=200) (actual time=20,737.933..20,738.143 rows=500 loops=1)

  • Sort Key: (_st_distance(pp_user.mpp_geo_point, pp_search_user.mpp_geo_point, '0'::double precision, true))
  • Sort Method: top-N heapsort Memory: 157kB
4. 664.213 20,720.098 ↓ 31,360.0 31,360 1

Nested Loop (cost=1.11..49,947.42 rows=1 width=200) (actual time=2.889..20,720.098 rows=31,360 loops=1)

5. 0.039 0.039 ↑ 12.0 1 1

Index Scan using match_profile_pool_user_filter_idx on match_profile_pool pp_user (cost=0.56..52.58 rows=12 width=150) (actual time=0.037..0.039 rows=1 loops=1)

  • Index Cond: ((mpp_user_id = 18,676,387) AND (mpp_filter_id = '91'::smallint))
6. 20,055.846 20,055.846 ↓ 31,360.0 31,360 1

Index Scan using match_profile_pool_geo_search_idx_gist on match_profile_pool pp_search_user (cost=0.55..4,157.87 rows=1 width=216) (actual time=1.196..20,055.846 rows=31,360 loops=1)

  • Index Cond: (mpp_geo_point && _st_expand(pp_user.mpp_geo_point, ((pp_user.mpp_distance * 1000))::double precision))
  • Filter: ((mpp_filter_id = '91'::smallint) AND (pp_user.mpp_locale_filter_set_id = mpp_locale_filter_set_id) AND (pp_user.mpp_gender = mpp_search_gender) AND (pp_user.mpp_search_gender = mpp_gender) AND ((mpp_bodyheight <@ pp_user.mpp_bodyheight_range) OR ((mpp_bodyheight - 1) <@ pp_user.mpp_bodyheight_range)) AND ((pp_user.mpp_bodyheight <@ mpp_bodyheight_range) OR ((pp_user.mpp_bodyheight - 1) <@ mpp_bodyheight_range)) AND ((mpp_age <@ pp_user.mpp_age_range) OR ((mpp_age - 1) <@ pp_user.mpp_age_range)) AND (mpp_smoking_id = ANY (pp_user.mpp_smoking_accepted_ids)) AND (mpp_education_id = ANY (pp_user.mpp_education_accepted_id)) AND (pp_user.mpp_education_id = ANY (mpp_education_accepted_id)) AND ((('now'::cstring)::date - COALESCE(mpp_last_login, ('now'::cstring)::date)) <= 60) AND (mpp_user_id <> ALL ('{12103514,18065941,9409904,16869540,17516574,18505950,17534143,18095835,18530796,15145753,18676387,5496252,18494675,17794706,18249855,7741111,8844189,18540360,18430392,7604643,8534889,13872713,18586156,17795506,15903315,13567531,13182475,18518430,18619004,13161119,17590404}'::integer[])) AND (pp_user.mpp_geo_point && _st_expand(mpp_geo_point, ((pp_user.mpp_distance * 1000))::double precision)) AND _st_dwithin(pp_user.mpp_geo_point, mpp_geo_point, ((pp_user.mpp_distance * 1000))::double precision, true))
  • Rows Removed by Filter: 8,994,842
Planning time : 4.594 ms
Execution time : 21,165.809 ms