8

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.

Improve this question

2 Answers 2

Reset to default
8

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
Improve this answer
3
  • That's a pretty amazing speed difference. My original query with geometry intersection was about 1.8 secs. Your _ST_DistanceTree suggestion took 1.4 secs. Yes, I have very complex polygons with lots of vertices.
    – amball
    Apr 21, 2017 at 0:12
  • That sounds great. But I'd like to know how did you find out about this function ? Is there any Documentation out there ? Even a direct search for _ST_DistanceTree or DistanceTree returns no results.
    – pLumo
    Apr 21, 2017 at 5:56
  • It's a "private" internal function, hence the leading underscore and lack of documentation. You can see it in the geography.sql file here. There is discussion of making this behavior the default in a future version of PostGIS.
    – dbaston
    Apr 21, 2017 at 13:36
2

The geography functions are slower because they do a lot more. Also not as much time has been spent on beefing up the performance of geography functions since most people still use geometry.

It varies how bad the difference is. AS I recall point to point distance checks and ST_Dwithin point to point are just slightly slower.

polygons are really bad and how bad they are is a function of how many points you've got in your polygon and the coverage of it. It's generally at least 10 times worse than geometry.

ST_Intersects in geometry for example uses a lot of short-cuts which rely on intersection matrix that geography can't. So a better comparison would be ST_DWithin geometry / ST_Intersects geography since geography ST_Intersects is really a wrapper around the geography ST_DWithin plumbing which relies on the distance plumbing.

Even then you'll find geometry is much faster. I think some of those faster pieces in geometry distance plumbing are something that can be added to geography. There just hasn't been enough demand / funding for that kind of work to make it happen.

One more thing. Can you do me a favor and compare the performance of

&& (using geography and geometry)

from what I recall, the bounding box performance is closer in performance but really looses it if it has to hit the core functions.

Improve this answer
1
  • Regarding the bounding box, the performance of && is similar, although geom is still faster (411 for geom vs 571 ms for geog).
    – amball
    Apr 20, 2017 at 19:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.