The larger the sensor or film size, the shallower the depth of field for a given subject framing and aperture. The smaller the sensor or film size, the greater the depth of field for a given subject framing and aperture.
That's why you have a lot of depth of field shooting with a point and shoot model versus a dSLR. Because of the smaller sensor size, you can use a shorter "actual" focal length lens for the same framing, resulting in a lot more depth of field with the smaller sensor for a given aperture and framing. The same thing applies comparing medium format to 35mm or APS-C size sensors (i.e, you don't need to stop down as much for the same depth of field for a given subject framing when shooting with cameras using smaller sensors or film sizes, because a shorter actual focal length lens can be used for the same subject framing as you use smaller sensors or film sizes).
A smaller sensor is also one reason why most point and shoot models don't let you stop down the aperture much over f/8 (and some don't even stop down that far).
The smaller the sensor size, the sooner you'll run into diffraction issues (starting to get softer photos past a certain aperture, regardless of lens quality). See the calculator on this page:
Now, some lenses that work with dSLR models can go to f/32 or even f/45. But, as a general rule, once you start getting past about f/11 with a typical APS-C sensor), you're going to start getting softer images than you would if you stuck with apertures around 2 or 3 stops from wide open, no matter what lens you use (as diffraction issues set in because of the sensor's size, regardless of optics design)
It may not make much difference, depending on what you're shooting (i.e., the need for more depth of field can outweigh the softness you'll start to get from diffraction issues, as compared to how much a lens can resolve at more optimum apertures for a given sensor size). But, with a wider lens on an APS-C size sensor, you have lots of depth of field by the time you stop down to around f/11 or so anyway. So, there is rarely any need to stop down much further unless you're shooting macros.