A study of almost 9,000 men and women found that people aged 50 to 64 who sleep for less than six and more than eight hours a night have worse decision-making ability and memories.
But for older adults aged 65 to 89, brain power was only impaired if they slept over eight hours a night.
And getting the right amount of sleep at an older age may prevent mental illness in later life such as dementia, the researchers at the University of Warwick suggested.
Researcher Professor Francesco Cappuccio said: 'Sleep is important for good health and mental wellbeing.
"Optimising sleep at an older age may help to delay the decline in brain function seen with age, or may slow or prevent the rapid decline that leads to dementia."
Co-author Dr Michelle Miller said that the study's finding suggested that the amount of sleep people need and its effect on their health changes with age.
"Six to eight hours of sleep per night is particularly important for optimum brain function in younger adults," Dr Miller said.
"These results are consistent with our previous research, which showed six to eight hours per night was optimal for physical health, including lowest risk of developing obesity, hypertension, diabetes, heart disease and stroke."
Prior studies have shown that getting enough sleep allows the brain to cleanse itself of harmful toxins accumulated during waking hours.
It is less apparent why too much sleep could be harmful, but existing research has linked oversleeping with medical problems such as heart disease and diabetes.
But researchers have said that the link may be partly due to oversleeping being more common among those suffering from depression, the unemployed and the poor, who are more likely to suffer health problems.
The findings of the latest study were published in the journal Plos One, based on data from 3,968 men and 4,821 women between 50 and 90 in the English Longitudinal Study of Ageing.
They registered the quality and quantity of the subjects' sleep over one month.
Responding to the results, Dr Laura Phipps, of Alzheimer's Research UK, said that further investigation was needed to establish whether there is a link between sleep quality and dementia.
She said: "Though many of us will have felt the effects of a bad night's sleep on our memory, this study did not look at dementia and it's too soon to conclude that poor sleep will lead to the condition.
"Further research to better understand the links between sleep quality and our risk of dementia is needed before we can know whether improving our sleep quality could prevent the condition."