Slow _add_raster_constraint_extent

[strk]

Calling _add_raster_constraint_extent takes ~24 seconds against a table containing 2048 rasters of 256x256 pixels and 3 bands each.

For comparison, calling st_envelope(st_collect(st_envelope(rast))) takes 1.5 seconds. There might be something unneeded being run by the function.

[Bborie Park]

This is related to #2777. In #2777, I changed -trunk to use envelope(collect(envelope(rast))) but did not backport it to 2.1 or 2.0 due to change in behavior.

In 2.1 or 2.0, it is using the far more expensive union(convexhull(rast)) approach.

[strk]

I'm actually seeing this slowdown with trunk. Still hadn't dug deeper.

[Bborie Park]

There are two parts to that constraint. The envelope(collect(envelope(rast))) creates the reference for the constraint. When the constraint is applied to the column, the column needs to be checked against the constraint. The check is coveredby(envelope(rast), geom). I suppose that coveredby is slowing things down. Could just be envelope(rast) && geom.

[strk]

+1 for using an operator, prbably @ would be ok: ​http://postgis.net/docs/ST_Geometry_Contained.html

[strk]

Using the operator would be even faster _after_ creating the index

[Bborie Park]

OK. I'll make the changes.

[Bborie Park]

Added to trunk in r12752.

[strk]

Quick test on a table with 19208 rows with 256x256 tiles brings down the _add_raster_constraint_extent time from ~23 seconds to ~15 seconds. select st_envelope(rast) takes ~7.5 seconds, so the math is one go to compute max extent and another go to check the constraint, which makes indeed those ~15 seconds.

Now I wonder if st_envelope could be made faster (does it detoast a single slice of the toast already?)

[strk]

I just noticed that ST_Envelope(rast) is an sql-implemented function calling ST_Envelope(ST_ConvexHull(rast)). No wonder it is slow …

[strk]

I've ticketed the ST_Envelope speedup as #2828