postgres=> EXPLAIN ANALYZE SELECT
postcode1,
round(avg(price)) AS price
FROM uk_price_paid WHERE town='BRISTOL'
GROUP BY postcode1
ORDER BY price DESC LIMIT 10;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=443330.38..443330.40 rows=10 width=36) (actual time=461.972..462.061 rows=10 loops=1)
-> Sort (cost=443330.38..443335.71 rows=2135 width=36) (actual time=461.971..462.057 rows=10 loops=1)
Sort Key: (round(avg(price), 0)) DESC
Sort Method: top-N heapsort Memory: 25kB
-> Finalize GroupAggregate (cost=442996.01..443284.24 rows=2135 width=36) (actual time=461.832..462.001 rows=33 loops=1)
Group Key: postcode1
-> Gather Merge (cost=442996.01..443241.54 rows=2135 width=36) (actual time=461.799..461.893 rows=33 loops=1)
Workers Planned: 1
Workers Launched: 0
-> Sort (cost=441996.00..442001.34 rows=2135 width=36) (actual time=461.467..461.471 rows=33 loops=1)
Sort Key: postcode1
Sort Method: quicksort Memory: 27kB
-> Partial HashAggregate (cost=441856.59..441877.94 rows=2135 width=36) (actual time=461.373..461.421 rows=33 loops=1)
Group Key: postcode1
Batches: 1 Memory Usage: 121kB
-> Parallel Index Scan using uk_price_paid_town_idx on uk_price_paid (cost=0.44..440452.71 rows=280776 width=8) (actual time=0.061..165.987 rows=425000 loops=1)
Index Cond: ((town)::text = 'BRISTOL'::text)
Planning Time: 0.266 ms
Execution Time: 462.168 ms
(19 rows)
Last active
December 20, 2022 13:18
-
-
Save gingerwizard/4c6681954976d0fd5f056d77488333ac to your computer and use it in GitHub Desktop.
Explain - Most expensive postcodes in a city
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment