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

[Mark Cave-Ayland]

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


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