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? 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. > (2) Is JOIN selectivity a fraction of table2 X table1 > records ? > I've tested this. It is a fraction of table2.rows X > table1.rows. 0.1 is probably a big number for that... Hehe indeed :) The reason this hit my TODO list was that I attempted a join on two large geometry columns and ended up with a query plan that was doomed to failure..... Maybe we should suggest some improved wording for the documentation? 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 7: don't forget to increase your free space map settings