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

[strk at refractions.net]

On Mon, Apr 18, 2005 at 02:53:45PM +0100, Mark Cave-Ayland wrote:
> Hi strk,
> 
> > -----Original Message-----
> > From: strk at refractions.net [mailto:strk at refractions.net] 
> > Sent: 18 April 2005 13:08
> > To: Mark Cave-Ayland (External)
> > Cc: postgis-devel at postgis.refractions.net
> > Subject: Re: [postgis-devel] RE: estimates problems and 1.0.0 delay
> > 
> > 
> > Mark, BTW, here is another warm.
> > Single point in table, invalid join selectivity:
> > 
> > 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 ?
> 
> >From what I remember, this would be incorrect - for the example you may have
> selectivity1 returning 1.0 and selectivity2 returning 0.0 - by multiplying
> the two values then you are effectively returning nothing. Also contributing
> to the issue is that the function must return the selectivity as a fraction
> of the product of the number of rows in table1 and table2 which is why it
> has been written in this way.
> 
> The problem with the code as it stands at the moment is, as you suggest,
> that we need to look at getting rid of total_tuples since this will cause
> issues when working with subqueries. So I would like to change this, but at
> the moment I'm stumped as to exactly what to replace it with :) I will try
> and put my thinking hat on....

Thanks Mark.
I think we can go with final release now as this would really take too
much time to wait on. This are the changes of today:

        - BUGFIX in estimator testers (support for LWGEOM and schema parsing)
        - BUGFIX in join selectivity estimator (defaults, leaks, tuplecount)

I think we should get the final out, as I said, tomorrow evening.
Could you just make a few checks of join selectivity estimation with
current code sice I fixed default returns to use DEFAULT_GEOMETRY_JOINSEL
instead of DEFAULT_GEOMETRY_SEL ?

TIA

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