Re: LEFT JOINs not optimized away when not needed

Moshe Jacobson <moshe(at)neadwerx(dot)com> writes:
> On Tue, Jul 8, 2014 at 4:40 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> No. There is nothing about GROUP BY in the join removal logic.

> OK. Thank you. Is this something that would make sense to add?

Dunno. I don't recall any previous requests for such a thing, so I'd not
be inclined to add it unless it can be done very cheaply (in terms of both
code and runtime). However, it seems like that might possibly be the
case, if someone wanted to pursue making a patch. The existing logic
to check for "no references above the join" would serve fine to exclude
grouping by the inner relation, so it might only be necessary to check
that the query has GROUP BY/DISTINCT at all (and, I guess, no aggregates
or window functions). Or possibly I'm missing something.

> More importantly, do you have any suggestions on how we can improve the
> speed of our views when only a couple of columns are selected from them?

Well, I'd stay away from twenty-seven-way joins if I were you :-(.
That's a recipe for pain. Perhaps denormalizing your schema a bit would
help there.

regards, tom lane