Case in point: I'm currently working on an application that uses mod_auth_kerb to perform Kerberos password authentication on behalf of users, then fork a background server which has access to that user's Kerberos credentials, and proxy future connections from that user to the dedicated backend.
Doing that using mod_rewrite requires using the RewriteMap 'prg:' map type, which introduces a single choke-point in the application: namely, the single script responsible for spawning the backend servers. That script also runs in the one Apache master listener, which means that no other incoming requests will be answered until the rewrite script returns its output.
If I were instead using mod_perl (as I suspect I will be, before this moves into production) I would be able to have each Apache child process handle its own forking of backends for requests it was handling.
The Apache request-handling chain is much richer than simply passing off incoming requests to a CGI or PHP script. Using mod_{perl,python,ruby,etc.}, you can implement authenticators, URL-rewriting modules, log handlers, output filters, etc., and take advantage of the mature Apache infrastructure for such code.
Basically, if you want to be able to slice and dice HTTP requests at every stage of processing, not just the content-generation one, the Apache mod_* add-ons are a pretty powerful tool for doing just that.