@Tzirechnoy, Скажите, а за счет чего получается прирост производительности, если

при всех равных, если в Order By еще и сделать cast к int? Получается, что просто в этом случае планировщик не пробует индекс вообще использовать? И за счет этого в 10 раз быстрее происходит?

EXPLAIN SELECT * FROM test_1 WHERE (jdoc->'field1')::int <= 50000 ORDER BY (jdoc->'field1')::int DESC LIMIT 10;

Скорее всего. ВПрочем, если внимательно прочитать explain -- то будет понятно за счёт чего.

выражения (в том числе касты) вырубают возможность использовать ключ для индексного поиска

Ну кроме where, да, просто без Cast в Order By (просто с jpath, как в изначальном примере) тоже самое получается

Впрочем, у меня на таком запросе и вашых тэстовых данных никаких приростов не видно -- без ::int оно работает честные доли миллисекунды, с ним -- до секунды.

Понял принял, спасибо. А как-то вообще можно по jsonb полю сделать индекс типизированный (к примеру, я точно знаю, что в поле field1 у меня int) и хочу за это получить оптимизации по поиску и сортировке? Исходя из позиции того, что частые запросы будут именно сортировка с выборкой по этому полю. Или все-таки самый очевидный и правильный путь - это нормализовать данные и использовать композитный ключ и/или дополнительные индексы?

лучше и правильнее — нормализовать. а так можно сделать функциональный индекс по выражению

BTREE (((jdoc->'field1')::int)); - что-то вроде этого? Или все-таки custom function?

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

Супер, спасибо. Можно еще один вопрос тоже по схожей теме?

Можно. Так и делаешь, типизацыя это тожэ такая функцыя в итоге. И да, нормализовать данные -- это гораздо более правильный путь. У нас тут всё-таки не документоориентированная база, а реляцыонная.

Если коротко - то есть некие данные многие-ко-многим userId (text) —> segmentId (bigint) При проектировании таблицы было два варианта: 1. Composite Primary Key (userId, segmentId); 2. PK userId —> '{segments_array: []}'; 3. PK userId —> bigint[]; Условия: 1. Сегменты постоянно добавляются/удаляются у пользователей (индекс точно будет расти); 2. Поиск чаще всего будет происходить по UserId и Subset Of Segment Id (subset обычно небольшой относительно общего кол-ва сегментов); 3. Необходим обратный поиск всех userId по segmentId; В итоге пришел к 1 варианту - Composite PK. Нивелировать разреженность индекса планировал за счет fillfactor=90 и autovacuum_vacuum_scale_factor=0 с autovacuum_vacuum_threshold=20000. Однако на конференции (High Load) слышал, что люди зачастую делают JSONB (не структурированные данные) вроде как из-за того, чтобы индекс не получался фрагментированным (насколько я понял). Вот теперь вопрос - выстрелил ли я себе уже в ногу и если да, то какой все-таки вариант лучше выбрать? Пока до production код не дошел - могу менять как угодно и что угодно.

что значит “нивелировать разреженность индекса”? о чём речь?

Возможно выражаюсь просто некорректно. В моем понимании, если я буду каждый раз удалять и добавлять значения в индексе - то будут образовываться пустоты в индексе (тут я немного плаваю ибо понахватался знаний из разных баз и уже немного каша в голове - я из мира MS Sql - больше опыта именно в нем и там было такое явления - что при удалении в индексе образуются "дырки" из-за которых деградация в скорости идет). Может я просто зря вообще об этом беспокоюсь - и все можно не париться и использовать как есть?

от этого эффекта вы избавиться не сможете в любом случае. в 14-й версии индексы сильно лучше себя ведут в плане распухания. в целом — я бы не парился

Для поиска userid по segmentid ещё нужэн индэкс segmentid в таблицэ-связке. А так да, классическое решэние. Есть, конечно, всякие варианты организовать псевдо-column storage... Но в общем на самом деле это редко надо и редко помогает.

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

Да не особенно оно и будет распухать. Ну, то есть не большэ, чем остальная таблица, которую пылесосит vacuum...

Да-да, индексы есть, даже больше - Foreign Keys сознательно не добавлены, т.к. тут консистентность будет решаться за счет клиента

вам тут сильно важно не допускать долгих транзакций. вплоть до принудительного ограничения времени запроса 5 минутами (к примеру). если нужны отчёты долгие и/или аналитика — либо отстающая, либо логическая реплика

Тут, в общем типичный цыкл разработки -- придумать, чем можно проиндэксировать чтобы поиск по новым бизнес-требованиям не сканировал всю базу 100500 раз, потом ещё заставить postgres это использовать, чтобы планировщик всё не ломал и не сканировал 100500 раз, потом выкинуть старые индэксы, поскольку в новых размер индэксов ужэ в 10 раз большэ базы и вставка в 30 раз медленнее, чем была бы в одним primary key.

Ага, я поэтому update батчить собираюсь на клиенте в один поток в идеале - чтобы избежать как раз проблем с блокировками долгими. А на уровне базы/таблицы можно указывать max transaction time (или transaction timeout)? Или это только в клиентском коде?

Ну надеюсь, что по 2 колонкам сильно много бизнес правил не придумают :) Тут скорее сервис для быстрой помощи агрегаций остальных сервисов, больше как персистентный словарь будет использоваться нежели, как источник полных данных. Пока что только как proof of concept - но в идеале должно быть сносным решением.

да-да, целостность за счёт клиента…

дело не в блокировках, точнее я не про них говорил. вам ничего не мешает гнать 10 потоков по 300 записей. и если оно всё не завязано на один общий счёт, к примеру, то будет нормально параллелиться. долгие транзакции тормозят вакуум, что приводит к распузанию таблиц и индексов. вы можете указать statement_timeout на уровне базы, тут не транзакция, а запрос. этот параметр можно выставить пользователю и/или базе конкретной. если нужна гибкость, то можно скрипт в крон повесить, который по pg_stat_activity будет отбирать нужные сессии и pg_terminate_backend()-ить их

Можэт, тогда tarantool взять? Ну, то есть как бы всё выглядит что вам весь codebase постгреса с его традицыями хорошэй поддержки SQL, серверного программирования, типизацыи, надёжно отлажэнного ACID-версионника как-то не очень нужэн... А как тупая молотилка по одному-двум тупым индэксам -- это ну так себе СУБД, небыстрая.

Вот это спасибо! Буду думать как лучше в своем кейсе применить.

Уже в процессе - devops настраивает, но загрузка у них такая, что я в прод гораздо раньше выйду - так что пока что на имеющихся мощностях. Клиент будет на тарантул не сложно переписать (изначально заложено в архитектуру возможная смена DB)