How to properly use PostgreSQL indexes?

Question

PostgreSQL

There are two tables A and B.

A

@Column({
        type: DataType.UUID,
        defaultValue: UUIDV4,
        primaryKey: true,
        unique: true,
        allowNull: false
    })
    id: string;

  @Column({
        type: DataType.INTEGER,
        allowNull: false,
        defaultValue: StatusEnum.PENDING
    })
    status: StatusEnum;

B

@Column({
        type: DataType.UUID,
        defaultValue: UUIDV4,
        primaryKey: true,
        unique: true,
        allowNull: false
    })
    id: string;

    @Index('time')
    @Column({
        type: DataType.DATE,
        allowNull: false
    })
    time: Date;

    @Column({
        type: DataType.FLOAT,
        allowNull: false
    })
    value: number;

    @ForeignKey(() => ATable)
    a_id: string

I think without being tied to the programming language, the essence is clear - in table A the fields id and status, in table B the fields id, time, value and a_id

A has many B

In table B about 5.000.000 (size ~ 1GB) I

use ORM, but even simple

SELECT * FROM b WHERE a_id='123' ORDER BY time DESC;

It takes 0.3c - 1c depending on the number of lines in the response. The INSERT is also very slow. After reading what volumes other users work with, I realized that something is clearly wrong.

Question: does index speed up the ORDER BY operation, and what could be the problem? Settings, hardware, or am I using index incorrectly?

Answer 1

1. In order to understand whether the index works or not, as well as how it generally works in the database console, you should execute EXPLAIN ANALYZE, in your case:

EXPLAIN ANALYZE SELECT * FROM b WHERE a_id='123' ORDER BY time DESC;

2. Most likely, in your case, the speed of the query depends not so much on the operation of the index, but on the number of records returned by the query for different a_id. Why is there no LIMIT ?