dimension theory - Why do modules with small support have high Exts?

To understand what "nice" is in your sense has been a very interesting question in commutative algebra.

In the following discussion I will assume, unless otherwise notice, that $(R,m,k)$ is Noetherian local, and $M$ is finitely generated. Let $(1)$ be the codimension of support of
$M$ and $(2)$ be the biggest non-vanishing index of $text{Ext}(M,-)$.

First, the number (2) is finite forces $M$ to have finite projective dimension by taking $N=k$ the residue field. So we will assume $text{pd} M <infty$. Then, as BCrd pointed out:

$$ (2) = text{pd} M = text{depth} R - text{depth} M$$

The first inequality is easy by computing Ext via a projective res. of M + Nakayama lemma. The second is the Auslander-Buchsbaum theorem.

On the other hand:

$$(1) = text{dim} R - text{dim} M $$

So

$$(1) - (2) = (text{dim} R - text{depth} R) - (text{dim} M-text{depth} M) $$

Thus, if both $R,M$ are Cohen-Macaulay (which by def. means dim=depth) and $text{pd} M <infty$ then $(1) = (2)$. If $R$ is "more Cohen-Macaulay" then $M$, we will have $(1)<(2)$.

The situation described in Emerton's answer is also very interesting. In general, the smallest index for which $text{Ext}^i(M,N) neq 0$ is the biggest length of an $N$-regular sequence inside the annihilator of $M$. When $N=R$ this number is called the grade of $M$, which I will call (3).

It is easy to see that $(3) leq (1)$ in general. One can prove that $(1) = (3)$ if $R$ is Gorenstein as follows: By Local Duality, $text{Ext}^i(M,R)$ is Matlis dual to the local cohomology module $text{H}_{m}^{d-i}(M)$, here $d= text{dim} R$. It is not hard to show that local cohomology vanish beyond $text{dim} M$, QED.

Amazingly, it has been an open conjecture for 50 years that $(1)=(3)$ whenever $M$ has finite projective dimension!

For "intuitive" understanding, I would offer the following: often when study modules of finite projective dimension one draw inspirations from those of the form $R$ modulo a regular sequence (so the resolution is a Koszul complex). In such case one can easily see that $(1) = (2) =(3)$.

EDIT: I got too caught up in the results and forgot your main question: why bigger codimension implies bigger projective resolution? A very low-tech way to see it is: bigger codimension means bigger annihilator of $M$. Now each element of the annihilator of $M$ gives a non-trivial relation on elements of $M$, namely $ax=0$, so it is not surprising that the modules with bigger annihilators have more complicated resolutions.