Intuitively it should have something to do with the behavior of $f$ near $0$.
This is true. Indeed, let $U(x):=\int_0^x du\,f(u)$. Then, according to (say) Tauberian Theorems 3 and 2 in Section XIII.5 of Vol. II of "An Introduction to Probability Theory and Its Applications", Second Ed., 1971, by Feller, for andany real $a\ge0$ and any slowly varying at $\infty$ function $L$, $$g(s)\underset{s\to\infty}\sim s^{-a}L(s)\iff U(x)\underset{x\downarrow0}\sim \frac{x^a L(1/x)}{\Gamma(a+1)}.$$