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