[postgis-users] Odd explain analyze estimates?

[Mark Cave-Ayland]

> -----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 &&?)


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