explain.depesz.com

PostgreSQL's explain analyze made readable

Result: S6wkg

Settings
# exclusive inclusive rows x rows loops node
1. 0.058 28,174.595 ↑ 1.0 200 1

Limit (cost=154,281.00..31,010,433.69 rows=200 width=1,632) (actual time=68.407..28,174.595 rows=200 loops=1)

  • Output: pd.person_uuid, pd.organization_uuid, (count(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (count(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (max(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (max(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (min(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (min(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (avg(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (avg(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (percentile_cont('0.25'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (percentile_cont('0.25'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (percentile_cont('0.5'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (percentile_cont('0.5'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (percentile_cont('0.75'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (percentile_cont('0.75'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (stddev_pop(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (stddev_pop(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (count(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (count(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (max(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (max(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (min(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (min(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (avg(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (avg(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (percentile_cont('0.25'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (percentile_cont('0.25'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (percentile_cont('0.5'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (percentile_cont('0.5'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (percentile_cont('0.75'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (percentile_cont('0.75'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (stddev_pop(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (stddev_pop(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (count(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (count(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (max(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (max(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (min(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (min(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (avg(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (avg(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (percentile_cont('0.25'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (percentile_cont('0.25'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (percentile_cont('0.5'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (percentile_cont('0.5'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (percentile_cont('0.75'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (percentile_cont('0.75'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (stddev_pop(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (stddev_pop(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (count(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (count(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (max(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (max(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (min(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (min(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (avg(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (avg(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (percentile_cont('0.25'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (percentile_cont('0.25'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (percentile_cont('0.5'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (percentile_cont('0.5'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (percentile_cont('0.75'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (percentile_cont('0.75'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (stddev_pop(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (stddev_pop(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (count(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (count(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (max(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (max(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (min(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (min(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (avg(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (avg(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (percentile_cont('0.25'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (percentile_cont('0.25'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (percentile_cont('0.5'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (percentile_cont('0.5'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (percentile_cont('0.75'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (percentile_cont('0.75'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (stddev_pop(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (stddev_pop(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval)))))
  • Buffers: shared hit=343,012
2. 1.104 28,174.537 ↑ 2,670.2 200 1

Nested Loop Left Join (cost=154,281.00..82,391,636,071.44 rows=534,036 width=1,632) (actual time=68.406..28,174.537 rows=200 loops=1)

  • Output: pd.person_uuid, pd.organization_uuid, (count(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (count(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (max(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (max(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (min(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (min(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (avg(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (avg(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (percentile_cont('0.25'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (percentile_cont('0.25'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (percentile_cont('0.5'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (percentile_cont('0.5'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (percentile_cont('0.75'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (percentile_cont('0.75'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (stddev_pop(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (stddev_pop(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (count(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (count(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (max(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (max(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (min(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (min(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (avg(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (avg(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (percentile_cont('0.25'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (percentile_cont('0.25'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (percentile_cont('0.5'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (percentile_cont('0.5'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (percentile_cont('0.75'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (percentile_cont('0.75'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (stddev_pop(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (stddev_pop(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (count(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (count(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (max(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (max(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (min(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (min(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (avg(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (avg(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (percentile_cont('0.25'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (percentile_cont('0.25'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (percentile_cont('0.5'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (percentile_cont('0.5'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (percentile_cont('0.75'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (percentile_cont('0.75'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (stddev_pop(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (stddev_pop(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (count(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (count(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (max(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (max(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (min(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (min(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (avg(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (avg(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (percentile_cont('0.25'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (percentile_cont('0.25'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (percentile_cont('0.5'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (percentile_cont('0.5'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (percentile_cont('0.75'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (percentile_cont('0.75'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (stddev_pop(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (stddev_pop(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (count(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (count(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (max(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (max(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (min(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (min(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (avg(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (avg(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (percentile_cont('0.25'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (percentile_cont('0.25'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (percentile_cont('0.5'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (percentile_cont('0.5'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (percentile_cont('0.75'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (percentile_cont('0.75'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))), (stddev_pop(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval))))), (stddev_pop(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval)))))
  • Buffers: shared hit=343,012
3. 0.233 0.233 ↑ 2,670.2 200 1

Seq Scan on ignitemart.t_person_dim pd (cost=0.00..11,934.36 rows=534,036 width=64) (actual time=0.032..0.233 rows=200 loops=1)

  • Output: pd.person_uuid, pd.organization_uuid, pd.geog
  • Buffers: shared hit=3
4. 26,581.000 28,173.200 ↑ 1.0 1 200

Aggregate (cost=154,281.00..154,281.01 rows=1 width=1,600) (actual time=140.866..140.866 rows=1 loops=200)

  • Output: count(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval)))), count(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval)))), max(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval)))), max(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval)))), min(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval)))), min(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval)))), avg(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval)))), avg(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval)))), percentile_cont('0.25'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval)))), percentile_cont('0.25'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval)))), percentile_cont('0.5'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval)))), percentile_cont('0.5'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval)))), percentile_cont('0.75'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval)))), percentile_cont('0.75'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval)))), stddev_pop(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval)))), stddev_pop(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '500'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval)))), count(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval)))), count(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval)))), max(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval)))), max(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval)))), min(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval)))), min(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval)))), avg(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval)))), avg(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval)))), percentile_cont('0.25'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval)))), percentile_cont('0.25'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval)))), percentile_cont('0.5'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval)))), percentile_cont('0.5'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval)))), percentile_cont('0.75'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval)))), percentile_cont('0.75'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval)))), stddev_pop(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval)))), stddev_pop(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '1000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval)))), count(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval)))), count(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval)))), max(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval)))), max(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval)))), min(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval)))), min(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval)))), avg(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval)))), avg(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval)))), percentile_cont('0.25'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval)))), percentile_cont('0.25'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval)))), percentile_cont('0.5'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval)))), percentile_cont('0.5'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval)))), percentile_cont('0.75'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval)))), percentile_cont('0.75'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval)))), stddev_pop(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval)))), stddev_pop(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '2000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval)))), count(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval)))), count(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval)))), max(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval)))), max(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval)))), min(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval)))), min(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval)))), avg(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval)))), avg(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval)))), percentile_cont('0.25'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval)))), percentile_cont('0.25'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval)))), percentile_cont('0.5'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval)))), percentile_cont('0.5'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval)))), percentile_cont('0.75'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval)))), percentile_cont('0.75'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval)))), stddev_pop(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval)))), stddev_pop(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '3000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval)))), count(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval)))), count(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval)))), max(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval)))), max(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval)))), min(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval)))), min(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval)))), avg(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval)))), avg(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval)))), percentile_cont('0.25'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval)))), percentile_cont('0.25'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval)))), percentile_cont('0.5'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval)))), percentile_cont('0.5'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval)))), percentile_cont('0.75'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval)))), percentile_cont('0.75'::double precision) WITHIN GROUP (ORDER BY ((s.spent_amount)::double precision)) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval)))), stddev_pop(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '1 year'::interval)))), stddev_pop(s.spent_amount) FILTER (WHERE (st_dwithin(s.geog, pd.geog, '4000'::double precision, true) AND (s.activity_date > (now() - '2 years'::interval))))
  • Buffers: shared hit=343,009
5. 1,592.200 1,592.200 ↓ 14.3 1,088 200

Index Scan using "mdl_engage_actions_table_p1_yaddr_v4$idx2" on metrics.mdl_engage_actions_table_p1_yaddr_v4 s (cost=0.67..2,214.38 rows=76 width=45) (actual time=0.118..7.961 rows=1,088 loops=200)

  • Output: s.spent_amount, s.geog, s.activity_date
  • Index Cond: ((s.geog && _st_expand(pd.geog, '4000'::double precision)) AND (s.activity_date > (now() - '2 years'::interval)) AND (s.person_uuid <> pd.person_uuid))
  • Filter: st_dwithin(s.geog, pd.geog, '4000'::double precision, true)
  • Rows Removed by Filter: 544
  • Buffers: shared hit=342,649
Planning time : 3.336 ms
Execution time : 28,176.268 ms