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

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"

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services