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)
Created
December 20, 2022 13:17
-
-
Save gingerwizard/97dba6dc9ace47fbd7b8e53bdf8d1887 to your computer and use it in GitHub Desktop.
Explain - Postcodes in London with the largest percentage price change in the last 20 yrs
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment