я могу fk на UNIQUE INDEX ? Мы же можем только на constraint с условием, что не составной. Или я буквально понял?)

А в каком случае порядок полей в b-tree влияет на что-то?

И правильно ли я понимаю, что в таких статьях http://c3gdlk.ru/blog/bazy-dannyh/poryadok-polej-v-sostavnom-indekse.html информация не верна? Ну, если порядок не влияет.

Там написано вот так "При использовании составного индекса важен его порядок и первым должно идти поле с самой высокой селективностью, т.е. то поле, уникальных значений которого в базе больше."

Да, можете.

понял, спасибо, в целом, наверное для такой выборки достаточно индекса по user_id ?
А если поменять в запросе user_id на age ? ( целое число ) и представим, что status — тоже целое число ?
и запрос такой where age > $1 and status > $2
вот, будет ли верно тогда предыдущее утверждение про селективность ?
Ещё чуть выше вы писали, что порядок полей при выборке через равенство не имеет значения — предполагаю, что может иметь ( зависит от реализации индекса ), если значением в ноде дерева является конкантенацией двух полей, то наверное да, а как в постгрес ?

При выборках по другим условиям (не по равенству, или не по всем полям).
Вообще, лучше понимать, как это работает — вопросов будет гораздо меньше. ;)

А если этот индекс составной, то нет?

Да, это безграмотная чушь.

> Алгоритмы работы Б Дерева потянут на несколько больших научных статей

Ну да, конечно. Только вот даже студенты "осиливают" базовые за одно или два занятия.

> И, чем больше мы будем добавлять элементов со значением 577, тем сильнее будет дерево ветвится справа.

И это бред. b-tree всегда сбалансировано. Ну и отсюда следует всё прочее.

Тогда понял, спасибо

Похоже это потому, что external_id_parent может быть null. И posgres не может найти соответствие для строки из за MATCH SIMPLE. MATCH PARTIAL возможно мог бы помочь, но он еще не реализован. Как еще можно решить кейс с таблицей, которая замыкается сама на себя?

Допустим есть таблица Категории. В ней сеть две колонки id и id_parent. Нужно чтобы при удалении  родителя, удалялись все дочернии категории.

> в целом, наверное для такой выборки достаточно индекса по user_id

Для выборки по двум полям индекс по этим двум полям почти всегда наиболее эффективен.

> и запрос такой where age > $1 and status > $2

А вот в этом случае селективность уже имеет значение.
Поиск в любом случае будет выполняться по одному из этих полей, а по другому — только фильтрация.
Поэтому индекс в этом случае стоит строить так, чтобы под условие column > 'constant' попадало поменьше записей.

> предполагаю, что может иметь ( зависит от реализации индекса ), если значением в ноде дерева является конкантенацией двух полей, то наверное да, а как в постгрес ?

Не "предполагайте", а поймите, как работает используемая структура данных. Что за "мифы и легенды" в программировании?! ;)

И тоже да (если FK на весь этот индекс)... Почему Вы просто не пробуете, кстати? ;)

да я просто где-то прочитал что на индексы можно в MySQL, а постгрес - нет. + у меня orm, где она ожидает либо не составной первичный ключ, либо уникальное поле.

Разница, насколько я знаю, в том, что в MySQL (было?) можно делать FK на неуникальные индексы (мне как-то показали, я сильно удивился). ;)
Но вообще я в нём не разбираюсь, если что.

Вы бы текстом показали \d. Потому что, насколько я помню, всё это просто работает.

external_id_parent будет null для корня

Да, я понял. Ну и на первый взгляд всё работает, нет?
https://dbfiddle.uk/?rdbms=postgres_13&fiddle=5a161897d9f6fd581d01361e96385e09

Прошу прощения, смогу посмотреть позже

Извините.Не  подскажете где я допускаю ошибку.в datetime  дата с типом данных date)