[postgis-devel] RE: join selectivity

[strk at refractions.net]

On Mon, Dec 13, 2004 at 03:04:01PM -0000, Mark Cave-Ayland wrote:
> 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?

Index on a single table makes 2 calls to RESTRICT:

  strk=# select * from test1, test2 where test1.geom && test2.geom;
  NOTICE:  LWGEOM_gist_joinsel called (returning 0.000005)
  NOTICE:  LWGEOM_gist_sel called
  NOTICE:   no constant arguments - returning default selectivity
  NOTICE:  LWGEOM_gist_sel called
  NOTICE:   no constant arguments - returning default selectivity
  
Index on a both table makes 4 calls to RESTRICT:

  strk=# select * from test1, test2 where test1.geom && test2.geom;
  NOTICE:  LWGEOM_gist_joinsel called (returning 0.000005)
  NOTICE:  LWGEOM_gist_sel called
  NOTICE:   no constant arguments - returning default selectivity
  NOTICE:  LWGEOM_gist_sel called
  NOTICE:   no constant arguments - returning default selectivity
  NOTICE:  LWGEOM_gist_sel called
  NOTICE:   no constant arguments - returning default selectivity
  NOTICE:  LWGEOM_gist_sel called
  NOTICE:   no constant arguments - returning default selectivity
  
No index makes no calls to RESTRICT:

  strk=# select * from test1, test2 where test1.geom && test2.geom;
  NOTICE:  LWGEOM_gist_joinsel called (returning 0.000005)

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

8.0.0RC1

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