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@svana.org> http://svana.org/kleptog/