Understanding indexes in PostgreSQL

In this section, you will learn the fundamental concept of indexing. As already mentioned, indexes are highly important and their importance can hardly be overestimated. Therefore, it is important for you to understand what indexes are good for and how they can be used efficiently without harming performance. To illustrate indexes, the first thing to do is to introduce the concept of so-called execution plans.

Execution plans are a component vital to understanding PostgreSQL's performance as a whole. They are your window to the inner workings of the database system. It is important to understand execution plans and indexes.

Using a simple index

The basic idea of SQL is that you can write a query and the database will decide on the best strategy to execute the request. Deciding on the best strategy is the task of the so-called optimizer. The idea behind the optimizer is to figure out which query is expected to impose the lowest costs on the system, and go...