UK Property Price Paid
The dataset contains data about prices paid for real-estate property in England and Wales. The data is available since year 1995. The size of the dataset in uncompressed form is about 4 GiB and it will take about 278 MiB in ClickHouse.
Source: https://www.gov.uk/government/statistical-data-sets/price-paid-data-downloads Description of the fields: https://www.gov.uk/guidance/about-the-price-paid-data
Contains HM Land Registry data Β© Crown copyright and database right 2021. This data is licensed under the Open Government Licence v3.0.
Download the Datasetβ
Run the command:
wget http://prod.publicdata.landregistry.gov.uk.s3-website-eu-west-1.amazonaws.com/pp-complete.csv
Download will take about 2 minutes with good internet connection.
Create the Tableβ
CREATE TABLE uk_price_paid
(
price UInt32,
date Date,
postcode1 LowCardinality(String),
postcode2 LowCardinality(String),
type Enum8('terraced' = 1, 'semi-detached' = 2, 'detached' = 3, 'flat' = 4, 'other' = 0),
is_new UInt8,
duration Enum8('freehold' = 1, 'leasehold' = 2, 'unknown' = 0),
addr1 String,
addr2 String,
street LowCardinality(String),
locality LowCardinality(String),
town LowCardinality(String),
district LowCardinality(String),
county LowCardinality(String),
category UInt8
) ENGINE = MergeTree ORDER BY (postcode1, postcode2, addr1, addr2);
Preprocess and Import Dataβ
We will use clickhouse-local
tool for data preprocessing and clickhouse-client
to upload it.
In this example, we define the structure of source data from the CSV file and specify a query to preprocess the data with clickhouse-local
.
The preprocessing is:
- splitting the postcode to two different columns
postcode1
andpostcode2
that is better for storage and queries; - coverting the
time
field to date as it only contains 00:00 time; - ignoring the UUid field because we don't need it for analysis;
- transforming
type
andduration
to more readable Enum fields with function transform; - transforming
is_new
andcategory
fields from single-character string (Y
/N
andA
/B
) to UInt8 field with 0 and 1.
Preprocessed data is piped directly to clickhouse-client
to be inserted into ClickHouse table in streaming fashion.
clickhouse-local --input-format CSV --structure '
uuid String,
price UInt32,
time DateTime,
postcode String,
a String,
b String,
c String,
addr1 String,
addr2 String,
street String,
locality String,
town String,
district String,
county String,
d String,
e String
' --query "
WITH splitByChar(' ', postcode) AS p
SELECT
price,
toDate(time) AS date,
p[1] AS postcode1,
p[2] AS postcode2,
transform(a, ['T', 'S', 'D', 'F', 'O'], ['terraced', 'semi-detached', 'detached', 'flat', 'other']) AS type,
b = 'Y' AS is_new,
transform(c, ['F', 'L', 'U'], ['freehold', 'leasehold', 'unknown']) AS duration,
addr1,
addr2,
street,
locality,
town,
district,
county,
d = 'B' AS category
FROM table" --date_time_input_format best_effort < pp-complete.csv | clickhouse-client --query "INSERT INTO uk_price_paid FORMAT TSV"
It will take about 40 seconds.
Validate the Dataβ
Query:
SELECT count() FROM uk_price_paid;
Result:
βββcount()ββ
β 26321785 β
ββββββββββββ
The size of dataset in ClickHouse is just 278 MiB, check it.
Query:
SELECT formatReadableSize(total_bytes) FROM system.tables WHERE name = 'uk_price_paid';
Result:
ββformatReadableSize(total_bytes)ββ
β 278.80 MiB β
βββββββββββββββββββββββββββββββββββ
Run Some Queriesβ
Query 1. Average Price Per Yearβ
Query:
SELECT toYear(date) AS year, round(avg(price)) AS price, bar(price, 0, 1000000, 80) FROM uk_price_paid GROUP BY year ORDER BY year;
Result:
ββyearββ¬ββpriceββ¬βbar(round(avg(price)), 0, 1000000, 80)ββ
β 1995 β 67932 β ββββββ β
β 1996 β 71505 β ββββββ β
β 1997 β 78532 β βββββββ β
β 1998 β 85436 β βββββββ β
β 1999 β 96037 β ββββββββ β
β 2000 β 107479 β βββββββββ β
β 2001 β 118885 β ββββββββββ β
β 2002 β 137941 β βββββββββββ β
β 2003 β 155889 β βββββββββββββ β
β 2004 β 178885 β βββββββββββββββ β
β 2005 β 189351 β ββββββββββββββββ β
β 2006 β 203528 β βββββββββββββββββ β
β 2007 β 219378 β ββββββββββββββββββ β
β 2008 β 217056 β ββββββββββββββββββ β
β 2009 β 213419 β βββββββββββββββββ β
β 2010 β 236109 β βββββββββββββββββββ β
β 2011 β 232805 β βββββββββββββββββββ β
β 2012 β 238367 β βββββββββββββββββββ β
β 2013 β 256931 β βββββββββββββββββββββ β
β 2014 β 279915 β βββββββββββββββββββββββ β
β 2015 β 297266 β ββββββββββββββββββββββββ β
β 2016 β 313201 β βββββββββββββββββββββββββ β
β 2017 β 346097 β ββββββββββββββββββββββββββββ β
β 2018 β 350116 β ββββββββββββββββββββββββββββ β
β 2019 β 351013 β ββββββββββββββββββββββββββββ β
β 2020 β 369420 β ββββββββββββββββββββββββββββββ β
β 2021 β 386903 β βββββββββββββββββββββββββββββββ β
ββββββββ΄βββββββββ΄βββββββββββββββββββββββββββββββββββββββββ
Query 2. Average Price per Year in Londonβ
Query:
SELECT toYear(date) AS year, round(avg(price)) AS price, bar(price, 0, 2000000, 100) FROM uk_price_paid WHERE town = 'LONDON' GROUP BY year ORDER BY year;
Result:
ββyearββ¬βββpriceββ¬βbar(round(avg(price)), 0, 2000000, 100)ββββββββββββββββ
β 1995 β 109116 β ββββββ β
β 1996 β 118667 β ββββββ β
β 1997 β 136518 β βββββββ β
β 1998 β 152983 β ββββββββ β
β 1999 β 180637 β βββββββββ β
β 2000 β 215838 β βββββββββββ β
β 2001 β 232994 β ββββββββββββ β
β 2002 β 263670 β ββββββββββββββ β
β 2003 β 278394 β ββββββββββββββ β
β 2004 β 304666 β ββββββββββββββββ β
β 2005 β 322875 β βββββββββββββββββ β
β 2006 β 356191 β ββββββββββββββββββ β
β 2007 β 404054 β βββββββββββββββββββββ β
β 2008 β 420741 β βββββββββββββββββββββ β
β 2009 β 427753 β ββββββββββββββββββββββ β
β 2010 β 480306 β ββββββββββββββββββββββββ β
β 2011 β 496274 β βββββββββββββββββββββββββ β
β 2012 β 519442 β ββββββββββββββββββββββββββ β
β 2013 β 616212 β βββββββββββββββββββββββββββββββ β
β 2014 β 724154 β βββββββββββββββββββββββββββββββββββββ β
β 2015 β 792129 β ββββββββββββββββββββββββββββββββββββββββ β
β 2016 β 843655 β βββββββββββββββββββββββββββββββββββββββββββ β
β 2017 β 982642 β ββββββββββββββββββββββββββββββββββββββββββββββββββ β
β 2018 β 1016835 β βββββββββββββββββββββββββββββββββββββββββββββββββββ β
β 2019 β 1042849 β βββββββββββββββββββββββββββββββββββββββββββββββββββββ β
β 2020 β 1011889 β βββββββββββββββββββββββββββββββββββββββββββββββββββ β
β 2021 β 960343 β ββββββββββββββββββββββββββββββββββββββββββββββββ β
ββββββββ΄ββββββββββ΄ββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
Something happened in 2013. I don't have a clue. Maybe you have a clue what happened in 2020?
Query 3. The Most Expensive Neighborhoodsβ
Query:
SELECT
town,
district,
count() AS c,
round(avg(price)) AS price,
bar(price, 0, 5000000, 100)
FROM uk_price_paid
WHERE date >= '2020-01-01'
GROUP BY
town,
district
HAVING c >= 100
ORDER BY price DESC
LIMIT 100;
Result:
ββtownββββββββββββββββββ¬βdistrictββββββββββββββββ¬ββββcββ¬βββpriceββ¬βbar(round(avg(price)), 0, 5000000, 100)βββββββββββββββββββββββββββββ
β LONDON β CITY OF WESTMINSTER β 3606 β 3280239 β ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β
β LONDON β CITY OF LONDON β 274 β 3160502 β ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β
β LONDON β KENSINGTON AND CHELSEA β 2550 β 2308478 β βββββββββββββββββββββββββββββββββββββββββββββββ β
β LEATHERHEAD β ELMBRIDGE β 114 β 1897407 β ββββββββββββββββββββββββββββββββββββββ β
β LONDON β CAMDEN β 3033 β 1805404 β ββββββββββββββββββββββββββββββββββββ β
β VIRGINIA WATER β RUNNYMEDE β 156 β 1753247 β βββββββββββββββββββββββββββββββββββ β
β WINDLESHAM β SURREY HEATH β 108 β 1677613 β ββββββββββββββββββββββββββββββββββ β
β THORNTON HEATH β CROYDON β 546 β 1671721 β ββββββββββββββββββββββββββββββββββ β
β BARNET β ENFIELD β 124 β 1505840 β ββββββββββββββββββββββββββββββ β
β COBHAM β ELMBRIDGE β 387 β 1237250 β βββββββββββββββββββββββββ β
β LONDON β ISLINGTON β 2668 β 1236980 β βββββββββββββββββββββββββ β
β OXFORD β SOUTH OXFORDSHIRE β 321 β 1220907 β βββββββββββββββββββββββββ β
β LONDON β RICHMOND UPON THAMES β 704 β 1215551 β βββββββββββββββββββββββββ β
β LONDON β HOUNSLOW β 671 β 1207493 β βββββββββββββββββββββββββ β
β ASCOT β WINDSOR AND MAIDENHEAD β 407 β 1183299 β ββββββββββββββββββββββββ β
β BEACONSFIELD β BUCKINGHAMSHIRE β 330 β 1175615 β ββββββββββββββββββββββββ β
β RICHMOND β RICHMOND UPON THAMES β 874 β 1110444 β βββββββββββββββββββββββ β
β LONDON β HAMMERSMITH AND FULHAM β 3086 β 1053983 β βββββββββββββββββββββ β
β SURBITON β ELMBRIDGE β 100 β 1011800 β βββββββββββββββββββββ β
β RADLETT β HERTSMERE β 283 β 1011712 β βββββββββββββββββββββ β
β SALCOMBE β SOUTH HAMS β 127 β 1011624 β βββββββββββββββββββββ β
β WEYBRIDGE β ELMBRIDGE β 655 β 1007265 β βββββββββββββββββββββ β
β ESHER β ELMBRIDGE β 485 β 986581 β ββββββββββββββββββββ β
β LEATHERHEAD β GUILDFORD β 202 β 977320 β ββββββββββββββββββββ β
β BURFORD β WEST OXFORDSHIRE β 111 β 966893 β ββββββββββββββββββββ β
β BROCKENHURST β NEW FOREST β 129 β 956675 β ββββββββββββββββββββ β
β HINDHEAD β WAVERLEY β 137 β 953753 β βββββββββββββββββββ β
β GERRARDS CROSS β BUCKINGHAMSHIRE β 419 β 951121 β βββββββββββββββββββ β
β EAST MOLESEY β ELMBRIDGE β 192 β 936769 β βββββββββββββββββββ β
β CHALFONT ST GILES β BUCKINGHAMSHIRE β 146 β 925515 β βββββββββββββββββββ β
β LONDON β TOWER HAMLETS β 4388 β 918304 β βββββββββββββββββββ β
β OLNEY β MILTON KEYNES β 235 β 910646 β βββββββββββββββββββ β
β HENLEY-ON-THAMES β SOUTH OXFORDSHIRE β 540 β 902418 β ββββββββββββββββββ β
β LONDON β SOUTHWARK β 3885 β 892997 β ββββββββββββββββββ β
β KINGSTON UPON THAMES β KINGSTON UPON THAMES β 960 β 885969 β ββββββββββββββββββ β
β LONDON β EALING β 2658 β 871755 β ββββββββββββββββββ β
β CRANBROOK β TUNBRIDGE WELLS β 431 β 862348 β ββββββββββββββββββ β
β LONDON β MERTON β 2099 β 859118 β ββββββββββββββββββ β
β BELVEDERE β BEXLEY β 346 β 842423 β βββββββββββββββββ β
β GUILDFORD β WAVERLEY β 143 β 841277 β βββββββββββββββββ β
β HARPENDEN β ST ALBANS β 657 β 841216 β βββββββββββββββββ β
β LONDON β HACKNEY β 3307 β 837090 β βββββββββββββββββ β
β LONDON β WANDSWORTH β 6566 β 832663 β βββββββββββββββββ β
β MAIDENHEAD β BUCKINGHAMSHIRE β 123 β 824299 β βββββββββββββββββ β
β KINGS LANGLEY β DACORUM β 145 β 821331 β βββββββββββββββββ β
β BERKHAMSTED β DACORUM β 543 β 818415 β βββββββββββββββββ β
β GREAT MISSENDEN β BUCKINGHAMSHIRE β 226 β 802807 β ββββββββββββββββ β
β BILLINGSHURST β CHICHESTER β 144 β 797829 β ββββββββββββββββ β
β WOKING β GUILDFORD β 176 β 793494 β ββββββββββββββββ β
β STOCKBRIDGE β TEST VALLEY β 178 β 793269 β ββββββββββββββββ β
β EPSOM β REIGATE AND BANSTEAD β 172 β 791862 β ββββββββββββββββ β
β TONBRIDGE β TUNBRIDGE WELLS β 360 β 787876 β ββββββββββββββββ β
β TEDDINGTON β RICHMOND UPON THAMES β 595 β 786492 β ββββββββββββββββ β
β TWICKENHAM β RICHMOND UPON THAMES β 1155 β 786193 β ββββββββββββββββ β
β LYNDHURST β NEW FOREST β 102 β 785593 β ββββββββββββββββ β
β LONDON β LAMBETH β 5228 β 774574 β ββββββββββββββββ β
β LONDON β BARNET β 3955 β 773259 β ββββββββββββββββ β
β OXFORD β VALE OF WHITE HORSE β 353 β 772088 β ββββββββββββββββ β
β TONBRIDGE β MAIDSTONE β 305 β 770740 β ββββββββββββββββ β
β LUTTERWORTH β HARBOROUGH β 538 β 768634 β ββββββββββββββββ β
β WOODSTOCK β WEST OXFORDSHIRE β 140 β 766037 β ββββββββββββββββ β
β MIDHURST β CHICHESTER β 257 β 764815 β ββββββββββββββββ β
β MARLOW β BUCKINGHAMSHIRE β 327 β 761876 β ββββββββββββββββ β
β LONDON β NEWHAM β 3237 β 761784 β ββββββββββββββββ β
β ALDERLEY EDGE β CHESHIRE EAST β 178 β 757318 β ββββββββββββββββ β
β LUTON β CENTRAL BEDFORDSHIRE β 212 β 754283 β βββββββββββββββ β
β PETWORTH β CHICHESTER β 154 β 754220 β βββββββββββββββ β
β ALRESFORD β WINCHESTER β 219 β 752718 β βββββββββββββββ β
β POTTERS BAR β WELWYN HATFIELD β 174 β 748465 β βββββββββββββββ β
β HASLEMERE β CHICHESTER β 128 β 746907 β βββββββββββββββ β
β TADWORTH β REIGATE AND BANSTEAD β 502 β 743252 β βββββββββββββββ β
β THAMES DITTON β ELMBRIDGE β 244 β 741913 β βββββββββββββββ β
β REIGATE β REIGATE AND BANSTEAD β 581 β 738198 β βββββββββββββββ β
β BOURNE END β BUCKINGHAMSHIRE β 138 β 735190 β βββββββββββββββ β
β SEVENOAKS β SEVENOAKS β 1156 β 730018 β βββββββββββββββ β
β OXTED β TANDRIDGE β 336 β 729123 β βββββββββββββββ β
β INGATESTONE β BRENTWOOD β 166 β 728103 β βββββββββββββββ β
β LONDON β BRENT β 2079 β 720605 β βββββββββββββββ β
β LONDON β HARINGEY β 3216 β 717780 β βββββββββββββββ β
β PURLEY β CROYDON β 575 β 716108 β βββββββββββββββ β
β WELWYN β WELWYN HATFIELD β 222 β 710603 β βββββββββββββββ β
β RICKMANSWORTH β THREE RIVERS β 798 β 704571 β ββββββββββββββ β
β BANSTEAD β REIGATE AND BANSTEAD β 401 β 701293 β ββββββββββββββ β
β CHIGWELL β EPPING FOREST β 261 β 701203 β ββββββββββββββ β
β PINNER β HARROW β 528 β 698885 β ββββββββββββββ β
β HASLEMERE β WAVERLEY β 280 β 696659 β ββββββββββββββ β
β SLOUGH β BUCKINGHAMSHIRE β 396 β 694917 β ββββββββββββββ β
β WALTON-ON-THAMES β ELMBRIDGE β 946 β 692395 β ββββββββββββββ β
β READING β SOUTH OXFORDSHIRE β 318 β 691988 β ββββββββββββββ β
β NORTHWOOD β HILLINGDON β 271 β 690643 β ββββββββββββββ β
β FELTHAM β HOUNSLOW β 763 β 688595 β ββββββββββββββ β
β ASHTEAD β MOLE VALLEY β 303 β 687923 β ββββββββββββββ β
β BARNET β BARNET β 975 β 686980 β ββββββββββββββ β
β WOKING β SURREY HEATH β 283 β 686669 β ββββββββββββββ β
β MALMESBURY β WILTSHIRE β 323 β 683324 β ββββββββββββββ β
β AMERSHAM β BUCKINGHAMSHIRE β 496 β 680962 β ββββββββββββββ β
β CHISLEHURST β BROMLEY β 430 β 680209 β ββββββββββββββ β
β HYTHE β FOLKESTONE AND HYTHE β 490 β 676908 β ββββββββββββββ β
β MAYFIELD β WEALDEN β 101 β 676210 β ββββββββββββββ β
β ASCOT β BRACKNELL FOREST β 168 β 676004 β ββββββββββββββ β
ββββββββββββββββββββββββ΄βββββββββββββββββββββββββ΄βββββββ΄ββββββββββ΄βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
Let's Speed Up Queries Using Projectionsβ
Projections allow to improve queries speed by storing pre-aggregated data.
Build a Projectionβ
Create an aggregate projection by dimensions toYear(date)
, district
, town
:
ALTER TABLE uk_price_paid
ADD PROJECTION projection_by_year_district_town
(
SELECT
toYear(date),
district,
town,
avg(price),
sum(price),
count()
GROUP BY
toYear(date),
district,
town
);
Populate the projection for existing data (without it projection will be created for only newly inserted data):
ALTER TABLE uk_price_paid
MATERIALIZE PROJECTION projection_by_year_district_town
SETTINGS mutations_sync = 1;
Test Performanceβ
Let's run the same 3 queries.
Enable projections for selects:
SET allow_experimental_projection_optimization = 1;
Query 1. Average Price Per Yearβ
Query:
SELECT
toYear(date) AS year,
round(avg(price)) AS price,
bar(price, 0, 1000000, 80)
FROM uk_price_paid
GROUP BY year
ORDER BY year ASC;
Result:
ββyearββ¬ββpriceββ¬βbar(round(avg(price)), 0, 1000000, 80)ββ
β 1995 β 67932 β ββββββ β
β 1996 β 71505 β ββββββ β
β 1997 β 78532 β βββββββ β
β 1998 β 85436 β βββββββ β
β 1999 β 96037 β ββββββββ β
β 2000 β 107479 β βββββββββ β
β 2001 β 118885 β ββββββββββ β
β 2002 β 137941 β βββββββββββ β
β 2003 β 155889 β βββββββββββββ β
β 2004 β 178885 β βββββββββββββββ β
β 2005 β 189351 β ββββββββββββββββ β
β 2006 β 203528 β βββββββββββββββββ β
β 2007 β 219378 β ββββββββββββββββββ β
β 2008 β 217056 β ββββββββββββββββββ β
β 2009 β 213419 β βββββββββββββββββ β
β 2010 β 236109 β βββββββββββββββββββ β
β 2011 β 232805 β βββββββββββββββββββ β
β 2012 β 238367 β βββββββββββββββββββ β
β 2013 β 256931 β βββββββββββββββββββββ β
β 2014 β 279915 β βββββββββββββββββββββββ β
β 2015 β 297266 β ββββββββββββββββββββββββ β
β 2016 β 313201 β βββββββββββββββββββββββββ β
β 2017 β 346097 β ββββββββββββββββββββββββββββ β
β 2018 β 350116 β ββββββββββββββββββββββββββββ β
β 2019 β 351013 β ββββββββββββββββββββββββββββ β
β 2020 β 369420 β ββββββββββββββββββββββββββββββ β
β 2021 β 386903 β βββββββββββββββββββββββββββββββ β
ββββββββ΄βββββββββ΄βββββββββββββββββββββββββββββββββββββββββ
Query 2. Average Price Per Year in Londonβ
Query:
SELECT
toYear(date) AS year,
round(avg(price)) AS price,
bar(price, 0, 2000000, 100)
FROM uk_price_paid
WHERE town = 'LONDON'
GROUP BY year
ORDER BY year ASC;
Result:
ββyearββ¬βββpriceββ¬βbar(round(avg(price)), 0, 2000000, 100)ββββββββββββββββ
β 1995 β 109116 β ββββββ β
β 1996 β 118667 β ββββββ β
β 1997 β 136518 β βββββββ β
β 1998 β 152983 β ββββββββ β
β 1999 β 180637 β βββββββββ β
β 2000 β 215838 β βββββββββββ β
β 2001 β 232994 β ββββββββββββ β
β 2002 β 263670 β ββββββββββββββ β
β 2003 β 278394 β ββββββββββββββ β
β 2004 β 304666 β ββββββββββββββββ β
β 2005 β 322875 β βββββββββββββββββ β
β 2006 β 356191 β ββββββββββββββββββ β
β 2007 β 404054 β βββββββββββββββββββββ β
β 2008 β 420741 β βββββββββββββββββββββ β
β 2009 β 427753 β ββββββββββββββββββββββ β
β 2010 β 480306 β ββββββββββββββββββββββββ β
β 2011 β 496274 β βββββββββββββββββββββββββ β
β 2012 β 519442 β ββββββββββββββββββββββββββ β
β 2013 β 616212 β βββββββββββββββββββββββββββββββ β
β 2014 β 724154 β βββββββββββββββββββββββββββββββββββββ β
β 2015 β 792129 β ββββββββββββββββββββββββββββββββββββββββ β
β 2016 β 843655 β βββββββββββββββββββββββββββββββββββββββββββ β
β 2017 β 982642 β ββββββββββββββββββββββββββββββββββββββββββββββββββ β
β 2018 β 1016835 β βββββββββββββββββββββββββββββββββββββββββββββββββββ β
β 2019 β 1042849 β βββββββββββββββββββββββββββββββββββββββββββββββββββββ β
β 2020 β 1011889 β βββββββββββββββββββββββββββββββββββββββββββββββββββ β
β 2021 β 960343 β ββββββββββββββββββββββββββββββββββββββββββββββββ β
ββββββββ΄ββββββββββ΄ββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
Query 3. The Most Expensive Neighborhoodsβ
The condition (date >= '2020-01-01') needs to be modified to match projection dimension (toYear(date) >= 2020).
Query:
SELECT
town,
district,
count() AS c,
round(avg(price)) AS price,
bar(price, 0, 5000000, 100)
FROM uk_price_paid
WHERE toYear(date) >= 2020
GROUP BY
town,
district
HAVING c >= 100
ORDER BY price DESC
LIMIT 100;
Result:
ββtownββββββββββββββββββ¬βdistrictββββββββββββββββ¬ββββcββ¬βββpriceββ¬βbar(round(avg(price)), 0, 5000000, 100)βββββββββββββββββββββββββββββ
β LONDON β CITY OF WESTMINSTER β 3606 β 3280239 β ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β
β LONDON β CITY OF LONDON β 274 β 3160502 β ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β
β LONDON β KENSINGTON AND CHELSEA β 2550 β 2308478 β βββββββββββββββββββββββββββββββββββββββββββββββ β
β LEATHERHEAD β ELMBRIDGE β 114 β 1897407 β ββββββββββββββββββββββββββββββββββββββ β
β LONDON β CAMDEN β 3033 β 1805404 β ββββββββββββββββββββββββββββββββββββ β
β VIRGINIA WATER β RUNNYMEDE β 156 β 1753247 β βββββββββββββββββββββββββββββββββββ β
β WINDLESHAM β SURREY HEATH β 108 β 1677613 β ββββββββββββββββββββββββββββββββββ β
β THORNTON HEATH β CROYDON β 546 β 1671721 β ββββββββββββββββββββββββββββββββββ β
β BARNET β ENFIELD β 124 β 1505840 β ββββββββββββββββββββββββββββββ β
β COBHAM β ELMBRIDGE β 387 β 1237250 β βββββββββββββββββββββββββ β
β LONDON β ISLINGTON β 2668 β 1236980 β βββββββββββββββββββββββββ β
β OXFORD β SOUTH OXFORDSHIRE β 321 β 1220907 β βββββββββββββββββββββββββ β
β LONDON β RICHMOND UPON THAMES β 704 β 1215551 β βββββββββββββββββββββββββ β
β LONDON β HOUNSLOW β 671 β 1207493 β βββββββββββββββββββββββββ β
β ASCOT β WINDSOR AND MAIDENHEAD β 407 β 1183299 β ββββββββββββββββββββββββ β
β BEACONSFIELD β BUCKINGHAMSHIRE β 330 β 1175615 β ββββββββββββββββββββββββ β
β RICHMOND β RICHMOND UPON THAMES β 874 β 1110444 β βββββββββββββββββββββββ β
β LONDON β HAMMERSMITH AND FULHAM β 3086 β 1053983 β βββββββββββββββββββββ β
β SURBITON β ELMBRIDGE β 100 β 1011800 β βββββββββββββββββββββ β
β RADLETT β HERTSMERE β 283 β 1011712 β βββββββββββββββββββββ β
β SALCOMBE β SOUTH HAMS β 127 β 1011624 β βββββββββββββββββββββ β
β WEYBRIDGE β ELMBRIDGE β 655 β 1007265 β βββββββββββββββββββββ β
β ESHER β ELMBRIDGE β 485 β 986581 β ββββββββββββββββββββ β
β LEATHERHEAD β GUILDFORD β 202 β 977320 β ββββββββββββββββββββ β
β BURFORD β WEST OXFORDSHIRE β 111 β 966893 β ββββββββββββββββββββ β
β BROCKENHURST β NEW FOREST β 129 β 956675 β ββββββββββββββββββββ β
β HINDHEAD β WAVERLEY β 137 β 953753 β βββββββββββββββββββ β
β GERRARDS CROSS β BUCKINGHAMSHIRE β 419 β 951121 β βββββββββββββββββββ β
β EAST MOLESEY β ELMBRIDGE β 192 β 936769 β βββββββββββββββββββ β
β CHALFONT ST GILES β BUCKINGHAMSHIRE β 146 β 925515 β βββββββββββββββββββ β
β LONDON β TOWER HAMLETS β 4388 β 918304 β βββββββββββββββββββ β
β OLNEY β MILTON KEYNES β 235 β 910646 β βββββββββββββββββββ β
β HENLEY-ON-THAMES β SOUTH OXFORDSHIRE β 540 β 902418 β ββββββββββββββββββ β
β LONDON β SOUTHWARK β 3885 β 892997 β ββββββββββββββββββ β
β KINGSTON UPON THAMES β KINGSTON UPON THAMES β 960 β 885969 β ββββββββββββββββββ β
β LONDON β EALING β 2658 β 871755 β ββββββββββββββββββ β
β CRANBROOK β TUNBRIDGE WELLS β 431 β 862348 β ββββββββββββββββββ β
β LONDON β MERTON β 2099 β 859118 β ββββββββββββββββββ β
β BELVEDERE β BEXLEY β 346 β 842423 β βββββββββββββββββ β
β GUILDFORD β WAVERLEY β 143 β 841277 β βββββββββββββββββ β
β HARPENDEN β ST ALBANS β 657 β 841216 β βββββββββββββββββ β
β LONDON β HACKNEY β 3307 β 837090 β βββββββββββββββββ β
β LONDON β WANDSWORTH β 6566 β 832663 β βββββββββββββββββ β
β MAIDENHEAD β BUCKINGHAMSHIRE β 123 β 824299 β βββββββββββββββββ β
β KINGS LANGLEY β DACORUM β 145 β 821331 β βββββββββββββββββ β
β BERKHAMSTED β DACORUM β 543 β 818415 β βββββββββββββββββ β
β GREAT MISSENDEN β BUCKINGHAMSHIRE β 226 β 802807 β ββββββββββββββββ β
β BILLINGSHURST β CHICHESTER β 144 β 797829 β ββββββββββββββββ β
β WOKING β GUILDFORD β 176 β 793494 β ββββββββββββββββ β
β STOCKBRIDGE β TEST VALLEY β 178 β 793269 β ββββββββββββββββ β
β EPSOM β REIGATE AND BANSTEAD β 172 β 791862 β ββββββββββββββββ β
β TONBRIDGE β TUNBRIDGE WELLS β 360 β 787876 β ββββββββββββββββ β
β TEDDINGTON β RICHMOND UPON THAMES β 595 β 786492 β ββββββββββββββββ β
β TWICKENHAM β RICHMOND UPON THAMES β 1155 β 786193 β ββββββββββββββββ β
β LYNDHURST β NEW FOREST β 102 β 785593 β ββββββββββββββββ β
β LONDON β LAMBETH β 5228 β 774574 β ββββββββββββββββ β
β LONDON β BARNET β 3955 β 773259 β ββββββββββββββββ β
β OXFORD β VALE OF WHITE HORSE β 353 β 772088 β ββββββββββββββββ β
β TONBRIDGE β MAIDSTONE β 305 β 770740 β ββββββββββββββββ β
β LUTTERWORTH β HARBOROUGH β 538 β 768634 β ββββββββββββββββ β
β WOODSTOCK β WEST OXFORDSHIRE β 140 β 766037 β ββββββββββββββββ β
β MIDHURST β CHICHESTER β 257 β 764815 β ββββββββββββββββ β
β MARLOW β BUCKINGHAMSHIRE β 327 β 761876 β ββββββββββββββββ β
β LONDON β NEWHAM β 3237 β 761784 β ββββββββββββββββ β
β ALDERLEY EDGE β CHESHIRE EAST β 178 β 757318 β ββββββββββββββββ β
β LUTON β CENTRAL BEDFORDSHIRE β 212 β 754283 β βββββββββββββββ β
β PETWORTH β CHICHESTER β 154 β 754220 β βββββββββββββββ β
β ALRESFORD β WINCHESTER β 219 β 752718 β βββββββββββββββ β
β POTTERS BAR β WELWYN HATFIELD β 174 β 748465 β βββββββββββββββ β
β HASLEMERE β CHICHESTER β 128 β 746907 β βββββββββββββββ β
β TADWORTH β REIGATE AND BANSTEAD β 502 β 743252 β βββββββββββββββ β
β THAMES DITTON β ELMBRIDGE β 244 β 741913 β βββββββββββββββ β
β REIGATE β REIGATE AND BANSTEAD β 581 β 738198 β βββββββββββββββ β
β BOURNE END β BUCKINGHAMSHIRE β 138 β 735190 β βββββββββββββββ β
β SEVENOAKS β SEVENOAKS β 1156 β 730018 β βββββββββββββββ β
β OXTED β TANDRIDGE β 336 β 729123 β βββββββββββββββ β
β INGATESTONE β BRENTWOOD β 166 β 728103 β βββββββββββββββ β
β LONDON β BRENT β 2079 β 720605 β βββββββββββββββ β
β LONDON β HARINGEY β 3216 β 717780 β βββββββββββββββ β
β PURLEY β CROYDON β 575 β 716108 β βββββββββββββββ β
β WELWYN β WELWYN HATFIELD β 222 β 710603 β βββββββββββββββ β
β RICKMANSWORTH β THREE RIVERS β 798 β 704571 β ββββββββββββββ β
β BANSTEAD β REIGATE AND BANSTEAD β 401 β 701293 β ββββββββββββββ β
β CHIGWELL β EPPING FOREST β 261 β 701203 β ββββββββββββββ β
β PINNER β HARROW β 528 β 698885 β ββββββββββββββ β
β HASLEMERE β WAVERLEY β 280 β 696659 β ββββββββββββββ β
β SLOUGH β BUCKINGHAMSHIRE β 396 β 694917 β ββββββββββββββ β
β WALTON-ON-THAMES β ELMBRIDGE β 946 β 692395 β ββββββββββββββ β
β READING β SOUTH OXFORDSHIRE β 318 β 691988 β ββββββββββββββ β
β NORTHWOOD β HILLINGDON β 271 β 690643 β ββββββββββββββ β
β FELTHAM β HOUNSLOW β 763 β 688595 β ββββββββββββββ β
β ASHTEAD β MOLE VALLEY β 303 β 687923 β ββββββββββββββ β
β BARNET β BARNET β 975 β 686980 β ββββββββββββββ β
β WOKING β SURREY HEATH β 283 β 686669 β ββββββββββββββ β
β MALMESBURY β WILTSHIRE β 323 β 683324 β ββββββββββββββ β
β AMERSHAM β BUCKINGHAMSHIRE β 496 β 680962 β ββββββββββββββ β
β CHISLEHURST β BROMLEY β 430 β 680209 β ββββββββββββββ β
β HYTHE β FOLKESTONE AND HYTHE β 490 β 676908 β ββββββββββββββ β
β MAYFIELD β WEALDEN β 101 β 676210 β ββββββββββββββ β
β ASCOT β BRACKNELL FOREST β 168 β 676004 β ββββββββββββββ β
ββββββββββββββββββββββββ΄βββββββββββββββββββββββββ΄βββββββ΄ββββββββββ΄βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
Summaryβ
All 3 queries work much faster and read fewer rows.
Query 1
no projection: 27 rows in set. Elapsed: 0.158 sec. Processed 26.32 million rows, 157.93 MB (166.57 million rows/s., 999.39 MB/s.)
projection: 27 rows in set. Elapsed: 0.007 sec. Processed 105.96 thousand rows, 3.33 MB (14.58 million rows/s., 458.13 MB/s.)
Query 2
no projection: 27 rows in set. Elapsed: 0.163 sec. Processed 26.32 million rows, 80.01 MB (161.75 million rows/s., 491.64 MB/s.)
projection: 27 rows in set. Elapsed: 0.008 sec. Processed 105.96 thousand rows, 3.67 MB (13.29 million rows/s., 459.89 MB/s.)
Query 3
no projection: 100 rows in set. Elapsed: 0.069 sec. Processed 26.32 million rows, 62.47 MB (382.13 million rows/s., 906.93 MB/s.)
projection: 100 rows in set. Elapsed: 0.029 sec. Processed 8.08 thousand rows, 511.08 KB (276.06 thousand rows/s., 17.47 MB/s.)
Test It in Playgroundβ
The dataset is also available in the Online Playground.