Re: Statistics on views (execute a plan from within analyze)

On Fri, Mar 30, 2007 at 12:01:33PM -0400, Oscar Täckström wrote:
> I am working on a course project on implementing collection of statistics
> on views in pgsql. The statistics will be used in conjunction with view
> matching in the optimizer, to improve selectivity estimates.

Interesting idea...

> For this to be possible, I need to be able to execute view definitions
> from within the analyze code (so I can then sample the result set and
> collect the statistics on the sampled tuples). Is there a preferred way
> to do this? A simple solution is to use the SPI, but is this really
> recommended for use from within this code? I don't want to materialize
> the result set, but calculate the statistics directly in memory and just
> throw away the tuples that's not part of the sample.

I beleive you can use SPI to only retreive tuples as they are
calculated, i.e. it doesn't materialise the dataset. However, if this
is a concern, you could always use SPI to create a cursor to iterate
over your set, That will give complete control...

Hope this helps,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.