Performance(?) issues with ST_Reskew() (on rasters)

[Frank Gevaerts]

The raster data is sand and gravel layer thickness, which is used to do strength calculations for large rectangular concrete structures resting on top of those layers. These structures are not all oriented exactly the same, and none of them is the same orientation as the raster data (which is north-south)

For the actual calculations we just need mean values inside polygons, so nothing too special is needed, but the customer wants to export the raster data neatly aligned to the concrete structures for semi-manual sanity checking of the calculations.

ST_ReSkew seemed to me to be the easiest (and fastest) way to get the data in that form, so it was the first approach to try.

Frank

On Mon, Feb 24, 2025 at 01:43:00PM -0800, Paul Ramsey wrote:
> Out of curiousity, what are you using ST_ReSkew for? 
> 
> > On Feb 24, 2025, at 11:24 AM, Paul Ramsey <pramsey at cleverelephant.ca> wrote:
> > 
> > https://trac.osgeo.org/postgis/ticket/5854
> > 
> >> On Feb 24, 2025, at 10:22 AM, Paul Ramsey <pramsey at cleverelephant.ca> wrote:
> >> 
> >> Replicated. I had to go up to 430 to make mine flip behaviour, but it is similar.
> >> 
> >> P.
> >> 
> >>> On Feb 24, 2025, at 9:46 AM, Frank Gevaerts <frank.gevaerts at fks.be> wrote:
> >>> 
> >>> Ok, I managed to simplify this to basically the example in the documentation:
> >>> 
> >>> This works fine, in a reasonable (typically 100ms to 150ms) time:
> >>> SELECT ST_Rotation(ST_Reskew(ST_AddBand(ST_MakeEmptyRaster(100, 359, 0, 0, 0.001, -0.001, 0, 0, 4269), '8BUI'::text, 1, 0), 0.0015));
> >>> 
> >>> This does not work fine, I stopped it after more than five minutes:
> >>> SELECT ST_Rotation(ST_Reskew(ST_AddBand(ST_MakeEmptyRaster(100, 360, 0, 0, 0.001, -0.001, 0, 0, 4269), '8BUI'::text, 1, 0), 0.0015));
> >>> 
> >>> The only difference is the size of the raster, the broken one having just one more row. The postgres process was using 100% CPU during that time, I don't think it was doing any IO (so no tempfiles as far as I could see).
> >>> 
> >>> I reproduced it on two systems, and things break for the same numbers, both on debian with postgresql 16, one with postgis 3.5.2/gdal 3.10.2, and one with postgis 3.4.2/gdal 3.2.2
> >>> 
> >>> Frank
> >>> 
> >>> On Mon, Feb 24, 2025 at 07:49:07AM -0800, Paul Ramsey wrote:
> >>>> If you provide a dump of a “good” raster, and a “bad” raster, and the exact SQL you are running, the odds of someone duplicating your issue and then fixing your issue go up 10-fold. I like fixing problems, not trying to replicate problems. 
> >>>> ATB,
> >>>> P
> >>>> 
> >>>>> On Feb 24, 2025, at 7:38 AM, Frank Gevaerts <frank.gevaerts at fks.be> wrote:
> >>>>> 
> >>>>> Hello,
> >>>>> 
> >>>>> I'm having issues with ST_Reskew(). The source raster (32BSI) contains 150000 points, and ST_Reskew() takes forever. If I rescale the raster to anywhere under ~80000 points, it suddenly only takes around 300ms. This is not linear, ~85000 points is bad, and then suddenly 80000 points is good.
> >>>>> 
> >>>>> I'm not actually sure if it's "just" a performance problem or an infinite loop, I have not had the patience yet to have it run for more than 20 minutes.
> >>>>> 
> >>>>> This is using postgis 3.5.2 on debian installed from apt.postgresql.org, and libgdal36 3.10.2
> >>>>> 
> >>>>> Is there anything I'm overlooking, or is there a memory usage parameter I can set to help with this?
> >>>>> 
> >>>>> Frank
> >>>> 
> >>> 
> >>> -- 
> >>> Frank Gevaerts                                 frank.gevaerts at fks.be
> >>> fks bvba - Formal and Knowledge Systems        http://www.fks.be/
> >>> Grote Baan 79                                  Tel:  ++32-(0)11-21 49 11
> >>> B-3511 KURINGEN-HASSELT                        Fax:  ++32-(0)11-22 04 19
> >> 
> > 
> 

-- 
Frank Gevaerts                                 frank.gevaerts at fks.be
fks bvba - Formal and Knowledge Systems        http://www.fks.be/
Grote Baan 79                                  Tel:  ++32-(0)11-21 49 11
B-3511 KURINGEN-HASSELT                        Fax:  ++32-(0)11-22 04 19