Avoid redundant re-evaluation of JSON_TABLE nested paths
86ab7f4c721d makes JSON_TABLE with a NESTED PATH re-ran the nested path's
jsonpath expression several times for each parent row. That makes such
queries significantly slower as the nested arrays grew, even without a PLAN
clause.
Two leftovers from the plan/join executor rework were responsible.
JsonTablePlanScanNextRow() still reset and advanced the nested plan
itself, although JsonTablePlanNextRow() now does that; and
JsonTableResetNestedPlan() eagerly called JsonTableResetRowPattern()
(which evaluates the path) in addition to setting the reset flag that
makes JsonTablePlanNextRow() evaluate it again. Together these caused
the nested path to be evaluated multiple times per parent row.
Reduce JsonTablePlanScanNextRow() to advancing its own row pattern
iterator, and have JsonTableResetNestedPlan() only reset the transient
scan state (so a not-yet-advanced sibling still reads as NULL) while
deferring the actual path evaluation to the reset flag. The nested path
is now evaluated exactly once per parent row, as before the PLAN clause
feature; results are unchanged and are covered by the existing tests.
Reported-by: Thom Brown <thom@linux.com>
Discussion: https://postgr.es/m/CAA-aLv5U94KD4C%2BLhAPYcCeGvs1xBMngcS5oEkZHN9YWwXUHsA%40mail.gmail.com