[pgrouting-users] [OT] stored procedure performance

Hi All,

This is a little off topic, but I did find this issue developing a postgis related stored procedure.

This is a simple reverse geocoder and it works fine, but here is the issue and this potentially impacts all procedures that return a record.

So here is the basic query. I added some raise notice to show whats happening.

select * from imt_rgeo_countyzip(-79.2723,41.2393);
"Clarion";"PA";"42031";"16225";0;0

NOTICE: calling imt_rgeo_countyzip
CONTEXT: PL/pgSQL function "imt_rgeo_countyzip" line 8 at assignment
NOTICE: rr: (Clarion,PA,42031,0), radius: 0.0013
CONTEXT: PL/pgSQL function "imt_rgeo_countyzip" line 8 at assignment
NOTICE: zip: (16225,0), radius: 0.0013
CONTEXT: PL/pgSQL function "imt_rgeo_countyzip" line 8 at assignment
Total query runtime: 81 ms.
1 row retrieved.

So here is the problem! If you call the function this way, it appears to get called once for each field in the returned record. So if you have 6 fields it actually executes the function 6 times!!!! and the cost goes up by 6X to 415 ms from 81 ms.

select (imt_rgeo_countyzip(-79.2723,41.2393)).*;
"Clarion";"PA";"42031";"16225";0;0

NOTICE: calling imt_rgeo_countyzip
CONTEXT: PL/pgSQL function "imt_rgeo_countyzip" line 8 at assignment
NOTICE: rr: (Clarion,PA,42031,0), radius: 0.0013
CONTEXT: PL/pgSQL function "imt_rgeo_countyzip" line 8 at assignment
NOTICE: zip: (16225,0), radius: 0.0013
CONTEXT: PL/pgSQL function "imt_rgeo_countyzip" line 8 at assignment
NOTICE: calling imt_rgeo_countyzip
CONTEXT: PL/pgSQL function "imt_rgeo_countyzip" line 8 at assignment
NOTICE: rr: (Clarion,PA,42031,0), radius: 0.0013
CONTEXT: PL/pgSQL function "imt_rgeo_countyzip" line 8 at assignment
NOTICE: zip: (16225,0), radius: 0.0013
CONTEXT: PL/pgSQL function "imt_rgeo_countyzip" line 8 at assignment
NOTICE: calling imt_rgeo_countyzip
CONTEXT: PL/pgSQL function "imt_rgeo_countyzip" line 8 at assignment
NOTICE: rr: (Clarion,PA,42031,0), radius: 0.0013
CONTEXT: PL/pgSQL function "imt_rgeo_countyzip" line 8 at assignment
NOTICE: zip: (16225,0), radius: 0.0013
CONTEXT: PL/pgSQL function "imt_rgeo_countyzip" line 8 at assignment
NOTICE: calling imt_rgeo_countyzip
CONTEXT: PL/pgSQL function "imt_rgeo_countyzip" line 8 at assignment
NOTICE: rr: (Clarion,PA,42031,0), radius: 0.0013
CONTEXT: PL/pgSQL function "imt_rgeo_countyzip" line 8 at assignment
NOTICE: zip: (16225,0), radius: 0.0013
CONTEXT: PL/pgSQL function "imt_rgeo_countyzip" line 8 at assignment
NOTICE: calling imt_rgeo_countyzip
CONTEXT: PL/pgSQL function "imt_rgeo_countyzip" line 8 at assignment
NOTICE: rr: (Clarion,PA,42031,0), radius: 0.0013
CONTEXT: PL/pgSQL function "imt_rgeo_countyzip" line 8 at assignment
NOTICE: zip: (16225,0), radius: 0.0013
CONTEXT: PL/pgSQL function "imt_rgeo_countyzip" line 8 at assignment
NOTICE: calling imt_rgeo_countyzip
CONTEXT: PL/pgSQL function "imt_rgeo_countyzip" line 8 at assignment
NOTICE: rr: (Clarion,PA,42031,0), radius: 0.0013
CONTEXT: PL/pgSQL function "imt_rgeo_countyzip" line 8 at assignment
NOTICE: zip: (16225,0), radius: 0.0013
CONTEXT: PL/pgSQL function "imt_rgeo_countyzip" line 8 at assignment

Total query runtime: 415 ms.
1 row retrieved.

I guess this could be that I do not understand what the (..).* syntax is supposed to be doing.

Thoughts,
   -Steve