Re: LEFT JOINs not optimized away when not needed

On Wed, Jul 9, 2014 at 1:37 PM, Moshe Jacobson <moshe(at)neadwerx(dot)com> wrote:
> On Wed, Jul 9, 2014 at 1:33 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Right. Yeah, I was not that excited about its usefulness either.
>> However, the OP seems to think that it's a common optimization
>> (I wonder if he can cite chapter and verse on which other DBMSes
>> do it).
>
> I make no claim about its commonness, but I think this type of optimization
> is very useful when selecting a limited subset of the columns from a complex
> view, particularly where the output rows are expected to be distinct on the
> exact set of columns that are selected.

Hmm. So maybe something like this?

CREATE VIEW person_with_cars AS
SELECT p.id, p.full_name, p.something_else, array_agg(c.plate_number)
AS plate_numbers
FROM person p LEFT JOIN cars c ON p.id = c.person_id
GROUP BY p.id, p.full_name, p.something_else;

It's reasonable to hope that if the aggregated column isn't selected,
the join will get removed, but cars (person_id) is not unique.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company