“But I cannot accept that a default, bare-bones WordPress install hasn’t the first clue how to cache and avoid expensive, redundant trips to the database”
Actually, WordPress 2.5 does have just that sort of thing built right into it. It’s called the object cache:
http://neosmart.net/blog/2008/wordpress-25-and-the-object-cache/
Here’s the key though… Where do you store your cached data?
The caching solutions you’re talking about are “whole page” solutions, which cache the resulting HTML output of page generation and serve it up instead of regenerating the page. WP-Super-Cache is particularly effective at this, but does indeed rely on running Apache with mod_rewrite (the current most popular webserver combination).
The built in caching in WordPress is an object cache. Data retrieved from the database is stored and saved somewhere else. But, by default, WordPress does not attempt to tell the user where they should store that data. It’s a framework, with several possible plugins for just that.
See, back in previous versions, WordPress had a built in cache that defaulted to storing the data in a local cache of files. This helps somewhat, but for the most part, it’s not helpful. Storing data in files and retrieving them from files incurs a lot of disk I/O. And what is a database server but something that does much the same thing? So that was ripped out of 2.5 and the generic platform for object caching was put in instead. It can cache to a number of different things, including persistent and fast memory (using memcached). A plugin does exist to cache objects to files though:
http://neosmart.net/blog/2008/file-based-extension-to-the-wordpress-object-cache/
But on the whole, this is not going to reduce your CPU time, because you’re still generating the page. Database access is not the CPU limitation on most hosts, as the database is usually on another server entirely. Thus the need for whole page caches.
Incidentally, WordPress provides the necessary hooks specifically for whole-page caching. This was designed in, not some sort of “hack” that was done by these plugin programmers.
WordPress is designed, from the ground up, to be a base of systems, not to be a complete and total system in and of itself. WordPress does not do everything, it just does one thing as well as it can. You don’t use can openers on luggage, do you?