Based mainly on my own single experiment (so that there is no statistical power whatever to my results), having a period of mental out-spacing somewhere in the middle years of life seems modal. Naturally, it manifests differently with differing personal histories.
That sense of oneself behaving far more stupidly than one's self-appraised potential would lead one to expect ... Notice how much of that apparent low level of performance is based on how your own behavior appears to yourself when you yourself compare with the standards set for yourself by yourself.
I keep thinking this life stage is parallel to the rebellion of the early teen years, when kids develop an unrealistic assessment of their own abilities and want permission to act out their illusions. "Antiparallel" might be a better word; in middle years we have been acting out our personal agency (using the jargon of psychology) and upon looking back we become somewhat frightened about how deceived we actually have been about our own competence. Well, anyway, that was my experience.
Now, if you add to that a pondering on what God must be thinking, you have a start to finally understanding the OT admonition that "fear" of the Lord is where the wisdom of age finally begins.
Before this, wisdom seems like the accumulation of experience, or possibly a social action organization. Afterward, wisdom seems more like a change of state: a melting of the brittle strength of ice into the ineffable strength of water, to evoke a common metaphor of older people.
In actual reality you are going to keep saying, "I can't believe I did that!" Over and over. But, going forward, that unbelief won't be accompanied by the same level of shock and surprise.