Re: join selectivity

On Mon, Dec 13, 2004 at 10:16:09AM -0000, Mark Cave-Ayland wrote:
>
> > -----Original Message-----
> > From: strk [mailto:strk(at)keybit(dot)net]
> > Sent: 10 December 2004 15:35
> > To: Mark Cave-Ayland
> > Cc: postgis-devel(at)postgis(dot)refractions(dot)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) sequencially scan 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
>