[GIS] PostGIS geography intersect slower than geometry intersect

postgis

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.

Best Answer

As @LR1234567 pointed out, the geography version of ST_Intersects is really just a wrapper around a distance calculation. If your geographies contain a lot of vertices, you may get better performance by forcing PostGIS to use a tree-based distance calculation.

This algorithm is much faster than the brute-force distance calculation, but PostGIS makes the wager that it's only worth using it when the tree can be reused multiple times. You can override this logic and force the use of the tree-based calculation like this:

SELECT DISTINCT g1.iso, g2.iso FROM gadm g1, gadm g2
WHERE g1.geog && g2.geog 
  AND g1.iso!=g2.iso
  AND _ST_DistanceTree(g1.geog, g2.geog) < 0.00001
Related Question