PostGIS expects a geometry type parameter when creating a Geometry column and permitted values include GEOMETRY, POINT, POLYGON, etc.

If I choose GEOMETRY then my Geometry column can store points, lines, polygons, etc, and if I choose POLYGON then the column can only store polygons. One obvious reason to use a specific geometry type such as POLYGON is to enforce a type constraint on insert / update.

Has anyone experienced performance gains from using a specific type over a generic type? For example, if I execute an ST_Intersects function against an equivalent number of GEOMETRY objects and POLYGON objects should I expect either operation to be faster?

To clarify - at this stage I'm only interested in PostGIS performance and I do not want to consider the cost of any additional complexity required in a database client that may need to handle each geometry type differently.

If I'm able to set aside some time I will attempt to test performance myself, but I would also like to hear anyone else's experience.

  • 4
    The C core types are encoded with the explicit geometry type, and most functions in PostGS do have to include a base check (against e.g. GEOS) to decide the most appropriate action for the compared geometry types. The index mechanics should not differ (much) between types themselves. I would expect most, if not all, available functions to perform equally on comparable data, no matter if the geometry typemod is specific or generic. – geozelot Jun 4 at 21:49
  • Accessing the data is also very important. For example, QGIS would scan every row of a geometry (generic) column when accessing it (adding the table, opening a project containing the said table, saving the project etc), while if the specific type+projection is set, the table is loaded directly. It can take several minutes to perform this validation... each time you touch the project/table – JGH Jun 6 at 12:01

PostGIS indexes and functions are all coded to handle arbitrary geometry types. So there is no performance gain by using a more specific geometry typmod parameter.

  • can you point to any documentation or direct experience to back this up? I don't doubt the accuracy of the statement, but decisions based on this could have cascading consequences so it would be good to have some additional confidence on the matter. – tomfumb Jun 7 at 16:07
  • This is base on my knowledge of the source code, and discussions with Paul Ramsey, the lead PostGIS developer and architect. So it's pretty solid. – dr_jts Jun 7 at 16:53

Your Answer

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

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