[postgis-devel] RE: join selectivity

[Mark Cave-Ayland]

Hi strk,

> -----Original Message-----
> From: strk at refractions.net [mailto:strk at refractions.net] 
> Sent: 13 December 2004 14:05
> To: Mark Cave-Ayland
> Cc: postgis-devel at postgis.refractions.net
> Subject: Re: [postgis-devel] RE: join selectivity
> 
> 
> 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).

Right. So what you're saying is that if there is *no* GiST index on *one* of
the geom columns, or sequential scans are disabled, then the calls to
RESTRICT go away?

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

CCd to pgsql-hackers for clarification. BTW which version are you developing
against - 7.4 or 8.0?


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