Re: LEFT JOINs not optimized away when not needed

Moshe Jacobson <moshe@neadwerx.com> writes:
> On Tue, Jul 8, 2014 at 11:19 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> What's your setting of join_collapse_limit, and if it's less than
>> 27, does raising it fix this query?

> We raised it to 30 and restarted postgres but the query plan still touched
> all of the tables.

All of the tables?  Your upthread EXPLAIN result (which I duplicated here)
shows it touching only nine of the 27 tables.  AFAICS, the eight
un-removed joins are either joining to non-unique keys, or can't be
removed because their tables are needed to provide joining columns for
un-removable joins.

My thought that join_collapse_limit might matter was incorrect --
I was thinking that remove_useless_joins() was invoked on subproblems,
but actually it considers the whole jointree.  This is borne out by
experimentation; changing it doesn't change the set of removed joins
in this example.  (Which is a good thing because you really didn't want
to run with a collapse_limit as high as 30.)

So it looks to me like it's operating as intended.

            regards, tom lane