Pierre Racine has been diligently working on PostGIS WKT Raster development. He was recently creating an sql function that
uses output parameters.
That was all nice and well, except he couldn't figure out how to output the output parameters as columns.
The function looked something like this:
CREATE FUNCTION somefunction(rast raster, OUT field1 integer, OUT field2 sometype, etc.) AS
$$ blah blah blah $$
The answer I said:
Pierre said: Great that works, but how come its numoutputfields times slower than
SELECT somefunction(rast) As f
How do I get the original faster speed?
To my embarassment, I had never noticed this pattern of slowness before
and I've been using this construct for years. So I suggested:
(SELECT somefunction(rast) As m
FROM somerastertable) As foo
That got him back to much closer speed of the original. So the morals
of this story:
- It pays to be helpful
- You don't know how good it can be until someone with fresh eyes comes along and points out the flaws in your practices.
I have with this bit of information, increased our batch geocoding algorithm speed by 5-fold in some cases
and of course it will help in other avenues such as the common PostGIS practice of
I can rewrite the above as the below and I suspect probably double my speed where I need extra speed. I still
prefer the old syntax since its a bit shorter albeit slower. Where I really need to improve speed I will try this alternative syntax.
SELECT (g).geom, (g).path
FROM (SELECT ST_Dump(geom) As g
FROM sometable) As foo
Please note that this particular issue applies to PostgreSQL 8.4 and I presume it effects below versions as well. I haven't tried this in PostgreSQL 9.0 to see if the difference in syntax
would make a difference in speed. Still need to load up all the Tiger data 2009 we have in our 8.4 install into 9.0 to test our batch geocoding processing algorithm.
Tracked: Apr 08, 02:28