In the previous section, you have learned how to use EXPLAIN to understand how PostgreSQL is going to execute a query; it is now time to use EXPLAIN in action to tune some slow queries and improve performance.
This section will show you some basic concepts of the day-to-day usage of EXPLAIN as a powerful tool to determine where and how to instrument PostgreSQL in doing faster data access. Of course, query tuning is a very complex subject and often requires repeated trial-based optimization, so the aim of this section is not to provide you with true knowledge about query tuning but rather a basic understanding of how to improve your own database and queries.
Sometimes, tuning a query involves simply rewriting it a way that is more comfortable – or better, more comprehensible –to PostgreSQL, but most often, that means using an appropriate index to speed up access to the underlying data.
Let's start with a simple example: we want to extract all...