Add more clear explanation of when to use different profiling modes

[Rperry2174]

Right now we have connect, exec, push, and pull and it may not be immediately obvious when to use each of these modes for pyroscope.

We should either make one page that explains the differences / nuances / use cases for all these or/and do a more in-depth example on the pages themselves of showing them in aciton:

https://pyroscope.io/docs/agent-configuration-connect/#pyroscope-connect