[SEDONA-206] Performance regression of ST_Transform in 1.3.0-incubating

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.3.0
• Fix Version/s: 1.3.1
• Labels: pull-request-available
• Assignee: Unassigned
• Reporter: Kristin Cowalcijk
• Created: 02/Dec/22 11:09
• Updated: 12/Dec/22 06:01
• Resolved: 12/Dec/22 06:01

Description

The performance of ST_Transform drastically degraded since 1.3.0. This is the flamegraph of an executor node when running queries like this:

SELECT COUNT(1) FROM ms_buildings WHERE ST_Transform(ST_FlipCoordinates(geom), 'epsg:4326', 'epsg:3857') is not NULL

The performance regression is caused by the introduction of custom CRS support. As we can see, most of the time was spent by exception handlers in geotools-referencing module. It is a very common use case to specify a well known CRS when calling ST_Transform, and we don't want to suffer from such a performance hit when doing that. One approach to fix this problem is to detect if the CRS string "looks like" a well known CRS by pattern matching before calling the expensive CRS.parseWKT.

Issue Links

• links to: GitHub Pull Request #727