From: | Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Robert Haas <robertmhaas(at)gmail(dot)com>, Noah Misch <noah(at)leadboat(dot)com>, Simon Riggs <simon(at)2ndquadrant(dot)com>, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>, Julien Rouhaud <julien(dot)rouhaud(at)dalibo(dot)com>, Michael Paquier <michael(dot)paquier(at)gmail(dot)com>, Stefan Huehner <stefan(at)huehner(dot)org>, PostgreSQL mailing lists <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: pg9.6 segfault using simple query (related to use fk for join estimates) |
Date: | 2016-06-06 18:20:05 |
Message-ID: | 0f872240-0bd4-7d4b-2439-65a1b875e27d@2ndquadrant.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 06/06/2016 07:40 PM, Tom Lane wrote:
> Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Fabrízio de Royes Mello | 2016-06-06 18:23:57 | Re: hstore: add hstore_length function |
Previous Message | Robert Haas | 2016-06-06 18:19:07 | Re: Reviewing freeze map code |