Re: LEFT JOINs not optimized away when not needed

Moshe Jacobson <moshe(at)neadwerx(dot)com> writes:
> On Tue, Jul 8, 2014 at 11:19 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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