Skip to content

Instantly share code, notes, and snippets.

@gingerwizard
Last active December 20, 2022 13:18
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save gingerwizard/4c6681954976d0fd5f056d77488333ac to your computer and use it in GitHub Desktop.
Explain - Most expensive postcodes in a city
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)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment