[postgis-users] Odd explain analyze estimates?

[strk at refractions.net]

On Fri, Mar 04, 2005 at 04:54:17PM -0000, Mark Cave-Ayland wrote:
> 
> > -----Original Message-----
> > From: postgis-users-bounces at postgis.refractions.net 
> > [mailto:postgis-users-bounces at postgis.refractions.net] On 
> > Behalf Of strk at refractions.net
> > Sent: 04 March 2005 15:01
> > To: postgis-users at postgis.refractions.net
> > Subject: Re: [postgis-users] Odd explain analyze estimates?
> > 
> > 
> > On Fri, Mar 04, 2005 at 03:58:02PM +0100, strk at refractions.net wrote:
> > > I think this is still due to JOIN selectivity.
> > > In the first query it cannot attempt a real estimate so a 
> > default is 
> > > returned. In the second query it tries, and if I get it 
> > right it fails 
> > > ;).
> > 
> > Sorry, reading closely to the estimates it seems that Mark's 
> > JOIN selectivity estimator does a good job... it is the 
> > RESTRICT selectivity which is returning a number too low (due 
> > to unavailability of a constant probably). You can enable 
> > debugging info:
> > 
> > #define DEBUG_GEOMETRY_STATS 1
> > in lwgeom/lwgeom_estimate.c around line 28
> 
> 
> Hi strk,
> 
> It's good to hear that the JOIN selectivity estimator is doing something
> useful :) My concern is that we found that the existing algorithm does not
> work particularly well on sub-selects because the RESTRICT function called
> as part of a nested-loop plan for joins currently needs to know the number
> of rows in a relation, and we do not know this for subqueries. 
> 
> I think if this is going to be a problem in the common Mapserver case then
> we should disable it until I get a chance to work on it again. As you
> suggested, undefining REALLY_DO_JOINSEL should stop RESTRICT being called in
> this way and give a more reasonable default behaviour (does this also change
> the CREATE OPERATOR definition so that it uses the default JOIN selectivity
> for &&?)

It makes our JOINSEL return a default value:
#define DEFAULT_GEOMETRY_JOINSEL 0.000005

If I recall correctly the pgsql default was much higher (0.1 or something).

--strk;

> 
> 
> Kind regards,
> 
> Mark.
> 
> ------------------------
> 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
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users