What exactly is AVIF doing in this case that I can't do with two <img> tags at a smaller size? I can easily create a thumbnail that beats the AVIF quality in less than the 5KB delta between the static and progressive version in Squoosh.

The quick answer is: you can have up to four passes. With progressive AVIF, you let the browser avoid rendering previous passes if a subsequent one has already been downloaded. It's more efficient and saves battery.