On 06/06/2016 07:40 PM, Tom Lane wrote:
> Tomas Vondra <tomas.vondra@2ndquadrant.com> writes:
>> On 06/06/2016 06:15 PM, Tom Lane wrote:
>>> This checks that you found a joinclause mentioning foreignrel. But
>>> foreignrel need have nothing to do with the foreign key; it could be any
>>> table in the query.
>
>> I don't follow. How could it have 'nothing to do with the foreign key'?
>
> Precisely that: clauselist_join_selectivity iterates over every table in
> the join as a potential foreignrel, and you explicitly refuse to check
> that that table has anything to do with the foreign key's referenced side.
>
> Here's an example:
>
> drop table if exists t1, t2, t3;
> create table t1(f1 int, f2 int, primary key(f1,f2));
> insert into t1 select x,x from generate_series(1,100000) x;
> create table t2 (f1 int, f2 int, foreign key(f1,f2) references t1);
> insert into t2 select (x+10)/10,(x+10)/10 from generate_series(1,100000) x;
> create table t3(f1 int, f2 int);
> insert into t3 select (x+10)/10,(x+10)/10 from generate_series(1,100000) x;
> analyze t1;
> analyze t2;
> analyze t3;
> explain select * from t1 join t2 on t1.f1=t2.f1 and t1.f2=t2.f2;
> explain select * from t3 join t2 on t3.f1=t2.f1 and t3.f2=t2.f2;
>
> 9.5 estimates the first query as producing 1 row, the second as producing
> 100 rows. Both of those estimates suck, of course, but it's what you'd
> expect from treating the joinclauses as uncorrelated. HEAD estimates them
> both at 100000 rows, which is correct for the first query but a pure
> flight of fancy for the second query. Tracing through this shows that
> it's accepting t2's FK as a reason to make the estimate, even though
> t1 doesn't even appear in that query!
D'oh!
Clearly we need to check confrelid somewhere, not just varno/varattno. I
think this should do the trick
rte = planner_rt_fetch(var->varno, root);
if (foreignrel->relid == var->varno && fkinfo->confrelid == rte->relid && fkinfo->confkeys[i] ==
var->varattno) foundvarmask |= 1;
It seems to resolve the the issue (the estimate is now just 100), but
I'm not going to claim it's 100% correct.
In any case, thanks for point this out.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
