Explain - Postcodes in London with the largest percentage price change in the last 20 yrs

query3.md

postgres=> EXPLAIN SELECT med_2002.postcode1, median_2002, median_2022, round(((median_2022 - median_2002)/median_2002) * 100) AS percent_change FROM (
  SELECT postcode1, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price) AS median_2002 FROM uk_price_paid WHERE town = 'LONDON' AND extract(year from date) = '2002' GROUP BY postcode1

) med_2002 INNER JOIN (
  SELECT postcode1, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price) AS median_2022 FROM uk_price_paid WHERE town = 'LONDON' AND extract(year from date) = '2022' GROUP BY postcode1
) med_2022 ON med_2002.postcode1=med_2022.postcode1 ORDER BY percent_change DESC LIMIT 10
postgres-> ;
                                                                                                                                                                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=461020.26..461020.29 rows=10 width=28)
   ->  Sort  (cost=461020.26..461077.24 rows=22791 width=28)
         Sort Key: (round(((((percentile_cont('0.5'::double precision) WITHIN GROUP (ORDER BY ((uk_price_paid_1.price)::double precision))) - (percentile_cont('0.5'::double precision) WITHIN GROUP (ORDER BY ((uk_price_paid.price)::double precision)))) / (percentile_cont('0.5'::double precision) WITHIN GROUP (ORDER BY ((uk_price_paid.price)::double precision)))) * '100'::double precision))) DESC
         ->  Merge Join  (cost=445467.89..460527.76 rows=22791 width=28)
               Merge Cond: ((uk_price_paid.postcode1)::text = (uk_price_paid_1.postcode1)::text)
               ->  GroupAggregate  (cost=369713.90..383788.78 rows=2135 width=12)
                     Group Key: uk_price_paid.postcode1
                     ->  Gather Merge  (cost=369713.90..382894.71 rows=115651 width=8)
                           Workers Planned: 1
                           ->  Sort  (cost=368713.89..368883.96 rows=68030 width=8)
                                 Sort Key: uk_price_paid.postcode1
                                 ->  Parallel Bitmap Heap Scan on uk_price_paid  (cost=34657.34..363253.16 rows=68030 width=8)
                                       Recheck Cond: ((EXTRACT(year FROM date) = '2002'::numeric) AND ((town)::text = 'LONDON'::text))
                                       ->  BitmapAnd  (cost=34657.34..34657.34 rows=115651 width=0)
                                             ->  Bitmap Index Scan on uk_price_paid_extract_idx  (cost=0.00..14829.95 rows=1434879 width=0)
                                                   Index Cond: (EXTRACT(year FROM date) = '2002'::numeric)
                                             ->  Bitmap Index Scan on uk_price_paid_town_idx  (cost=0.00..19769.31 rows=2355423 width=0)
                                                   Index Cond: ((town)::text = 'LONDON'::text)
               ->  GroupAggregate  (cost=75754.00..76229.78 rows=2135 width=12)
                     Group Key: uk_price_paid_1.postcode1
                     ->  Sort  (cost=75754.00..75866.27 rows=44910 width=8)
                           Sort Key: uk_price_paid_1.postcode1
                           ->  Bitmap Heap Scan on uk_price_paid uk_price_paid_1  (cost=25551.16..72283.63 rows=44910 width=8)
                                 Recheck Cond: ((EXTRACT(year FROM date) = '2022'::numeric) AND ((town)::text = 'LONDON'::text))
                                 ->  BitmapAnd  (cost=25551.16..25551.16 rows=44910 width=0)
                                       ->  Bitmap Index Scan on uk_price_paid_extract_idx  (cost=0.00..5759.14 rows=557197 width=0)
                                             Index Cond: (EXTRACT(year FROM date) = '2022'::numeric)
                                       ->  Bitmap Index Scan on uk_price_paid_town_idx  (cost=0.00..19769.31 rows=2355423 width=0)
                                             Index Cond: ((town)::text = 'LONDON'::text)
(29 rows)