0

I am trying to execute this SQL, but the query is really slow (almost 2 hours), It's actually querying 1 million records (property_ids), that's why it's slow. But can someone suggest me a strategy to make this query faster, may be doing this in batches etc.

WITH properties AS
    (SELECT sid FROM prod.vw_property WHERE status = 'active')
SELECT p.sid,
      -- active list count means not expired
      count(*) FILTER (WHERE cl.expired_at IS NULL) AS list_count_active,

      -- 30d list count DOES include those that are now expired (ie. listed and expired quickly within 30d)
      count(*) FILTER (WHERE (cl.listed_on >= ((now())::date - '30 days'::interval))) AS list_count_30d,

      -- All other aggregates should only include active (not expired) rew listings
      avg(cl.list_price) FILTER (WHERE cl.expired_at IS NULL) AS list_price_avg,
      percentile_cont(0.50) WITHIN GROUP (ORDER BY cl.list_price) FILTER (WHERE cl.expired_at IS NULL)::numeric(12, 2) AS list_price_median,
      min(cl.list_price) FILTER (WHERE cl.expired_at IS NULL) AS list_price_min,
      max(cl.list_price) FILTER (WHERE cl.expired_at IS NULL) AS list_price_max,
      avg((cl.list_price / (cl.interior_size_sqft)::numeric)) FILTER (WHERE (cl.interior_size_sqft > 100 AND cl.expired_at IS NULL)) AS list_price_per_sqft_avg,
      percentile_cont((0.50)::double precision) WITHIN GROUP (ORDER BY (((cl.list_price / (cl.interior_size_sqft)::numeric))::double precision))
          FILTER (WHERE (cl.interior_size_sqft > 100) AND cl.expired_at IS NULL)::numeric(12, 2) AS list_price_per_sqft_median,

      -- Show up to 10 closest listings that were used to get the aggregate numbers
      (array_agg(cl.rew_properties_id ORDER BY (st_distance(p.location, cl.location)))
           FILTER (WHERE cl.expired_at IS NULL))[1:10] AS closest_rew_properties_ids
     FROM prod.vw_property p
     INNER JOIN prod.vw_comparable_listing cl
     ON
       ((((COALESCE(upper(p.land_area_sqft), 0))::numeric >= cl.land_size_sqft_lower)
         AND ((COALESCE(upper(p.land_area_sqft), 0))::numeric <= cl.land_size_sqft_upper)
         AND (((upper(p.interior_area_finished_sqft))::numeric >= cl.interior_size_sqft_lower)
              AND ((upper(p.interior_area_finished_sqft))::numeric <= cl.interior_size_sqft_upper))
         AND (((p.num_bed)::numeric >= cl.num_bed_lower) AND ((p.num_bed)::numeric <= cl.num_bed_upper))
         -- Within 2km
         AND st_dwithin(p.location, cl.location, 2000) AND (
          CASE
              WHEN (p.rew_property_type = ANY (ARRAY['duplex'::prod.rew_property_type_enum, 'fourplex'::prod.rew_property_type_enum, 'triplex'::prod.rew_property_type_enum])) THEN 'townhouse'::prod.rew_property_type_enum
              ELSE p.rew_property_type
          END = cl.property_type_group)))
     JOIN properties ps ON ps.sid = p.sid
     GROUP BY 1;

Any suggestion will be appreciated. Also would like to add that AWS RDS large instance was at 52% due to this query.

Here is the Query plan:

QUERY PLAN
GroupAggregate  (cost=26994787.36..26994787.70 rows=1 width=212)
  Group Key: p.sid
  CTE properties
    ->  Seq Scan on property p_1  (cost=0.00..1260715.24 rows=2807666 width=4)
          Filter: ((latitude IS NOT NULL) AND (longitude IS NOT NULL) AND (rew_geographies_id IS NOT NULL) AND (status = 'active'::prod.source_record_status_type))
  ->  Sort  (cost=25734072.12..25734072.13 rows=1 width=122)
        Sort Key: p.sid
        ->  Nested Loop  (cost=0.54..25734072.11 rows=1 width=122)
              Join Filter: (p.sid = ps.sid)
              ->  Nested Loop Left Join  (cost=0.54..25642822.97 rows=1 width=122)
                    Join Filter: (pt.sid = p.property_type_sid)
"                    Filter: (CASE WHEN (pt.rew_property_type = ANY ('{duplex,fourplex,triplex}'::prod.rew_property_type_enum[])) THEN 'townhouse'::prod.rew_property_type_enum ELSE pt.rew_property_type END = cl.property_type_group)"
                    ->  Nested Loop  (cost=0.54..25641727.79 rows=36 width=130)
                          ->  Seq Scan on vw_comparable_listing cl  (cost=0.00..881.04 rows=28404 width=100)
                          ->  Index Scan using property_location_ix on property p  (cost=0.54..902.71 rows=1 width=96)
