Re: [postgis-devel] RE: join selectivity
От | strk@refractions.net |
---|---|
Тема | Re: [postgis-devel] RE: join selectivity |
Дата | |
Msg-id | 20041213171329.GB7867@freek.keybit.net обсуждение исходный текст |
Ответ на | Re: [postgis-devel] RE: join selectivity ("Mark Cave-Ayland" <m.cave-ayland@webbased.co.uk>) |
Список | pgsql-hackers |
On Mon, Dec 13, 2004 at 03:04:01PM -0000, Mark Cave-Ayland wrote: > Hi strk, > > > -----Original Message----- > > From: strk@refractions.net [mailto:strk@refractions.net] > > Sent: 13 December 2004 14:05 > > To: Mark Cave-Ayland > > Cc: postgis-devel@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_selcalled 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_selcalled 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 >
В списке pgsql-hackers по дате отправления: