On Mon, Dec 13, 2004 at 12:16:05PM -0500, Tom Lane wrote: > "Mark Cave-Ayland" <[EMAIL PROTECTED]> writes: > > 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. > > Hm, are you testing in a context where both tables have indexes that are > relevant to the && operator?
Single index relevant to the && operator makes 2 calls to RESTRICT. Double index (one for each table) makes 4 calls to RESTRICT. In both cases JOIN is called once. --strk; > The estimated join result size is computed from the join selectivity > estimate for the && operator. I was about to say that restriction > selectivity wouldn't be used at all, but on second thought I believe > that it would be invoked while considering nestloop with inner indexscan > plans. That is, we'd consider > > NestLoop > Seq Scan on table2 > Indexscan on table1 > IndexCond: table1.geom && outer.geom > > and to determine the estimated cost of each indexscan, we would invoke > restriction selectivity for &&, with varRelid referencing table1. > Given this call you are supposed to treat table2.geom as a constant of > uncertain value, so the thing is semantically sensible as a restriction > clause for table1 (whether you can produce a really good estimate is > another question :-(). > > Similarly, we'd consider the reverse plan with table1 as outer, and > that would give rise to another restriction selectivity check with > varRelid = table2. > > >> (2) Is JOIN selectivity a fraction of table2 X table1 > >> records ? > > Yes. Similarly restriction selectivity is a fraction of records in the > table under consideration. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
