On 11 December 2017 at 21:18, Ashutosh Bapat
<ashutosh.bapat@enterprisedb.com> wrote:
> On Thu, Dec 7, 2017 at 5:11 AM, David Rowley
> <david.rowley@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"
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services