On Mon, Dec 11, 2017 at 2:42 PM, David Rowley <david.row...@2ndquadrant.com> wrote: > On 11 December 2017 at 21:18, Ashutosh Bapat > <ashutosh.ba...@enterprisedb.com> wrote: >> On Thu, Dec 7, 2017 at 5:11 AM, David Rowley >> <david.row...@2ndquadrant.com> wrote: >>> While rebasing this today I also noticed that we won't properly detect >>> unique joins in add_paths_to_joinrel() as we're still testing for >>> uniqueness against the partitioned parent rather than the only child. >>> This is likely not a huge problem since we'll always get a false >>> negative and never a false positive, but it is a missing optimisation. >>> I've not thought of how to solve it yet, it's perhaps not worth going >>> to too much trouble over. >>> >> > [...] > >> Do you have a testcase, which shows the problem? > > I do indeed: > > create table p (a int not null) partition by range (a); > create table p1 partition of p for values from (minvalue) to (maxvalue); > create unique index on p1 (a); > create table t (a int not null); > > insert into p values(1),(2); > insert into t values(1); > > analyze p; > analyze t; > > explain (verbose, costs off) select * from p inner join t on p.a = t.a; > QUERY PLAN > ----------------------------- > Nested Loop > Output: p1.a, t.a > Join Filter: (p1.a = t.a) > -> Seq Scan on public.t > Output: t.a > -> Seq Scan on public.p1 > Output: p1.a > (7 rows) > > explain (verbose, costs off) select * from p1 inner join t on p1.a = t.a; > QUERY PLAN > ----------------------------- > Nested Loop > Output: p1.a, t.a > Inner Unique: true > Join Filter: (p1.a = t.a) > -> Seq Scan on public.t > Output: t.a > -> Seq Scan on public.p1 > Output: p1.a > (8 rows) > > Notice that when we join to the partitioned table directly and the > Append is removed, we don't get the "Inner Unique: true"
Ok. I thought we don't use unique joins in partition-wise joins. Sorry for the misunderstanding. I think this didn't work with inheritance before partition-wise join as well for the same reason. Probably, we could do it if unique paths (sorted paths which can be unique-ified) bubble up the Append hierarchy. We already have code in add_paths_to_append_rel() to create sorted paths when even a single child has an index on it. -- Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company
