It's primarily the hum, I think. They're heavier winds than most Fender singles so the noise is significant.
And they don't have the sparkle of a narrower single (or as mentioned above, the bite of a Firebird rail pickup).
For decades, P90s were considered sort of obsolete and inferior to humbuckers.
As rigs evolved and gain levels increased exponentially, noise became an even more critical issue.
With more flavors of humbucker available, even longtime P90 fans like Leslie West switched away from them.
At the same time, they began to see renewed interest precisely because they have a different sound of their own.
Some love having fast singlecoil attack in something fatter and a tad smoother than traditional Strat or Tele singlecoils.
Other pickup types in that middle ground have seen a big resurgence in the 21st century too.
Overwound Strat pickups have become hugely popular, especially for bridge position. (Understandably, IMO.)
Minihums are making a comeback among rockers, and Jazzmasters are becoming more popular too.
Today, more players relish a sound that isn't quite so mainstream as a straight humbucker or a Strat pickup.
I love P90 tone myself, despite the noise issue. Particularly in neck position, paired with a humbucker or Tele bridge pickup.
Still, there's nothing quite like the bark & growl of a good P90 in bridge position. Wonderful and vintagey.
It also seems as if these days more and more people are hip to the fact that hotter isn't always better.
Especially relevant to P90s because of the noise factor.
Noiseless versions are continuing to evolve, too: I have a pretty good set of noiseless P90s that Kent Armstrong made.
They're 90% there - recognizably P90 tone, just a tad more polite than the real thing.
Recently learned that hum-sized P90s are better-shielded than traditional ones; for me that makes them well worth trying.
