I am running a PostGIS server (version 2.3). I have a table with the following structure:

- index: BIGINT
- geometry: LINESTRING

I was playing with some of my spatial queries (ST_Intersects in this case) to understand the performance better. I had a query that looked like

SELECT * FROM my_table WHERE ST_Intersects('SRID=4326;POINT(x, y)', my_table.geometry)

As you can tell, table structure and query itself are all simple. Note that geometry column has only LINESTRINGs that follows the same coordinate system (referred with SRID 4326).

As soon as I updated my query with a type cast (SELECT * FROM my_table WHERE ST_Intersects('SRID=4326;POINT(x, y)'::geometry, my_table.geometry)), the performance improved by 10-20x.

Is this happening because PostGIS doesn't need to find out if input geometry is geometry or geography? Not sure, because the second argument of ST_Intersects is guaranteed to be a geometry, because it was created with the following query: SELECT AddGeometryColumn('my_table', 'geometry', 4326, 'LINESTRING', 2);.

Does anybody have any insight on what would be reason for the performance boost with the type cast?

  • It probably just gives the planner enough info to go the index route – Nicklas Avén Jul 5 '17 at 21:41
  • 1
    A postgis performance question is never complete without the EXPLAIN ANALYZE outputs. – pLumo Jul 6 '17 at 7:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.