[postgis-devel] To PL/pgSQL or not to PL/pgSQL

Tue Aug 31 07:01:39 PDT 2010

Thanks for your answer Regina. I repost to the group...

I planned those functions to get and set block of values from/into the raster:

-ST_GetValues(rast, band, x, y, width, height) -> values[]
-ST_SetValues(rast, band, x, y, width, height, values[])
-ST_SetValues(rast1, band1, x1, y1, fromrast, band, x2, y2, width, height)
-ToRaster(raster, band, geometry, val)

The two first one works with value arrays and the third one copy values from a raster band. They are differenciated from their pixel by pixel version by the "s" at the end. Those three are all planned as C functions. The last one burn the shadow of a geometry into a raster band with the provided value. I don't know yet if a plpgsql version of this last one would be quick enough.

However some functions (ST_MapAlgebra for example) need to get and set pixels values one at a time. My questionning is whether we could keep a function like mapalgebra in plpgsql, getting all or part of the values to get and set in one or many of those get and set C calls, or whether we really have to implement it in C.

There are some advantages to have functions in plpgsql when we can: they are easily modifiable by users.

Pierre

From: Obe, Regina [mailto:robe.dnd at cityofboston.gov]
Sent: 31 août 2010 08:27
To: Pierre Racine; George Silva
Cc: Jorge Arévalo
Subject: RE: [postgis-devel] To PL/pgSQL or not to PL/pgSQL

Hi Guys,

Sorry I've been very quiet.  I haven't had a chance to take a close look at this since I've been tied up with other things.

I think you asked once if plpgsql is slower than implementing this stuff in C.

The short answer is it usually is but it doesn't necessarily need to be and in some cases can be faster since its closer to the PostgreSQL side.  Take for example ST_MinimimumBoundingCircle.  Its implemented in plpgsql but I would suspect its not much slower than if implemented in C.

The real problem -- is you really got to come up with a better way of getting pixels in and out of the C layer than ST_SetValue, ST_Value or tackle the more fundamental issue of why so much memory/time is losting switching between the barriers.

That is the part that is really really slow.  If you compare say ST_Dump   vs. doing the same with generate_series (ST_GeometryN/ST_PointN), that is what makes all the difference.  The more
things you need to pull out, it doesn't get worse linearly, it gets worse quadratically. ST_GeometryN/ST_PointN is quadratically worse in speed than doing the same with ST_Dump (except in the case
where you only need to work with a few points of a large set of points/geometries - then you gain with ST_PointN/ST_GeometryN

What I have found kills you with implementing things in plpgsql programming (atleast against PostGIS and I assume any non-native C that is not part of the core PostgreSQL memory space) is what I call the reentry effect - converting back and forth between the native PostgreSQL environment (plpgsql/ native PostgreSQL C functions).   Which is why you
want to get as much data as you plan to work with in say PostgreSQL arrays (even if you overshoot it a bit), and feed it back in a single function or alternatively figure out a way to reduce that reentry effect.

This is really why I've been pushing the change to ST_SetValue , ST_Value to allow setting and pulling blocks of pixels.  Without that fundamental change, I feel that any function you implement in plpgsql will
be about 1000 times slower than it needs to be.  The alternative of researching that would be nice and getting rid of that barrier.  I fear that is harder to fix though.  Though maybe not.  After all in the end -- PostgreSQL is all C.

________________________________
From: Pierre Racine [mailto:Pierre.Racine at sbf.ulaval.ca]
Sent: Friday, August 27, 2010 2:32 PM
To: George Silva
Cc: Obe, Regina; Jorge Arévalo
Subject: RE: [postgis-devel] To PL/pgSQL or not to PL/pgSQL
George,

Here is a first version of MapAlgebra. There is a one raster version and a two rasters version. Some enhancement are still  to do:

-Everything is implemented in plpgsql and might be (very) slow.
-They are not yet able to look for neighbour pixels. Ex. rast1[-1,1] or rast2[-3,2]
-They do not yet work with neighbour tiles pixels.
-The two raster version do not handle rotation very well.
-The two raster version do not resample when needed.

Let me know if you like it. I'm doing amazing things with the two rasters version:

-A very sophisticated ST_Union(raster) into a unique raster
-ST_Reclass
-And more to come (ST_Clip, ST_Intersection(geometry, raster) -> raster, ST_SelectByValue).

I think this  function will be, with st_intersection, the very core of WKT Raster analysis capacity.

I wrote them to assert my specifications for a number of function that I am about to put in the wiki.

Pierre

From: postgis-devel-bounces at postgis.refractions.net [mailto:postgis-devel-bounces at postgis.refractions.net] On Behalf Of George Silva
Sent: 19 août 2010 17:41
To: PostGIS Development Discussion
Subject: Re: [postgis-devel] To PL/pgSQL or not to PL/pgSQL

Hello Pierre,

I'm up for a plpgsql implementation for now. I've written a few functions for wkt raster, but as you noticed, I stumbled upon some problems for the more complex ones.

I've implemented also: plus, minus, and other math operations for rasters. They all work fine.

When I get home I'll email it to the list and you can use them as you seem fit.

Cheers,

George
On Thu, Aug 19, 2010 at 5:10 PM, Pierre Racine <Pierre.Racine at sbf.ulaval.ca<mailto:Pierre.Racine at sbf.ulaval.ca>> wrote:
Hi,

Are functions written in C REALLY faster than functions written in PL/pgSQL? Why?

Is there any rule saying what king of function should be better implemented in C and what kind should be better (of OK) in PL/pgSQL?

Do you guys prefer to publish a slower PL/pgSQL function NOW (warning that it might be implemented in C later with the same signature) or wait months that the C equivalent is implemented?

I understand that most of the PostGIS functionality is in GEOS but we don't have a GEOS equivalent for WKT Raster (we have GDAL but it's very limited in terms of analysis functionality) and I feel that everything would go much faster implementing many things in PL/pgSQL. For example right now I'm implementing a nice ST_MapAlgebra function in PL/pgSQL. I have no idea if it would be really much faster in C. I love PL/pgSQL...

Thanks for your advices,

Pierre

_______________________________________________
postgis-devel mailing list
postgis-devel at postgis.refractions.net<mailto:postgis-devel at postgis.refractions.net>
http://postgis.refractions.net/mailman/listinfo/postgis-devel

--
George R. C. Silva

Desenvolvimento em GIS
http://blog.geoprocessamento.net
________________________________

The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20100831/97d8cf31/attachment.html>