Знай свой compound: Оптимизация запросов через составные индексы в PostgreSQL

CREATE TABLE some_table (
id UUID PRIMARY KEY,
foreign_id UUID,
created_at TIMESTAMP WITH TIMEZONE
)

Есть индекс:

CREATE INDEX some_idx ON some_table (foreign_id, created_at);

Какой запрос (и примерно насколько) будет быстрее:

SELECT foreign_id, MAX(created_at)
FROM some_table
WHERE created_at > now() - interval '1 minute'
GROUP BY foreign_id;

ИЛИ

SELECT foreign_id, MAX(created_at)
FROM some_table
WHERE created_at > now() - interval '1 minute'
AND foreign_id IN (SELECT id FROM foreign_table)
GROUP BY foreign_id;

Голосуйте 👍 за первый и 👎 за второй

И не ссыте ошибиться, круто видеть что люди пытаются

Ответ через

3

2

1

Вы думали я дам ответ? Хер там, я расскажу как это работает, а ответ вы сами должны понять

Короче, compound индексы хранятся базой слева направо, то есть если индекс (a, b, c) вы ищите по колонке (a) или (a, b), то индекс будет работать, но если вы пропустили какую-то из колонок то индекс не будет использован

ВАЖНО в каждой БД свое устройство компаунд индексов, поэтому проверяйте по своей доке

Почему это надо знать: потому что человек не знающий пошел бы создавать новый индекс, который уменьшил бы скорость всех записей, а знающий решил бы моментально, так еще и ускорил бы работу с этим индексом (из-за того, что теперь еще один запрос позволял утилизировать кэш этого индекса)

Бонус вопрос: какой вид индекса будет использован во вложенном SELECT, если id - это primary key?

Здесь правильно использовать Covering index, т.к. нам не требуется сопоставление с таблицей основной по выбранным из индекса строкам, лишняя операция

А правильный ответ, индекс тут нужен только на created_at и 1 запрос нормально отфильтрует.

Вот за что я люблю CockroachDB, что он ещё умеет такие малоэффективные индексы как timestamp разделять по хешу для эффективного поиска, т.к. использовать индекс на timestamp в целом такая себе идея, он размывает индекс до размеров почти 1 к 1 с исходными данными.

P.S. перепроверил, при условии, что в таблице других колонок нет, т.е. она полностью соответствует примеру приведённому, то разницы в индексах не будет между ("created_at") INCLUDE("foreign_id") и ("created_at", "foreign_id").

хотя, в стоимости чтения данных есть разница, которая может при других условиях играть роль, при Covering меньше данных читает

«есть индекс» - имел ввиду что он нужен в других местах, поэтому если что-то править, то можно поменять местами в compound чтобы и старые запросы использовали индекс и вот эта новая без хака

да, про порядок колонок всё верно написал, но это не единственный нюанс)

Про covering? А что он в данной ситуации дает?

при стандартном индексе мы после получения данных из индекса делаем мерж с исходной таблицей выбранных строк, при covering этого не делается, если все колонки для выборки уже в индексе

А разве это не INCLUDES?

да, оно и есть, я там в синтаксисе выше неправильно исправил со STORING)
* поправил

Слушай, а что это за такая красивая визуализация?

это встроенная cockroach борда для distsql (explain analyze(distsql)), вот примерчег красивый

незаменимая вещь для дебага распределённых запросов в проде

«time stamp разделять по хэшу» - можешь рассказать подробнее?

Я сейчас могу ошибаться, но вроде как timestamp будет храниться как дерево где корень это год, потом на несколько узлов ниже месяц, потом неделя, потом час, потом минута, потом секунда, тогда да в результатах дерева будет 1:1, но само дерево намного меньше

То есть, если вдруг узлы - это unix timestamp, я ахуею)

Не понимаю как это можно прооптимизировать

для pg есть вот такая https://explain.depesz.com и такая https://tatiyants.com/pev/#/plans

не, значение даты хранится в микросекундах unix epocs, а индекс по умолчанию, какой там сейчас я не слежу за pg

по кокроучу, вот дока с описанием, это относится к любым монотонно возрастающим значениям, чтобы иметь точку разделения для распределения по нодам.

BRIN там используется индекс https://www.crunchydata.com/blog/postgresql-brin-indexes-big-data-performance-with-minimal-storage

https://www.postgresql.org/docs/current/indexes-types.html#INDEXES-TYPES-BRIN тоже для монотонно возрастающих своя схема хранения

Я с очень давних пор его игнорировал) Почитаю как он устроен, спасибо!