Teodor Sigaev <teodor@sigaev.ru> writes:
>> BTW, was the larger query plan that you showed (with a Materialize node)
>> generated by 9.6, or v10 HEAD? Because I would be surprised if 9.6 did
> v10,
> commit acbd8375e954774181b673a31b814e9d46f436a5
> Author: Magnus Hagander <magnus@hagander.net>
> Date: Fri Jun 2 11:18:24 2017 +0200
Thanks. Meanwhile, I poked into this larger example (which Teodor had
sent me the data for off-list). I concur with the conclusion that the
speed change is because the HEAD code inserts a Materialize node on
the inside of an inner loop even though it thinks the outside will
produce only one row. In reality the outside produces five rows so
there's a small win from the Materialize, and because this is all in
a SubPlan that gets executed 24121 times, that adds up.
However, I still don't think that this is evidence in favor of forcing
a Materialize on the inside of a nestloop even when we think the outside
will produce just one row; and it's certainly not evidence that we should
do that accidentally in a small number of cases due to a logic error.
This query itself has got four other places where there's a nestloop
with an outer rel that's predicted to return just one row, and in those
four places the prediction is correct. If we were to establish a policy
like that, we'd be adding useless overhead to those other places.
(Out of idle curiosity, I hacked the planner to force materialization
for all non-parameterized nestloop inners, and confirmed that that
adds back a couple hundred msec to this query. It might have been
worse, except that two of the four other places are in SubPlans that
never get executed in this specific example.)
So I think it's just chance that this bug was a net benefit on this
query, and it's not a reason not to go ahead with the patch.
regards, tom lane