For me, it was the Finding Nemo game for the gamecube. There were so many loading screens and they all took forever. I've always loved that game but I've never dealt with loading screens as long as that.
Weckless: The Yakuza Missions on the Gamecube. It had terribly long load times and the game sucked.
Metal Gear Solid 4 had seven minute plus long installs between gameplay chapters and for the life of me I don't know why since most missions in that game were 90% cutscenes.
BotW loading screen felt eternal, it was a chore to warp between shrines. also the splatoon loading screens felt really long but maybe it was my sucky wifi
Monster Hunter World is terrible too on console. Going to PC was a godsent. You can literally already be in a hunt before the console even loaded the game, and then it still needs to load the quest.
Rayman on the Switch is really bad here. it's the worst offender here. Simply because the game's assets are too compressed and thus the file size is too small. They went from one extreme to the other on Switch.
Breath of the Wild could take upwards of 30 seconds to load the entire map between cutscenes, fast travel, etc. Conversely, Xenoblade Chronicles 2 only took about 3 seconds to load a Titan.
Witcher 3: Wild Hunt would take anywhere from 45 seconds to over a minute, and that includes the unskippable, insufferably slow "recap" narration after booting up the game. Heck, optimization was so bad that the menu interface would lag severely.