[postgis-devel] Regression performance.

[David Zwarg]

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).

Thanks,
Zwarg

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,
> and
> > 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
> positions
> > 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
> pixels,
> > from one of the SRTM tiles linked to by the WKTRaster tutorial page.
> Times
> > 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
> of
> > 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
> of
> > 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:
> http://trac.osgeo.org/postgis/ticket/1557
>
> --strk;
>
>  ,------o-.
>  |   __/  |    Delivering high quality PostGIS 2.0 !
>  |  / 2.0 |    http://strk.keybit.net
>  `-o------'
>
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20120209/e30bc38f/attachment.html>