[postgis-devel] RE: join selectivity

[Mark Cave-Ayland]

Hi strk,

(cut)

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

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

Indeed, you could be on the right lines here in thinking the planner
considers some form of individual scan on each first before finalising on a
plan type (although unless the tables are small I would have thought this
would not have been an option). Does this change if you do a SET
ENABLE_SEQSCAN = 'f' before the query? 

It just seems strange for a <column> <operator> <column> clause to call a
function involving a constant. Again, I'd probably ask on pgsql-hackers just
to clarify - I think Tom Lane was involved with the planner, so will be able
to answer this one fairly quickly.

> (2) Is JOIN selectivity a fraction of table2 X table1
>    records ?
 
> I've tested this. It is a fraction of table2.rows X 
> table1.rows. 0.1 is probably a big number for that...

Hehe indeed :) The reason this hit my TODO list was that I attempted a join
on two large geometry columns and ended up with a query plan that was doomed
to failure..... Maybe we should suggest some improved wording for the
documentation?


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