The inner product in a $L^2$ space can be defined as:
$$\langle f,g\rangle =\int_a^b \bar{f}(x)g(x)w(x)dx$$
For Legendre polynomials, we define it as:
$$\langle P_m,P_n\rangle =\int_0^1 \bar{P}_m(x)P_n(x)dx$$ so $w(x)=1$.
But there are case in which $w(x)\neq 1$. For example, Laguerre $w(x)=e^{-x}$ and Hermite polynomials $w(x)=e^{-x^2}$.
Is there any intuition/motivation behind different weight functions of orthogonal polynomials? I think it might be related to measure theory and Sturm-Liouville problems.
I'm not sure this is a great answer, but in the case of the Legendre polynomials, you are working on a compact interval, so the given inner product with weight $\equiv 1$ makes sense.
On the other hand, for the Laguerre and Hermite polynomials, you work on the intervals $[0,\infty)$ and $(-\infty, \infty)$, respectively. Since products of polynomials are not integrable on these infinite intervals, you need some weight in the inner product just to get convergence in the integrals. But you can't just choose any weight: you need weights that decay faster at $\infty$ than the reciprocal of any polynomial. Hence choosing weights like $e^{-x}$ (for the Laguerre polynomials) and $e^{-x^2}$ (for the Hermite polynomials).
These choices seem very natural to me. I think if you were to ask a random mathematician to name a positive function on $\mathbb{R}$ that decays faster than the reciprocal of any polynomial, they would probably say $e^{-x}$.
For the Hermite polynomials, where you are working over $(-\infty, \infty)$, you need to have a weight that decays quickly at both $-\infty$ and $\infty$, hence $e^{-x^2}$.