Re: [postgis-devel] RE: join selectivity

Hi strk,

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