Re: join selectivity

On Thu, Dec 16, 2004 at 03:12:21PM -0500, Greg Stark wrote:
>
> "Mark Cave-Ayland" <m(dot)cave-ayland(at)webbased(dot)co(dot)uk> writes:
>
> > Well at the moment PostGIS has a RESTRICT function that takes an expression
> > of the form <column> <op> <constant> where column is a column consisting of
> > geometries and constant is a bounding box. This is based upon histogram
> > statistics and works well.
>
> Are these functions that would be useful for GiST indexes in general?

They provide selectivity for an 'overlap' operator.
GiST is not involved in any way.
Basically it provides statistical gathering for box types columns
and it's analysys in estimating the number of boxes that would
overlap a constant box.

> What's involved in pulling them into a system? I mean, for example, a database
> using RTREE (or GiST I guess) boxes and the @ operator.

It uses BOX2D as a key, maybe if you provide a cast from your
type to BOX2D it could work... I'd like to hear about attempt
at this.

> I didn't realize anyone really had any idea where to start with gathering
> statistics or writing selectivity functions for geometric types. It's great
> news to hear there's actually work in this area.

Statistics in postgis have been available for a long time:

2002-10-12 00:52 dblasby

* postgis_estimate.c: New file with original estimation methods.

--strk;

>
> --
> greg
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)