[postgis-devel] RE: estimates problems and 1.0.0 delay

[strk at refractions.net]

Yes, I should have done this before... will be in for 1.0.1.
Committed in both HEAD and 1.0 branches.
I didn't think it was so easy to exploit...
--strk;

On Thu, Apr 21, 2005 at 02:57:50PM -0700, Ron Mayer wrote:
> strk at refractions.net wrote:
> >Mark, BTW, here is another warm.
> >Single point in table, invalid join selectivity:
> 
> I think I've actually encountered this issue in a real (non-contrived test 
> case) query,
> after all the tables were just ANALYZEd immediatelly before running the 
> query.
> 
>   fli=# select * from rtgr.landpoly,
>                       userfeatures.area_features
>                 where rtgr.landpoly.the_geom && 
>                 userfeatures.area_features.the_geom
>                   and 
>                   intersects(rtgr.landpoly.the_geom,userfeatures.area_features.the_geom)
>                   and userfeatures.area_features.feature_id=7;
> 
>   ERROR:  invalid join selectivity: 1.003720
> 
> What's happening here is that the "landpoly" contains all the polygons for
> landmarks in the county; and "area_features" is a user-defined polygon that
> the user creates by drawing on a map with a mouse. There's nothing the
> prohibits a user from drawing a polygon that encloses the entire county
> (effectively saying that he wants to select all the landmarks).
> 
> 
> When I do this, if I understand selectivity right, I guess you'd expect the
> selectivity to be somewhere around one (so the estimaror is making a
> very good guess, only 0.3% off); but in this case rounding pushed it
> slightly above one.   From the error message, I'm guessing it's saying
> that values over 1 are not legal?
> 
> It make me wonder if we'd want something like this.  (I also wonder
> it for the stuff around line 724, but I wasn't hitting that condition.)
> 
> 
> greenie postgis-1.0.0/lwgeom> diff -c lwgeom_estimate.c.~1~ 
> lwgeom_estimate.c
> *** lwgeom_estimate.c.~1~       Mon Apr 18 07:12:59 2005
> --- lwgeom_estimate.c   Thu Apr 21 14:49:36 2005
> ***************
> *** 984,989 ****
> --- 984,991 ----
>                 PG_RETURN_FLOAT8(DEFAULT_GEOMETRY_JOINSEL);
>         }
> 
> +         if ( rows_returned > total_tuples )
> +               PG_RETURN_FLOAT8(1.0);
>         PG_RETURN_FLOAT8(rows_returned / total_tuples);
>   }
> 
> 
> 
> 
> 
> >strk=# explain analyze select * from t1 a, t1 b where a.g && b.g;          
> >NOTICE:  LWGEOM_gist_joinsel called with jointype 0
> >NOTICE:  Working with relations oids: 4150592 4150592
> >NOTICE:   -- geomstats1 box: 1 1, 1 1
> >NOTICE:   -- geomstats2 box: 1 1, 1 1
> >NOTICE:   -- calculated intersection box is : 1 1, 1 1
> >NOTICE:   search_box contains histogram, returning 1
> >NOTICE:   search_box contains histogram, returning 1
> >NOTICE:  selectivity1: 1   selectivity2: 1
> >NOTICE:  Rows from rel1: 1.000000
> >NOTICE:  Rows from rel2: 1.000000
> >NOTICE:  Estimated rows returned: 4.000000
> >ERROR:  invalid join selectivity: 4.000000
> >
> >What about using selectivity1 * selectivity2 ?
> >It would allow use to avoid total_tuples lookup.
> >Would it be correct ?
> >
> >--strk;
> >
> >
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel