What is Adobe InDesign used for as opposed to Photoshop or Illustrator?
Page layout as opposed to raster image editing/creation or vector image editing/creation.
Lauren's answer is correct, in that InDesign is a layout program, but in the last few years InDesign has taken on many more tasks than just layout-for-print. With CS3, Adobe built a "headless Photoshop" (the actual Photoshop code) into InDesign, giving us drop shadows, bevels, glows, feathering, etc., and they expanded these capabilities greatly in CS4 and CS5. CS5 added an RGB document mode, nominally for web layouts, but it goes much further than that.
With CS5, many of the tasks that were once exclusively Photoshop are now done faster and much more accurately in InDesign. For example, digital billboards, display screen and web banners can be put together much more quickly in InDesign than in Photoshop, especially if you have images that need careful cropping. It's very difficult in Photoshop to tell what the effective resolution of an imported image asset is, once it's been placed as a Smart Object, but it's very easy to keep track of this in InDesign. You can even tell the program to warn you if there's an image in your layout that is below a certain resolution. Cropping a placed image in Photoshop is a matter of playing with masks and is awkward to modify, where in InDesign cropping and scaling are simple. All these things get you to the finished product (and payment!) faster, so you can do more work and earn a better living.
Typography in ID is light years ahead of the fairly primitive tools in PS, and many of the simple vector drawing tasks that might in times past have required Illustrator can be done right inside InDesign.
In advertising work, where the same branding elements (colors, text styles, object styles, image assets, etc.) in a campaign must be duplicated across print, web and electronic displays, InDesign is the hub of the entire workflow. Photoshop and Illustrator are used to create individual elements that need their capabilities, but everything comes together in InDesign. For many jobs, these days, I use Photoshop only to retouch image assets and Illustrator only to create or tweak complex vector graphics, but better than 90% of the work is done in InDesign.
For ePub or magazine work targeted at mobile devices, InDesign CS5.5 is the must-have tool. CS5.5 also expanded InDesign's HTML capabilities immensely (and ePub, which is just HTML and CSS under the hood), making possible a workflow in InDesign that allows print and web assets to be created simultaneously. This opens up many possibilities, particularly for small shops and freelancers, that were previously too hard or took too long to be viable.
InDesign also reigns supreme in its original niche: for book design, for any project that requires advanced typography, and for many automated workflows such as catalog or directory publishing. Because of the way InDesign documents are built, larger corporations can even create entire InDesign documents, using custom programming, straight out of a database -- without requiring InDesign itself until the final step of actually publishing the document.