Index-only scans

Indexes have already been described in Chapter 04, PostgreSQL Advanced Building Blocks. Simply speaking, indexes work like a glossary at the end of a book. When searching for a keyword in a book, to make it faster one can look it up in the glossary and then go to the page specified. The glossary is alphabetically organized; that's why searching in it is fast. However, when it is necessary just to find out if a keyword exists in the book, there is no need to go to the page. Just looking in the glossary is enough.

PostgreSQL can do the same. If all the information that is needed for a query is contained in the index, the database will not perform the scan on the table data and only use the index. This is called an index-only scan.

To demonstrate how it works, let's create an index for the table dwh.access_log_not_partitioned, as follows:

CREATE INDEX on dwh.access_log_not_partitioned (ts, status_code);

Now, suppose we want to find out when the first HTTP request that resulted...