On 10/1/22 15:42, Ron wrote:
On 10/1/22 14:54, Christoph Moench-Tegeder wrote:
## Ron (ronljohnsonjr@gmail.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.