From: | Andy Halsall <halsall_andy(at)hotmail(dot)com> |
---|---|
To: | <pgsql(at)j-davis(dot)com>, "postgresql (dot)org novice list" <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Function Scan costs |
Date: | 2012-07-06 10:34:06 |
Message-ID: | BLU123-W19F9E406757945583A21F0F5EE0@phx.gbl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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.
Andy
> Subject: Re: [NOVICE] Function Scan costs
> From: pgsql(at)j-davis(dot)com
> To: halsall_andy(at)hotmail(dot)com
> CC: pgsql-novice(at)postgresql(dot)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
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Rob Richardson | 2012-07-06 13:04:57 | How can I re-use an expression in a SELECT? |
Previous Message | Michael Swierczek | 2012-07-05 14:49:12 | Re: IN Operator query |