[postgis-devel] Regression performance.
dzwarg+postgis at azavea.com
Thu Feb 9 16:14:12 PST 2012
I see your point about #1557, but I disagree.
I believe that we should aim for the best performance out of the raster
MapAlgebra routines in any way possible. It may be inconvenient for calling
functions, but if you really need those values, you may always do
"[rast.x]::integer". It is a little messier, but I believe that it's an
acceptable inconvenience: no data is lost converting the raster x or y
coordinate from float8 to int32, and only if you need it will you incur the
penalty of casting to an integer.
Unfortunately, that is exactly the situation you seem to be experiencing,
strk, but I believe that it would be a mistake to penalize all callers of
ST_MapAlgebraExpr for this overhead, versus the rare (we *just* introduced
the x & y parameters) case when you need the x and y parameters inside of
the cell processing expression.
A note on the surprising factor: it took myself and David Bronough at the
code sprint all day to profile and identify what was happening. Both of us
were looking at the patch from r9112, scratching our heads. The removal of
strstr should have made it faster, but it turns out the use of integers in
the expressions were doing something in the parser/planner/evaluator for
every pixel, and this was slowing everything down. It doesn't make sense
on many levels, and I would have thought that integer math would be faster
that floating point math, but in this case it is not the case. Something in
the way postgres evaluates that parameterized query changes that
assumption, which I have no knowledge of (thus the mystery for me).
On Thu, Feb 9, 2012 at 12:47 PM, Sandro Santilli <strk at keybit.net> wrote:
> On Thu, Feb 09, 2012 at 12:20:45PM -0800, David Zwarg wrote:
> > Hello,
> > I was profiling the performance of the changes that happened in r9112,
> > it turned out to be very significant to pass and evaluate integers in the
> > prepared expression. Sticking with float8 values for the x and y
> > of the pixel contain significant performance benefits (although
> > counter-intuitive).
> > I've changed the x and y positions in 1 raster map algebra back to float
> > values for performance reasons.
> > These are my metrics for running 5 sets of 10 rasters, each 500x500
> > from one of the SRTM tiles linked to by the WKTRaster tutorial page.
> > are in milliseconds. The first column is revision 9112 (removal of strstr
> > in the pixel loop, using integer as x and y pixel values), the second
> > column is that same revision (with a modification to use float8 instead
> > int32 for x and y pixel coordinates), the third column is revision 9111
> > (using strstr in the pixel loop), the fourth column is the incorporation
> > this change into the HEAD revision as of this morning (more memory
> > allocations have been moved out of the pixel loop).
> The numbers for those like me not willing to fire a graphical browser:
> r9112 r9112-int r9111 r9137
> 9839.746 8473.403 9094.589 7820.646
> 6228.067 5329.813 5763.88 4738.372
> 8749.516 7441.936 7893.893 6866.745
> 13618.762 11254.392 12177.314 10780.401
> 6233.851 5442.746 5730.231 4767.192
> I'm really surprised. Which version of PostgreSQL were you using ?
> I'm still not convinced we should pretend numbers are float though.
> It was changed back for a reason:
> | __/ | Delivering high quality PostGIS 2.0 !
> | / 2.0 | http://strk.keybit.net
> postgis-devel mailing list
> postgis-devel at postgis.refractions.net
-------------- next part --------------
An HTML attachment was scrubbed...
More information about the postgis-devel