У меня были остаточные воспоминания о том, что в 12 и в 13 версии индексы стали какие-то более поджарые и вроде бы как где-то слышал, что чтобы эти новые алгоритмы начали действовать индексы надо пересоздать...

13-й версии появилась дедупликация, часть индексов станет меньше.

У меня были остаточные воспоминания о том, что в 12 и в 13 версии индексы стали какие-то более поджарые и вроде бы как где-то слышал, что чтобы эти новые алгоритмы начали действовать индексы надо пересоздать...

в 12 сменился формат (новая версия nbtree), но размер не поменялся. для перехода на новую версию надо переиндексировать.
если этого не сдеалать — ничего не поменяется, всё будет работать

приветствую. не могу разгадать загадку, буду благодарен за помощь
итак, есть два запроса к одной и той же таблице, оба запроса делают select ... order by id asc for update. в результате одновременного выполнения запросов получаю дэдлок.
отличаются эти запросы условием. в одном where id in (...), во втором where user_id in (...)
explain выдает на первый запрос вот что

QUERY PLAN
LockRows  (cost=0.42..25.35 rows=3 width=26) (actual time=4.446..5.036 rows=3 loops=1)
  ->  Index Scan using availability_availabilityqueue_pkey on availability_availabilityqueue  (cost=0.42..25.32 rows=3 width=26) (actual time=4.401..4.974 rows=3 loops=1)
        Index Cond: (id = ANY ('{fffff3e0-0c09-40cb-9cc0-29d79f557fa4,ffffb9f7-d2f9-4695-b448-dfee8b904bd7,ffffb151-a171-4803-a08d-fe618c9f9b26}'::uuid[]))
Planning Time: 0.128 ms
Execution Time: 5.057 ms

кусок эксплейна второго запроса

QUERY PLAN
LockRows  (cost=5638.39..5666.69 rows=2264 width=26) (actual time=5.034..5.036 rows=0 loops=1)
  ->  Sort  (cost=5638.39..5644.05 rows=2264 width=26) (actual time=5.033..5.035 rows=0 loops=1)
        Sort Key: id
        Sort Method: quicksort  Memory: 25kB
...

в обычной ситуации причиной дедлока является отсутствие сортировки (ну или различные сортировки). тут же сортировка указана. но у одного из запросов отсутствует в explain. это несколько смущает...

а то есть если мы перешли на 12 версию, то можно запустить переиндексацию, и тогда в дальшейшем при переходе на 13 версию делать ничего не нужно будет?

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

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

Потому что порядок rows в таблице не соответствует их порядку в индексе, например.

Следовательно причина децствительно в том, что по факту order by не выполняется?

ну да, со временем включится дедупликация и они усохнут. если хотите сразу, то надо переиндексировать ещё раз

Он выполняется, но по id, а не по положению записи в таблице, понимаете?

Очень примерно, наверное :)
Ладно, допустим. Как можно решить данную проблему, при условии сортировки именно по id? (uuid)
Других полей для сортировки, увы, нет. Добавлять автоинкременты тоже не хотелось бы

> Как можно решить данную проблему, при условии сортировки именно по id? (uuid)

Ну а это точно проблема? Т.е. производительность уже страдает?

а не подскажете, как сделать where column not in (kakaya_to_peremennaya) если у переменной тип ченибудь типа numeric[]?

Страдает приложение из-за наличия дедлоков.

В смысле "страдает"?
Т.е. они происходят настолько часто, что производительность неприемлемо низка (иначе можно просто игнорировать эту "проблему")?

дедлоки происходят достаточно часто, чтобы падали автотесты и куашники на это сетовали.
дедлок в двух процессах, один - хттп-ручка, второй - в демоне (celery, если это важно)
ретраить http-ручки не вариант. хотелось бы сделать так, чтобы дедлоков не было. есть, конечно, вариант с advisory lock, но производительность после этого упадет достаточно, чтобы сетовали уже не куашники, а бизнес

> ретраить http-ручки не вариант

Почему это?! Если там этого нет, они broken by design.

> но производительность после этого упадет достаточно

А Вы думаете, что производительность от других вариантов avoidance в этой ситуации не упадёт? ;(
Можете показать \d таблицы и запросы, конечно...