Hi strk, > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: 13 December 2004 14:05 > To: Mark Cave-Ayland > Cc: [EMAIL PROTECTED] > 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 ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match