On 06/06/2016 07:40 PM, Tom Lane wrote:
Tomas Vondra <tomas.von...@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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
