Thanks Jeff. From what I can see I'm not sure foreign tables will help us. The query is pretty efficient - two index scans and a single, minimal result to materialize. I would've expected the time to find the result to be high in comparison to the overall time of the call and so I was interested in where the overheads were.
Looking at this a different way, I've written an immutable function that returns a fixed value. If I call this from libpq I can establish the cost in time of the overhead of the call from client and back. If I compare this with the time it takes to execute my real stored procedure (from original post), the overhead is ~40%. Seems high.
> Subject: Re: [NOVICE] Function Scan costs
> From: pgsql@j-davis.com
> To: halsall_andy@hotmail.com
> CC: pgsql-novice@postgresql.org
> Date: Wed, 27 Jun 2012 16:33:51 -0700
>
> On Wed, 2012-06-27 at 15:56 +0000, Andy Halsall wrote:
> > I'm calling functions via libpq. I've noticed that in the EXPLAIN
> > analysis the time for the Function Scan is high relative to that of
> > the actual query. In the example below (if I'm reading it correctly),
> > the query seems to take just 0.022 ms whereas the time allocated to
> > "Function Scan is 0.483ms. Is this to do with parsing original query
> > and substituting params? Could somebody please explain? Thanks.
>
> The times involved are quite small so it's hard to speculate on exactly
> what's causing the difference (if there is a real difference).
>
> If I had to guess, I would say that it's because a set-returning
> function (SRF) always materializes the entire result. That's a
> limitation of SRFs, and you might consider a foreign table instead if it
> suits your needs.
>
> Regards,
> Jeff Davis
>
>