Re: Example code Re: Singleton SELECT inside cursor loop

On 10/1/22 15:42, Ron wrote:
> On 10/1/22 14:54, Christoph Moench-Tegeder wrote:
>> ## Ron (ronljohnsonjr(at)gmail(dot)com):
>>
>>> The question then is "why am I just now seeing the problem?"  We've been
>>> using v12 for two years, and it just happened.
>>>
>>> The only recent change is that I upgraded it from RDS 12.10 to 12.11 a
>>> couple of weeks ago.
>> That's correlation, but no proof for causality.
>
> Right.  But it is an important change which happened between job runs (the
> 22nd of each month).
>
>> Now that you've confirmed that you have indeed a mismatch between generic
>> and custom plan, you could compare those (EXPLAIN EXECUTE) and see
>> where the generic plan goes wrong.
>
> I'll rerun the EXPLAIN EXECUTE with and without "plan_cache_mode =
> force_custom_plan", and attach them in a reply some time soon.

Attached are explain outputs from: , and then

TASK001785639_explain_output_custom.log: a "first five" fast execution
TASK001785639_explain_output_generic.log: "the sixth" (generic) plan when it
took 6 minutes.

Next are similar plans except where default_statistic_target = 1000, and the
table is reanalyzed.  It didn't help with this query.

TASK001785639_explain_output_custom_def_stats_1000.log
TASK001785639_explain_output_generic_def_stats_1000.log

Finally is the explain output from "plan_cache_mode = force_custom_plan":
TASK001785639_explain_output_force_custom_def_stats_1000.log

According to meld diff, custom_def_stats_1000 and
force_custom_def_stats_1000 have surprisingly similar plans (the only
difference being that in the forced custom plan, 2 workers were launched,
and so they filtered out some rows.  Execution time was about 460ms in both.

[snip]
>> If would help if you could compare execution plans with plans from
>> before the update, but I guess you didn't document those?
>
> So, since ANALYZE did not help.

EDIT: *No***, since ANALYZE did not help.

--
Angular momentum makes the world go 'round.