But then is there some way to tell Optimizer not to consider transitive joins ?
Or to know if the join is transitive or not ?
On 14-Oct-2017 3:43 AM, "Tom Lane" <
tgl@sss.pgh.pa.us> wrote:
Gourav Kumar <gourav1905@gmail.com> writes:
> For e.g. I am checking for this query
> ...
> where
> and ss1.ca_county = ss2.ca_county
> and ss2.ca_county = ss3.ca_county
> and ss1.ca_county = ws1.ca_county
> and ws1.ca_county = ws2.ca_county
> and ws1.ca_county = ws3.ca_county
> It doesn't has a join predicate between ss1 and ws2 or ss1 and ws3. But
> optimizer still considers a join among them.
Sure it does, after transitive propagation of those equalities;
for instance we can derive ss1.ca_county = ws2.ca_county from
the above-quoted conditions. And it would be very stupid of the
optimizer not to consider those derived join conditions, because
they may lead to the optimal join order.
In general it's already true that the optimizer doesn't consider
clauseless joins unless there's no other choice. But this example
isn't showing such a case.
regards, tom lane