Re: join selectivity

On Mon, Dec 13, 2004 at 10:16:09AM -0000, Mark Cave-Ayland wrote:
> 
> > -----Original Message-----
> > From: strk [mailto:strk@keybit.net] 
> > Sent: 10 December 2004 15:35
> > To: Mark Cave-Ayland
> > Cc: postgis-devel@postgis.refractions.net
> > Subject: join selectivity
> > 
> > 
> > Taking a look at join selectivity...
> > For a query like this:
> > 
> >     SELECT id FROM table1, table2 
> >     WHERE table1.geom && table2.geom;
> > 
> > RESTRICT selectivity is invoked twice and
> > JOIN selectivity is invoked once.
> > The RESTRICT code is not able to find a costant part
> > and thus returns the default value (0.000005),
> > JOIN selectivity so far returns an hard-wired 0.1.
> > 
> > Questions:
> > (1) What should RESTRICT selectivity do in this case ?!
> > (2) Is JOIN selectivity a fraction of table2 X table1
> >     records ?
> 
> 
> Hi strk,
> 
> Really??! I can't see why the RESTRICT selectivity should be called - the
> only thing I can think of is that it's being called as some part of cast or
> query rewriting.

Maybe that's how the planner decide what to do:1) sequencially scan table1 and use index for each row (RESTRICT)2)
sequenciallyscan table2 and use index for each row (RESTRICT)3) ... some other magic I'm missing .. (JOIN)
 
> 
> Hmmm good question - the wording in the documentation is "The idea behind a
> join selectivity estimator is to guess what fraction of the rows in a pair
> of tables will satisfy a WHERE-clause condition of the form" which is
> slightly ambiguous - I would ask on pgsql-hackers now that the mailing lists
> are working normally again.

I've tested this. It is a fraction of table2.rows X table1.rows.
0.1 is probably a big number for that...

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