I have a table of large polygons (countries at high resolution), with a geometry (4326) and geography column. Both geography and geometry are indexed with a gist spatial index.
A geography-based query takes more than 10 minutes:
SELECT DISTINCT g1.iso, g2.iso FROM gadm g1, gadm g2 WHERE ST_Intersects(g1.geog, g2.geog) AND g1.iso!=g2.iso
The equivalent geometry-based query take 1.8 secs:
SELECT DISTINCT g1.iso, g2.iso FROM gadm g1, gadm g2 WHERE ST_Intersects(g1.geom, g2.geom) AND g1.iso!=g2.iso
What is going on? The query plans from EXPLAIN look identical, except one uses the geom index and one the geog index.
I am running Postgres 9.5.6 and PostGIS 2.2.1.