Re: Removing Functionally Dependent GROUP BY Columns

On 24 January 2016 at 08:20, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> writes:
>> On 23 January 2016 at 12:44, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> * What you did to join.sql/join.out seems a bit bizarre. The existing
>>> test case that you modified was meant to test something else, and
>>> conflating this behavior with the pre-existing one (and not documenting
>>> it) is confusing as can be. A bit more work on regression test cases
>>> seems indicated.
>
>> The history behind that is that at one point during developing the
>> patch that test had started failing due to the group by item being
>> removed therefore allowing the join removal conditions to be met. On
>> testing again with the old test query I see this no longer happens, so
>> I've removed the change, although the expected output still differs
>> due to the group by item being removed.
>
> Hmm ... but ... it seems to me that the test as it stands *should* fail
> after this patch, because once the non-pkey grouping column is removed
> the join removal optimization should apply. I think we should look a bit
> more closely at what's happening there.
>
> (IOW, I wasn't so much unhappy with the change to that test case as
> that it was being used as the only test case for this new behavior.
> I see you added some new, separate test cases, so that's good; but
> there's something fishy if the existing case doesn't change behavior.)

Thanks for looking at this again.

I've looked into why the join is not removed; since the redundant
GROUP BY columns are removed during planning, and since the outer
query is planned before the sub query, then when the join removal code
checks if the subquery can been removed, the subquery is yet to be
planned, so still contains the 2 GROUP BY items.

Perhaps the useless columns can be removed a bit earlier, perhaps in
parse analysis. I will look into that now.

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