Cannabis depressant effects are way stronger than its stimulant effects. I have never heard someone being more productive on marijuana. Maybe what they are saying is that it relaxes them and helps them escape that productivity loop.
The amounts/ratios of them largely determine what you experience. But in addition to that, the subjective effects are highly modulated by the NON-cannabinoids components as well - the "terpenes". Terpenes are in many (most?) other plants. For example, linalool (the reason for lavender's smell, which can induce relaxation on its own), or pinene (probably the most common terpene, smells like pine trees and can have stimulant-like effects on its own). My armchair-pharmacologist theory is that THC makes you much more sensitive to the effects of the terpenes.
A lot of...ahem...testing...might need to happen to dial in what works for you. I'm starting to notice (extracted) products with cannabinoid/terpene profiles printed on the labels - which is a great start. Not sure if it's only in this state, though.
Conversely, I've had amazing experiences with small doses of lsd and shrooms. They've made me feel more alert, creative, and more optimistic.
I'm always surprised when people who are otherwise obsessed with productivity (in tech, finance) tell me they enjoy weed because it seems to enable the exact opposite effect.