I'm glad I'm not the only one who was weirded out by the glossing over of the "it's better in the 20-40MHz range, I guess?" punch line. That's kind of exactly the frequency band where you need the reinforcement.
The article does get it right that you need to reduce the parasitic elements to the chip, but you have to consider like, everything - not just the wires to the package, but also the lead frame, bond wires, the wiring in the chip. Usually the chip designers modeled that all out and they put decoupling capacitors on chip and did PCB model simulations that includes some specific assumption about the impedance curves of the off-chip caps, and they probably used 0.1uF in their simulations.
If anything, you need closely placed decaps to prop up higher frequencies, not lower frequencies. Remember if you have a SPI bus clocking at 25MHz, 25MHz is just the fundamental - you have the whole fourier series going up to 100's of MHz on the edge.
The answer I had always seen looking at the chip models is that there is an off-chip capacitance value below which it does not make sense to use because the bond wires effectively isolate the chip above certain frequencies (i.e., while smaller value capacitors have higher SRF it doesn't matter because the chip can't "see" the capacitor due to the bond wires screening it out).
If you knew where that roll-off was, and you knew the curve of your capacitors + board parasitics, you'd place a cap as close as you can to the chip in the frequency band right below the roll off of the bond wires to prop up that zone. Then, you'd place larger caps farther away because, as the article notes, the inductance goes up but also you're just looking to prop up the higher impedance-at-lower-frequencies curve of the tiny cap that's close to the chip.
So a lot of it depends on the exact chip you're working with and how well designed it is. A classic chip design team would have an expert who did all the parasitic modeling of the package, board, and then they'd do a noise analysis on the chip and recommend a minimum on-chip decap so the board designers don't have to worry too much, they can get away with "almost anything" in the 0.1uF range. Unfortunately chip design teams are getting leaner and leaner these days and I don't see the same level of care being put into chips. I think we more or less get away with it because there is so much margin in the chip timing; also modern chips are "mostly" (>50%) fill cells -- e.g. decoupling capacitors -- that are placed right up against the logic gates so you can get away with bloody murder on the package and power distribution networks (background: modern chips are wiring-limited, not transistor-limited, but for process stability reasons you still need to make transistors everywhere at a uniform density, so they instantiate dummy transistors that are wired as capacitors between power and ground).
Where it really starts to matter is if you had e.g. a PLL and you're trying to reduce noise that the loop filter can't get rid of and in those cases often times you need much smaller capacitors because they have much better performance at higher frequencies. Yes, they suck at low frequencies - but your noise problem isn't in the 1MHz band anyways; the loop filter can track that out. It's going to be in the 100MHz+ range.
And as someone noted elsewhere, in-rush current is a real problem, and too much capacitance can cause a problem for regulator stability; especially the extremely high performance ceramics. And, if you're doing an extremely power efficient design you may need to consider factors like leakage and losses due to CV-energy cycling if you shut down significant portions of the design when not in use.
(edits for clarity)