Indexes in PostgreSQL

All indexes in PostgreSQL are secondary indexes, meaning that each index is stored separately from the table's main data area (the heap).
This means that in an ordinary index scan, each row retrieval requires fetching data from both the index and the heap.

To solve this performance problem, PostgreSQL supports index-only scans, which can answer queries from an index alone without any heap access.
The basic idea is to return values directly out of each index entry instead of consulting the associated heap entry.

There are two fundamental restrictions on when this method can be used:

1. The index type must support index-only scans. B-tree indexes always do.

2. The query must reference only columns stored in the index.

Example

Let's take a look at this table called students.

create table students (
  id serial primary key,
  g int,
  firstname text,
  lastname text,
  middlename text,
  address text,
  bio text,
  dob date
)

Let's generate a big amount of data for this table.

insert into
  students (
    g,
    firstname,
    lastname,
    middlename,
    address,
    bio,
    dob
  )
select
  random() * 100,
  substring(md5(random() :: text), 0, floor(random() * 31) :: int),
  substring(md5(random() :: text), 0, floor(random() * 31) :: int),
  substring(md5(random() :: text), 0, floor(random() * 31) :: int),
  substring(md5(random() :: text), 0, floor(random() * 31) :: int),
  substring(md5(random() :: text), 0, floor(random() * 31) :: int),
  now()
from
  generate_series(0, 1000000);

No Index

If we select the last name of students whose grade is equal to a specific grade (12 for example) and analyze the query execution, we get this:

explain analyze select lastname from students where g = 12;

Because there is no index, PostgreSQL did a sequential scan of the entire table to get the results.

It also did it in parallel so it was a bit faster.

If we want to make this query faster, we could add an index on g.

Normal index on a column

create index g_idx on students(g);

If we execute the query again, we get this result:

We can see that the execution time went down from 66 ms to 35 ms.

Impressive. But can we do more? Yes!

A covering index

This query can execute faster if it performed an index-only scan. i.e, if the lastname field was stored in the index.

How can we do that?
Let's drop the index

drop index g_idx;

And create a new index on g that also includes lastname

create index g_idx on students(g) include (lastname);

Now if we execute the query again, we get this result:

We can see that the database performed an Index Only scan, so it didn't have to return to the heap and fetch the rows matched because lastname is already stored in the index.
The execution time went down from 35 ms to 3 ms!

This is called a covering index.
A covering index is an index specifically designed to include the columns needed by a particular type of query that you run frequently.

Note about non-key payload columns

However, it's wise to be conservative about adding non-key payload columns to an index, especially wide columns. If an index tuple exceeds the maximum size allowed for the index type, data insertion will fail. In any case, non-key columns duplicate data from the index's table and bloat the size of the index, thus potentially slowing searches.

Now you might ask, what is the difference between the last index we created and a multicolumn index? We can discuss that in the next article!

Resources

• https://www.udemy.com/course/database-engines-crash-course/

• https://www.postgresql.org/docs/current/indexes-index-only-scans.html