based on http://blog.cleverelephant.ca/2018/09/postgis-external-storage.html

results.csv

results_noindex.csv

test.sql

update pg_type set typstorage='e' where typname='geometry';
-- also test with m and x

\timing

create table adm2 as select * from planet_osm_polygon where boundary='administrative' and admin_level='2' ORDER BY ST_GeoHash(ST_Transform(ST_Envelope(way),4326),10);

create table cities as select * from planet_osm_point where place='city' ORDER BY ST_GeoHash(ST_Transform(ST_Envelope(way),4326),10);

CREATE INDEX ON adm2 USING gist (way) WITH (fillfactor=100);
CREATE INDEX ON cities USING gist (way) WITH (fillfactor=100);
VACUUM ANALYZE adm2;
VACUUM ANALYZE cities;

-- repeat 4 times, throw away first, take median time
EXPLAIN ANALYZE SELECT COUNT(*), c.name FROM adm2 c JOIN cities p ON ST_Intersects(c.way, p.way) GROUP BY c.name;

DROP TABLE adm2; DROP TABLE cities;