Re: remove_useless_joins vs. bug #19560

Richard Guo <guofenglinux(at)gmail(dot)com> writes:
> I did some testing of planning time. Independent removals are fine,
> even faster than before: 64 removable joins that each join directly to
> t0 plan in 0.57 ms here vs 1.11 ms on master. They all go in the
> first pass, and one extra pass over a single-rel query is cheaper than
> the per-removal cleanup the old code did.

Thanks for looking into this!

> Chains of dependent joins are not fine though:

> select t0.id from t0
> left join t1 on t0.nxt = t1.id
> left join t2 on t1.nxt = t2.id
> ...
> left join t64 on t63.nxt = t64.id;

> Only t64 can go in the first pass; t63 still looks needed because
> attr_needed for t63.nxt includes t64's join, and we only find out
> otherwise after the restart. So each link costs a full pass, and this
> plans in 20.9 ms vs 1.98 ms on master. The gap widens quickly with
> the chain length, and views built on views give the same shape.

I'm skeptical that this example is a deal-breaker, because it seems
quite artificial. Queries with this many relations would typically
take a long time to plan. If we're somewhat slower to deal with the
edge case where most of the joins vanish, is anyone really likely to
notice? So I'm hesitant to add complexity in hopes of making the
edge case faster. Especially since this is a bug fix that I'm hoping
to back-patch; every dollop of complexity adds risk.

regards, tom lane