Once upon a time I have stumbled into a performance issue with one of the queries in the app I have been working on.

Here I want to share my findings during the issue investigation.

Let’s start simple. Imagine that we have a database with such a table:

CREATE TABLE sample(
    id  uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    some_name VARCHAR(200) NOT NULL,
    some_time TIMESTAMP NOT NULL,
    some_status VARCHAR(20) NOT NULL,
    some_number INTEGER NOT NULL
);

The table has about 6 million rows, and we need to query it fairly often. We were using the PostgresSQL as our database, so we were expecting that everything should work just fine on such scale. Since we know that it will be read intensively, we have put an index on it: