Output parameters, custom data type gotchas   

The answer I said:

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:

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:

• 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

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.