[postgis-users] What does this error mean, and how can I avoid it?

[Paul Ramsey]

Steve,

It's a notice, not an error, so you don't need to avoid it, merely  
understand what it means.

Mark C-A's amazing selectivity magic improvement allowed the planner  
to properly evaluate the selectivity of spatial joins between  
tables.  Things like "a.the_geom && b.the_geom".  Note that both  
sides of the operator are multiply valued, unlike, for example  
"a.the_geom && MakePoint(1,2)".  The older selectivity system only  
did good estimates for the latter case.

Mark's magic can only work when there are statistics available for  
both sides of the operator, and statistics are only gathered for  
things that have indexes defined for them. You might have made an  
index on a.the_geom, and statistics will be gathered for that.  
Statistics will not be gathered for transform(a.the_geom) or expand 
(a.the_geom).

Which brings us to the NOTICE.  It is warning you that it is  
computing join selectivity, but using things for which it doesn't  
have proper statistics available.  So it might get a bad selectivity  
estimate.  For simple queries and plans, there won't be a noticeable  
difference, because there really is only one efficient plan.  For  
more complex ones, it's possible you'll get a non-optimal plan.

Paul

On 3-Jan-08, at 7:53 AM, Stephen Woodbridge wrote:

> Hi all,
>
> I ran into this last night while playing with some queries:
>
> NOTICE:  LWGEOM_gist_joinsel called with arguments that are not  
> column references
> CONTEXT:  SQL statement "select a.file, a.name, b.name
>   from canada_p.pl a, canada_p.pl b
>  where expand(a.the_geom, 2/69) && b.the_geom
>    and (strpos(a.name, b.name)>0 or strpos(b.name, a.name)>0)"
>
> This one is new to me. I am trying to find all records with similar  
> names within about 2 miles of one another in this table.
>
> Thanks,
>   -Steve
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users