Re: join selectivity

"Mark Cave-Ayland" <m.cave-ayland@webbased.co.uk> 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?

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