[postgis-devel] RE: join selectivity

[strk at refractions.net]

On Mon, Dec 13, 2004 at 12:16:15PM -0000, Mark Cave-Ayland wrote:
> 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? 

Bingo.
Both ENABLE_SEQSCAN = 'f' or unavailability of an index make the
selectivity estimator calls go away.
The join selectivity is called nonetheless (also in absence of indexes).

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

Sure that'll be nice to have. 

Anyway initial skeleton is in CVS.
You need to create postgis_gist_joinsel and
change oprjoin to postgis_gist_joinsel
for that to work. 

UPDATE pg_operator
SET oprrest = 'postgis_gist_sel',
    oprjoin = 'postgis_gist_joinsel'::regproc
WHERE oprname = '&&' AND
  ( oprcode = 'geometry_overlap'::regproc
    OR oprcode = 'box2d_overlap'::regproc );

It currently only returns #define DEFAULT_GEOMETRY_JOINSEL 0.000005

--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
>  
> 
> 
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel