[postgis-devel] Re: estimated extent

[strk at refractions.net]

On Fri, Dec 10, 2004 at 01:20:56PM -0000, Mark Cave-Ayland wrote:
> 
> > -----Original Message-----
> > From: strk [mailto:strk at keybit.net] 
> > Sent: 10 December 2004 12:13
> > To: Mark Cave-Ayland
> > Cc: postgis-devel at postgis.refractions.net
> > Subject: estimated extent
> > 
> > 
> > Hello Mark.
> > I've added an 'estimated_extent' function to postgis,
> > which retrives the extent of sample data from the 
> > pg_statistic table. A question for you: how much should the 
> > sample extent grow to closer match *real* extent from a 
> > statistical point of view ? 
> > 
> > --strk;
> 
> 
> Hi strk,
> 
> Do you mean should we use some method to account for the estimated
> difference between the sample extent and the real extent? I think I would be
> inclined to work assuming the sample extent ~= real extent to begin with,
> because since we assume there is no data outside the area then we return a
> correspondingly smaller estimate, and hence we are more likely to use an
> index scan. I'm willing to be proved wrong though, since this is the first
> time we have attempted to code a selectivity function for a join and I have
> no suitable dataset to test this at the moment.
> 
> One more thing: I did some testing earlier in the year and found that the
> current SDFACTOR of 2 was cutting out too many good geometries for me - a
> figure of around 3.25 seemed to be much better, but I didn't get around to
> making the change. This should also have the effect of making the estimated
> extent closer to the real extent, so you might like to make this change at
> the same time during your testing.
> 
> 
> Kind regards,
> 
> Mark.

Thanks for the quick reply Mark.
I've changed SDFACTOR to 3.25 and - yes, histogram extens is closer to
real extent. An example with 8102 multipolygons gives these
extent_area_difference/real_extent_area factors:

 SDFACTOR=2:	-0.35
 SDFACTOR=3.25:	-0.11

BTW the function is not meant for use in the join selectivity estimator,
just for quick extraction of an (estimated) table extent. 

--strk;


> 
> ------------------------
> WebBased Ltd
> South West Technology Centre
> Tamar Science Park
> Plymouth
> PL6 8BT 
> 
> T: +44 (0)1752 791021
> F: +44 (0)1752 791023
> W: http://www.webbased.co.uk
>