[postgis-devel] RE: join selectivity

[Mark Cave-Ayland]

Hi strk,

Could you post the explain outputs for those queries too? I don't see why
with an index scan consideration between two columns that the planner has to
invoke four RESTRICT functions?


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
 

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