I've read from many that PL/V8 mathematic operations are generally faster than what you get with SQL functions and PL/pgsql functions. One area where I thought
this speed would be really useful was for writing Map Algebra call-back functions. A PostGIS 2.1+ map algebra callback function signature looks like:
func_name(double precision value, integer pos, text VARIADIC userargs)
So for starters, I set out to rewrite my favorite call-back function ST_Range4ma
as a PL/V8. I left out all the logic for handling pos and userargs, because frankly I never use those. In doing so, I discovered a couple of things. The handling of pos and userargs really
weighs down the built-in implementation. PL/V8 is indeed faster even if I were to write out all the for loop logic that the built-in PostGIS plpgsql version has. For demonstration here are 2 implementations of range function.
There is however one big gotcha with the PL/V8 handling of arrays. It seems if you give it an n-dimensional array (a matrix) as is the case with MapAlgebra callbacks that get passed a neighborhood of pixel values, it collapses it down to a 1-dimensional array though all the original elements still appear to be in the collapsed array. This bummed me a bit when I ran into it, but then I realized for many use-cases such as Max, Min, Range the collapsing effect simplifies the work and has no effect on the result.
For cases where you do need to keep track of the dimensions, I suspect you can create an SQL wrapper that includes the dimensions of the pixel value array and then in the plv8 logic need to break the array up based on the dimension. That will be my next venture.
I was thinking about that myself but haven't had a chance to test PL/Python in that regard. My gut feeling says it will be slower just because I think the V8 engine is less of a load than the Python environment.
Besides PL/V8 you can create window functions which you can't in PL/Python :)
One thought I have had is to rewrite the provided callback functions in C. Having said that, it's awesome that the are easy performance improvements... 52 to 18 seconds just by using a different PL language.
I was going to investigate the ST_Range4MA function. I had started creating a simplified since I didn't need the userargs piece. While that was not quite as fast as the SQL one. It was within 1-2 second. So I'm thinking we maybe don't want to do that user args check in each loop maybe check if it needs to be done and take a simpler route if no userargs is passed in.
So a lot of the gain was my simplication. I suspect copying the array in the beginning adds a lot of overhead.