The detective is close to solving the murder that has been buzzing with supernatural explanations and elements. Perhaps even the detective may have come to believe that the paranormal REALLY MAY be involved. However, now the truth is about to come to light, and all he needs is the witness. Turns out, all this time, the murder did have natural explanations. The witness saw the murder take place as a child, either recently or long ago, then repressed and/or masked the memory of it.
A more specific form of Trauma-Induced Amnesia where the blanks are filled in with the supernatural or paranormal; ghosts, demons, monsters, spirits, etc.
One example is an episode of the BBC series Sherlock, "The Hounds of Baskervilles," where the witness discovers that his father, who he believed to be murdered by a giant dog, was really murdered by an old colleague testing psychotropic nerve gas (while wearing a sweater with a dog on it). Another occurs in an episode of the American show Castle, where the victim was killed in a house that was rumored to be haunted because of a killing that took place that the victim was a witness too; but he repressed the memory and chose instead to investigate the paranormal explanation (the house also features several murders and a common theme that the murders are performed by demons). The second one is also a potential example of an exploited trope, since the victim was murdered by a man who knew both the house's reputation and the victims own memory of the event, kills him in a way that fits with the legend.
Consistency type: This trope is, as far as I know, rather consistent with reality since there are cases where a traumatic memory was forgotten or modified by the individual. Often used mostly in series already very grounded in reality.
Type: Plot device
Medium type: Common in works of mystery and crime dramas, either in print or television formats.
Current life-cycle status: The trope is played straight when it is played at all. The villain often uses this to their advantage, making it a kind of explain.