Re: [HACKERS] Removing [Merge]Append nodes which contain a single subpath

On Mon, Dec 11, 2017 at 2:42 PM, David Rowley
<david(dot)rowley(at)2ndquadrant(dot)com> wrote:
> On 11 December 2017 at 21:18, Ashutosh Bapat
> <ashutosh(dot)bapat(at)enterprisedb(dot)com> wrote:
>> On Thu, Dec 7, 2017 at 5:11 AM, David Rowley
>> <david(dot)rowley(at)2ndquadrant(dot)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