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!
If we made the modifications previously discussed to throw away FKs
that don't connect two tables mentioned in the query, this particular
example would stop failing. But it would only take a three-table
query involving t1, t2, and t3 to bring the bug back to life, whether
or not the join conditions actually match the FK.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
