Opened 5 years ago

Closed 5 years ago

Last modified 5 years ago

#2822 closed defect (fixed)

Slow _add_raster_constraint_extent

Reported by: strk Owned by: Bborie Park
Priority: medium Milestone: PostGIS 2.2.0
Component: raster Version: master
Keywords: history Cc:

Description

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.

Change History (10)

comment:1 Changed 5 years ago by 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.

comment:2 Changed 5 years ago by strk

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

comment:3 Changed 5 years ago by 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.

comment:4 Changed 5 years ago by strk

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

comment:5 Changed 5 years ago by strk

Using the operator would be even faster _after_ creating the index

comment:6 Changed 5 years ago by Bborie Park

Milestone: PostGIS 2.2.0
Status: newassigned
Version: 2.1.xtrunk

OK. I'll make the changes.

comment:7 Changed 5 years ago by Bborie Park

Keywords: history added
Resolution: fixed
Status: assignedclosed

Added to trunk in r12752.

comment:8 Changed 5 years ago by 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?)

comment:9 Changed 5 years ago by strk

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

comment:10 Changed 5 years ago by strk

I've ticketed the ST_Envelope speedup as #2828

Note: See TracTickets for help on using tickets.