Two games in particular stick out in my mind as having been mind-blowingly great (at the time) and then I realized what steaming piles they were after going back to them. Both are PS1 games.
Final Fantasy VIII: At the time, I was totally enamored with Squaresoft and FFVII had been, like, SOOOO awesome. While FFVII is still (to me) a solidly made game with a number of flaws, it was when I tried to replay FFVIII several years later that I realized it truly was an awful game and never deserved the praise it got. I know it's the most popular FF to hate on, but everything about it was so poorly designed that the trainwreck nature of it is all the more painful.
Vagrant Story: Worst. Action. RPG. Battle. System. Ever.
Granted, even at the time I didn't really "get into it" because I tried hard, even using the strategy guide to work my way through, and I lost interest...booting it up years later, I realized it wasn't just me. It truly was an awful POS.
And what are yours?