Generally speaking WordPress is fast. And this is the case even with large websites with tens of thousands of posts. Mostly, speed depends on the theme and than on the plugins. But in many cases the slowest part is the WordPress core. Here is how to speed it up.
SQL execution background
Most of the SQL queries WordPress uses are fast. They will get slower with more complex filters added to the query, but such queries are rare. In most cases you can’t do much to speed them up. Some of the operations WordPress runs will execute more than one query: filtering posts by category or tag, metadata queries. This is done because 2 simpler queries are usually faster than one query using joins.
When number of posts rises, queries will tend to slow down. If the query relies on columns that are indexed (post ID for instance), speed will remain the same and number of posts will not affect speed. But, in some cases queries will rely on columns that are not indexed or even if they are indexed, index is not having effect. Such columns are ususally date/time columns.
While main SQL queries will usually remain fast, even with default configuration of WordPress, default theme and usual plugins, there are SQL queries in the WordPress core that are very slow. And most of the are not needed at all. This time we will focus on SQL queries that are part of the page header. Using filters WordPress core adds several functions to the header that are used to generate additional tags. These tags are adding additional URL’s inside the website: previous post, next post, parent post (for hierarchical post types), first post in category. These filters can generate 5 to 10 SQL queries, and in some cases they can take longer to execute than all the other SQL queries on the page combined!