I'm definitely not an artist, but Lightwave 3D (http://www.newtek.com/products/lightwave) remains one of my favorite applications.  I suspect this is because it's one of the (few) types of applications that has escaped the trend of simplification at the expense of depth.
 
It seems that there are increasingly fewer desktop applications that have a steep but rewarding learning curve.  Most are built to be usable immediately out of the box, but over time they create a low ceiling on creativity and productivity.  Granted, for certain types of apps this is the correct thing to do, but I don't think it's the right goal for all consumer-level applications.  (Of course, I'm just a dev.  Any time I try to increase the learning curve on any of my features I tend to get smacked down by the PMs and Usability folks.  ;-)
 
But what about using computers as a hobby, or to enable a hobby?  Many people aren't using their home computer for anything except web browsing and a little bit of email and IM.  Is this because computers are inherently uninteresting or because their existing software quickly becomes boring and isn't fun or rewarding to use?
 
I suspect this is one reason why Linux is popular with younger techies.  Regardless of whether or not you're a programmer, it gives you more toys to play with; toys that grow on you as you learn and master them.
 
--
The web site of the day is CG Talk (http://www.cgtalk.com), a 3D enthusiast's web site with tons of talented artists, both amateurs and pros.  I've even seen some posts by one of the guys who created Gollum from Lord of the Rings.  Visit the gallery to view some of the recent popular images.