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?