I see your point about #1557, but I disagree.<br><br>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.<br>
<br>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.<br>
<br>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).<br>
<br>Thanks,<br>Zwarg<br><br><div class="gmail_quote">On Thu, Feb 9, 2012 at 12:47 PM, Sandro Santilli <span dir="ltr"><<a href="mailto:strk@keybit.net">strk@keybit.net</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div class="im">On Thu, Feb 09, 2012 at 12:20:45PM -0800, David Zwarg wrote:<br>
> Hello,<br>
><br>
> I was profiling the performance of the changes that happened in r9112, and<br>
> it turned out to be very significant to pass and evaluate integers in the<br>
> prepared expression. Sticking with float8 values for the x and y positions<br>
> of the pixel contain significant performance benefits (although<br>
> counter-intuitive).<br>
><br>
> I've changed the x and y positions in 1 raster map algebra back to float<br>
> values for performance reasons.<br>
><br>
> These are my metrics for running 5 sets of 10 rasters, each 500x500 pixels,<br>
> from one of the SRTM tiles linked to by the WKTRaster tutorial page. Times<br>
> are in milliseconds. The first column is revision 9112 (removal of strstr<br>
> in the pixel loop, using integer as x and y pixel values), the second<br>
> column is that same revision (with a modification to use float8 instead of<br>
> int32 for x and y pixel coordinates), the third column is revision 9111<br>
> (using strstr in the pixel loop), the fourth column is the incorporation of<br>
> this change into the HEAD revision as of this morning (more memory<br>
> allocations have been moved out of the pixel loop).<br>
<br>
</div>The numbers for those like me not willing to fire a graphical browser:<br>
<br>
r9112 r9112-int r9111 r9137<br>
----------------------------------------------------------<br>
<div class="im"> 9839.746 8473.403 9094.589 7820.646<br>
6228.067 5329.813 5763.88 4738.372<br>
8749.516 7441.936 7893.893 6866.745<br>
13618.762 11254.392 12177.314 10780.401<br>
6233.851 5442.746 5730.231 4767.192<br>
<br>
</div>I'm really surprised. Which version of PostgreSQL were you using ?<br>
I'm still not convinced we should pretend numbers are float though.<br>
It was changed back for a reason:<br>
<a href="http://trac.osgeo.org/postgis/ticket/1557" target="_blank">http://trac.osgeo.org/postgis/ticket/1557</a><br>
<br>
--strk;<br>
<br>
,------o-.<br>
| __/ | Delivering high quality PostGIS 2.0 !<br>
| / 2.0 | <a href="http://strk.keybit.net" target="_blank">http://strk.keybit.net</a><br>
`-o------'<br>
<br>
_______________________________________________<br>
postgis-devel mailing list<br>
<a href="mailto:postgis-devel@postgis.refractions.net">postgis-devel@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-devel" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-devel</a><br>
</blockquote></div><br>