"                                Index Cond: (location && _st_expand(cl.location, '2000'::double precision))"
"                                Filter: ((latitude IS NOT NULL) AND (longitude IS NOT NULL) AND (rew_geographies_id IS NOT NULL) AND ((num_bed)::numeric >= cl.num_bed_lower) AND ((num_bed)::numeric <= cl.num_bed_upper) AND ((COALESCE(upper(land_area_sqft), 0))::numeric >= cl.land_size_sqft_lower) AND ((COALESCE(upper(land_area_sqft), 0))::numeric <= cl.land_size_sqft_upper) AND ((upper(interior_area_finished_sqft))::numeric >= cl.interior_size_sqft_lower) AND ((upper(interior_area_finished_sqft))::numeric <= cl.interior_size_sqft_upper) AND (cl.location && _st_expand(location, '2000'::double precision)) AND _st_dwithin(location, cl.location, '2000'::double precision, true))"
                    ->  Materialize  (cost=0.00..57.41 rows=1361 width=8)
                          ->  Seq Scan on property_type pt  (cost=0.00..50.61 rows=1361 width=8)
              ->  CTE Scan on properties ps  (cost=0.00..56153.32 rows=2807666 width=4)
3
2

Many parentheses are just distracting noise. Several casts seem unnecessary.
Most importantly, the CTE seems to do nothing useful, remove it and just keep the WHERE condition:

SELECT p.sid,
       -- active list count means not expired
       count(*) FILTER (WHERE cl.expired_at IS NULL) AS list_count_active,

       -- 30d list count DOES include those that are now expired (ie. listed and expired quickly within 30d)
       count(*) FILTER (WHERE cl.listed_on >= now()::date - 30) AS list_count_30d,

       -- All other aggregates should only include active (not expired) rew listings
       avg(cl.list_price) FILTER (WHERE cl.expired_at IS NULL) AS list_price_avg,
       percentile_cont(0.50) WITHIN GROUP (ORDER BY cl.list_price) FILTER (WHERE cl.expired_at IS NULL)::numeric(12, 2) AS list_price_median,
       min(cl.list_price) FILTER (WHERE cl.expired_at IS NULL) AS list_price_min,
       max(cl.list_price) FILTER (WHERE cl.expired_at IS NULL) AS list_price_max,
       avg(cl.list_price / cl.interior_size_sqft::numeric) FILTER (WHERE (cl.interior_size_sqft > 100 AND cl.expired_at IS NULL)) AS list_price_per_sqft_avg,
       percentile_cont(0.50) WITHIN GROUP (ORDER BY cl.list_price / cl.interior_size_sqft::double precision)
          FILTER (WHERE cl.interior_size_sqft > 100 AND cl.expired_at IS NULL)::numeric(12, 2) AS list_price_per_sqft_median,

       -- Show up to 10 closest listings that were used to get the aggregate numbers
       (array_agg(cl.rew_properties_id ORDER BY st_distance(p.location, cl.location) FILTER (WHERE cl.expired_at IS NULL)))[1:10] AS closest_rew_properties_ids
FROM   prod.vw_property           p
JOIN   prod.vw_comparable_listing cl ON ST_DWITHIN(p.location, cl.location, 2000)  -- within 2km
WHERE  COALESCE(p.land_area_sqft, 0) BETWEEN cl.land_size_sqft_lower     AND cl.land_size_sqft_upper
AND    p.interior_area_finished_sqft BETWEEN cl.interior_size_sqft_lower AND cl.interior_size_sqft_upper
AND    p.num_bed                     BETWEEN cl.num_bed_lower            AND cl.num_bed_upper
AND    CASE WHEN p.rew_property_type = ANY ('{duplex,fourplex,triplex}'::prod.rew_property_type_enum[])
          THEN cl.property_type_group = 'townhouse'::prod.rew_property_type_enum
          ELSE cl.property_type_group = p.rew_property_type
       END
AND    p.status = 'active'
GROUP  BY 1;

upper in (COALESCE(upper(p.land_area_sqft), 0))::numeric >= cl.land_size_sqft_lower doesn't do anything useful. Nor (probably) does the cast to numeric. (And I am not sure you need COALESCE. Can the range [cl.land_size_sqft_lower, cl.land_size_sqft_upper] contain 0?)

Assuming that p.land_area_sqft etc are of some numeric type, not text, else you need to add back casts - and reconsider data types of your table columns.

(array_agg(...))[1:10] can be needlessly expensive if there can be substantially more than 10 elements in the resulting array. A custom aggregate function that stops adding elements after nr. 10 helps is this case.

You already have an index on property(location). If you are only interested in the 10 closest ("nearest") locations, consider and actual "nearest neighbour" search:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.