They are both capable of producing (no pun intended) great tracks and are very similar. The Producer Edition has more bells and whistles which you may or may not need.
The main difference is that Producer has the following:
1- Surround capabilities
2- EQ integrated into every track
3- Full version of Pantheon verb
4- Sonitus fx Suite
There are a couple other things Prodicer does (time scaling for video, etc...) but these are the main things.
For a complete side by side comparison click here:
http://www.cakewalk.com/Products/SONAR/studio.asp
Now, the real question is what do you need.
I was a PA9 user up until Sonar 3. I upgraded to Sonar 3 Studio because I was weary of Sonar but wanted to take advantage of hyperthreading.
I found S3 to be very stable and upgraded to S3 Producer becasue I wanted V-Sampler (no longer included) and wanted to check out the per-channel eq, etc...
I am running S4 Producer right now and really like it a lot. I hardly ever use a lot of the included features (per-channel eq, Pantheon) but use the Surround capabilities and time scaling often for my line of work (I work on the Cool School series of CD-Roms (
http://www.course.com) and own a studio)
I find that I use 3rd party plugs more than the included set. I have a loaded UAD-1 and TC Powercore (not impressed) but if I didn't have these and wasn't planning to buy them, Producer is worth it for the Sonitus Effects Suite.
The best news is that if you go with Studio and decide you want Producer you can always upgrade from S4 Studio to S4 Producer or upgrade to S5 producer (due out later this year I believe).
What are your system specs and what is your ultimate goal (demos, finished product, etc...)