Re: pgstat SRF?

Magnus Hagander wrote:
> Magnus Hagander wrote:
> > Tom Lane wrote:
> > > Magnus Hagander <magnus(at)hagander(dot)net> writes:
> > > > While looking over the statistics-for-functions patch
> > > > (http://archives.postgresql.org/pgsql-patches/2008-03/msg00300.php),
> > > > I came back to a thought I've had before - why do we keep one
> > > > function per column for pgstat functions, instead of using a set
> > > > returning function? Is there some actual reason for this, or is
> > > > it just legacy from a time when it was (much) harder to write
> > > > SRFs?
> > >
> > > I think it's so that you can build your own stats views instead of
> > > being compelled to select the data someone thought was good for
> > > you.
> >
> > You can still do that if it's an SRF. You could even make the SRF
> > take an optional argument to either return a single value (filtered
> > the same way the individual functions are) or when this one is set
> > to NULL, return the whole table.
> >
> > It would make the overhead a lot lower in the most common case
> > ("SELECT
> > * FROM pg_stat_<somethingorother>"), while only adding a little in
> > the other cases, I think.
> >
> > Though I'm not sure that overhead is big enough to care about in the
> > first place, but if you're VIEWs are longish it could be...
>
> Actually, looking at this once more, the interface to the functions
> sucked more than I thought. They're not actually accepting procpid as
> parameters, but just an index into the current array in pgstats..
> Basically, they're not supposed to be used in any other way than
> accessing all the rows at once :-)
>
> Attached is a version of the functions required for pg_stat_activity
> implemented as a SRF instead of different functions. A quick benchmark
> (grabbing the VIEW 10,000 times on a system with about 500 active
> backends) shows it's about 20% faster than the function-per-value
> approach, but the runtime per view is still very quick as it is today.
> (And most of what overhead there is most likely comes from reading the
> stats file)
>
> However, it also implements the lookup-by-PID functionality that IMHO
> makes a lot more sense than lookup-by-backend-array-index. This is
> obviously a lot more performant than querying the VIEW for all rows -
> something that might be a big win for monitoring apps that look for
> info about a single backend.
>
> Unsure if we want to go ahead and convert all functions, but I think
> we can make a good argument for making *new* stats views (like the
> ones about functions that in the queue) based on SRFs instead. It
> also has the nice side-effect of less rows in the system tables ;)
>
> Comments?

Unless there are any objections I'll complete this patch along with the
proper documentation, and apply it for now. I will look at converting
some further pgstats stuff into SRFs as well, assuming they show
similar results..

//Magnus