Как правильно использовать индексы PostgreSQL?

Question

[Ne7Le4Der]

Есть две таблицы A и 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

Я думаю не привязываясь к языку программирования суть ясна - в таблице A поля id и status, в таблице B поля id, time, value и a_id

A has many B

В таблице B порядка 5.000.000 (размер ~1гб)

Использую ORM, но даже простой

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

Выполняется 0.3c - 1c в зависимости от количества строк в ответе. Так же очень медленно выполняется INSERT. Почитав с какими объёмами работают другие пользователи понял, что что-то явно не так.

Вопрос: ускоряет ли index операцию ORDER BY, и в чем вообще может быть проблема? Настройки, железо, или я неправильно использую index?

Comments

[galaxy]

Выполняется 0.3c - 1c в зависимости от количества строк в ответ

И сколько строк в ответе?

А вообще, план надо смотреть.

Answer 1

[Сергей Томулевич]

1. Для того что бы понять, работает индек или нет, а так же как он вообще работает в консоле базы данных следует выполнить EXPLAIN ANALYZE, в Вашем случае:

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

2. Скорее всего в Вашем случае скорость запроса зависит не сколько от работы индекса, сколько от количества записей возвращаемых запросом для разных a_id. Почему нет LIMIT ?

Comments

[Ne7Le4Der]

1)
Почитал по поводу EXPLAIN. Исходя из того что я начитал и судя по результату - индекс не используется (поправьте, пожалуйста, если я не прав).

2) Это точки временного ряда (x,y), которые запрашиваются по одному конкретному графику. Возвращаемое количество небольшое (1000-10000), поэтому и не использую LIMIT, т.к. нужен ряд целиком.

На скриншоте который приложил 8399 точек во временном ряду

[kalapanga]

А будет разница во временах, если в запросе поменять DESC на ASC?

[Ne7Le4Der]

kalapanga,

+- то же самое

[Сергей Горностаев]

Ne7Le4Der, не используется. Хорошо бы глянуть DDL, который ваша ORM сгенерировала.

[Сергей Томулевич]

Ne7Le4Der,

\dt+ candies

Но вообще, я никогда не надеялся на ORM в части генерации структуры, и Вам не советую

CREATE INDEX CONCURRENTLY candies_idx0 ON candies USING btree(job_id, "time" DESC);

[Сергей Томулевич]

kalapanga,

Правило сортировки в индексе в данном случае не влияет, так как планировщику все же дешевле сначала отфильтровать по job_id из-за его достаточной "селективности" что не сложно увидеть по планам запросов.
Если бы rows которые реально выбраны и "Rows removed by filter" были бы хотя бы в одних порядках, то можно было бы говорить об использовании индекса исключительно по "time" в принципе.

Кстати да, статистику по таблице желательно обновить:

Ne7Le4Der,

Еще вот эту команду сделать (она не блокирующая)

VACUUM ANALYZE candies;

Он в плане планирует выборку 2К строк, а реально выбирает 8К, что говорит о том, что статистика не совсем актуальна.

[Сергей Томулевич]

Сергей Томулевич,

Про план ошибся, просмотрел loops=3

Впрочем, VACUUM ANALYZE никогда лишним не бывает.