Re: strange and slow joining of nested views

> Von: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Gesendet: Samstag, 4. Februar 2017 06:16
>
> Titus von Boxberg <titus(at)elbe-informatik(dot)de> writes:
> > I got the following problem for which I could not find a solution by
> searching the archives:
> > I have Tables Ta, Tb, Tc with primary keys as bigserials.
> > Ta references Tb references Tc.
> > Not all but most rows in Ta reference exactly one row in Tb.
>
> Hm, your problem query has 11 table scans (not to mention a couple of
> subplans) so you're oversimplifying here. Anyway, I think that
> increasing join_collapse_limit and/or from_collapse_limit to at least 11
> might help.
> As-is, you're more or less at the mercy of whether your textual query
> structure corresponds to a good join order.
>
> regards, tom lane

Thanks, I found the problem:

In the slow join case the planner always fails to restrict
one subselect in the joined view using EXISTS and one with a SUM clause
to the the one row that actually gets used by the join.
Both use functions that I forgot to declare STABLE.
After correcting this, the query is fast and the explain output looks like expected.

Still, it would be nice to know what makes the join different from a subselect.
setting geqo = off and varying join_collapse_limit and from_collapse_limit
from 1 to 50 did not change anything in the initial behaviour.
Shouldn't the planner eventually find them being equivalent?

Regards,
Titus