3

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.

Improve this question
2
  • 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, 2021 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, 2021 at 12:01

1 Answer 1

Reset to default
5

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.

Improve this answer
2
  • 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, 2021 at 16:07
  • 1
    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, 2021 at 16:53

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.