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

[Ron Mayer]

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;
> 
>