The problem: When you get a blog that is large enough, with a big enough archive, wordpress becomes a massive performance pig. The culprits are some of the queries used, particularly for the sidebar calendar and in building archive pages.
There are queries that are made in such a way that they cannot be index and require all rows to be processed in the posts database. So if you have a blog with 10,000 records, example, you have to process all 10,000 records. There are cases where queries like "COUNT_NUM_ROWS" is used, as well as using year and month as seperate items querying against the date field, rather than setting a top and bottom for a monthly list.
Where this becomes a particular problem is on an older blog, say with 10,000 posts and 2 or 3 years of archives. Googlebot comes calling, does a near simultaneous request for 36 archive pages (1st page for each month for 3 years) and suddenly there are 36 queries lined up that have to treat every single row in a 10,000 record db to produce only 10 items on a page. It is a huge load on the system, and appears to all but lock everyone else out while it is happening. That means that a backlog of other queries piles up, and the server pretty much comes to a stop. 36 requests like that at the same time can drive server load well past 30 or 40.
Worse, get someone with a caching remote fetch bot that gets all of the archive pages, and essentially you get server shutdown.
Why have there been 5 or 6 complete interface redo's, and yet basic queries are still horrible?