[postgis-devel] Regression performance.

David Zwarg dzwarg+postgis at azavea.com
Mon Feb 13 07:25:18 PST 2012 

Pierre,

+1

As for the penalty, you are correct: the penalty appears to be caused by
casting the integers to float values in the expression.

-Zwarg

On Fri, Feb 10, 2012 at 9:38 AM, Pierre Racine
<Pierre.Racine at sbf.ulaval.ca>wrote:

> It is true that passing the x and y might be usefull in some cases but it
> is kind of a luxury.
>
> If I can give my two cents I would suggest:
>
> -we come back to the float so we don't penalize most applications. I guess
> the only penalty to the one using the x and y is to cast the float to
> integer in the expression. Right?
>

> -we fill a ticket expecting some input from PostgreSQL people (robe or a
> some inquiry on their list)
>
> -we fix/wontfix the ticket depending on the info we get.
>
> Pierre
>
> > -----Original Message-----
> > From: postgis-devel-bounces at postgis.refractions.net [mailto:
> postgis-devel-
> > bounces at postgis.refractions.net] On Behalf Of dustymugs
> > Sent: Thursday, February 09, 2012 7:32 PM
> > To: postgis-devel at postgis.refractions.net
> > Subject: Re: [postgis-devel] Regression performance.
> >
> > Well, please do come to a consensus.  Once that is achieved, I'll make
> any
> > changes that need to be made to the 2-raster variants.
> >
> > -bborie
> >
> > On 02/09/2012 04:14 PM, David Zwarg wrote:
> > > 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
> > >>
> > >
> > >
> > >
> > > _______________________________________________
> > > postgis-devel mailing list
> > > postgis-devel at postgis.refractions.net
> > > http://postgis.refractions.net/mailman/listinfo/postgis-devel
> > _______________________________________________
> > postgis-devel mailing list
> > postgis-devel at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-devel
> _______________________________________________
> 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/20120213/fecef009/attachment.html>

More information about the postgis-devel mailing list