Table 3 Benchmarking queries

Q1

Get count of nodes, ways, roads, and rels.

4

4

SELECT ‘nodes’ AS tbl, COUNT(*) AS cnt FROM public.planet_osm_point UNION SELECT ‘roads’, COUNT(*) FROM public.planet_osm_line UNION SELECT ‘ways’, COUNT(*) FROM public.planet_osm_polygon UNION SELECT ‘rels’, COUNT(*) FROM public.planet_osm_polygon;

Q2

Get count of points, lines, and polygons

3

3

SELECT ‘point’ AS tbl, COUNT(*) AS cnt FROM public.planet_osm_point UNION SELECT ‘line’, COUNT(*) FROM public.planet_osm_line UNION SELECT ‘polygon’, COUNT(*) FROM public.planet_osm_polygon ;

Q3

Top amenities

168

210

SELECT amenity, COUNT(amenity) as num FROM planet_osm_point GROUP BY amenity ORDER by num DESC;

Q4

Get names of all restaurants and number of branches

2501

3794

SELECT name, count(name) FROM planet_osm_point WHERE amenity = ‘restaurant’ GROUP BY name ORDER BY count DESC;

Q5

Get all info about all the restaurants

3005

4321

SELECT * FROM planet_osm_point WHERE amenity = ‘restaurant’;

Q6

Get restaurants with 2 or more branches

178

84

SELECT name, count(name) as number FROM planet_osm_point WHERE amenity = ‘restaurant’ GROUP BY name HAVING count(name) >= 3 ORDER BY name ASC;

Q7

Get length of all roads (in km)

386224

494877

SELECT highway, name, way, st_length(way)/1000 AS length FROM planet_osm_line WHERE highway NOT IN (‘construction’, ‘footway’, ‘path’, ‘steps’, ‘track’, ‘cycleway’, ‘pedestrian’, ‘abandoned’, ‘disused’) AND (service NOT IN (‘parking_aisle’, ‘driveway’) OR service is null) AND (access NOT IN (‘no’, ‘private’) or access is null) ORDER BY name;

Q8

Get generated objects and cardinalities

8

8

SELECT *, pg_size_pretty(total_bytes) AS total , pg_size_pretty(index_bytes) AS INDEX , pg_size_pretty(table_bytes) AS TABLE FROM ( SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM ( SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME , c.reltuples AS row_estimate , pg_total_relation_size(c.oid) AS total_bytes , pg_indexes_size(c.oid) AS index_bytes , pg_total_relation_size(reltoastrelid) AS toast_bytes FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE relkind = ‘r’ ) a ) a where a.table_schema = ‘public’;