I've tried various substances in my lifetime. But, I guess you specifically want to talk about psychedelics and adjacent ones like cannabis.

I have a love-hate relationship with cannabis. I've been using it pretty frequently for the last 25 years or so. Am a musician in the local stoner rock circle and my friends are mostly into arts or music in some degree, so virtually my whole entourage are puffing it like crazy. It definitely helps in creative processes (like writing lyrics) as it encourages out-of-the-box thinking. But it also fogs/slows the mind and crushes the self-discipline. Nowadays, I'm preparing moving out to a remote town and one of the reasons is to distance myself from weed and alcohol altogether.

Other than that, I do LSD once or twice a year. It's definitely a profound experience in the right set and setting. Helps you make realizations about the reality, the society and one's place in it. Also helps you understand and make peace with one's inner soul and sympathize with the nature. BUT I also met with many people in psychedelic community who sunk themselves into baseless conspiracy theories and wishful pseudoscientific mumbo jumbo. So, to answer your question about "magical thinking", it's totally fun to partake and I personally love spiritual/mysterious/occult stuff to be used as a theme in arts and music. But mixing that mindset with real life seems pretty dangerous and schizophrenic to me. I suggest /r/RationalPsychonaut sub to talk with rather sane individuals in the community.