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 $$
LANGUAGE 'sql';
The answer I said:
SELECT (somefunction(rast)).*
FROM somerastertable;
Pierre said: Great that works, but how come its numoutputfields times slower than
.SELECT somefunction(rast) As f
FROM somerastertable;
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:
How about:SELECT (m).*
FROM
(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:
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
SELECT (ST_Dump(geom)).* FROM sometable
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[1]
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.