Хм, а если построен индекс A+B, но поиск необходим только по A Тут только с помощью like можно сделать или есть ​более оптимальные способы?

ну ты можешь построить сразу индекс по A+B и тогда можно делать запрос по колонке A смело

но по B отдельно не сможешь короче сможешь делать запрос по A и по А + В

даже на чистом равенстве без like это сработает?

да ?

ща скину один ресурс классный

http://use-the-index-luke.com/

читается за пару часов

спасибо! а если просто использовать два индекса A и B, насколько это хуже чем A+B? То есть, в первом случае он найдет нужно место в индексе A, как он затем переходит к B? логически немного не понимаю

верно сначала найдет все в А потом будет мержить с тем что нашел в B

могу наврать не помню сейчас какой будет query plan

но в этой книге все расписано будет как работает индекс

и насколько это дороже чем A+B выходит? Процесс мержа ведь подразумевает сравнение за линию O(N) ?

но в этой книге все расписано будет как работает индекс

сейчас возьмусь почитать, большое благодарствие)

http://use-the-index-luke.com/sql/where-clause/the-equals-operator/concatenated-keys

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

глянем?

спасибо! а если просто использовать два индекса A и B, насколько это хуже чем A+B? То есть, в первом случае он найдет нужно место в индексе A, как он затем переходит к B? логически немного не понимаю

Это будет bitmap scan, заметно хуже комбинированного индекса. Но даст делать запросы во всех 3х комбинациях.

ежели сделать индексы для всех трех (двух)вариантов — это будет отпимальным для поиска? (не смотря на то, что ​это гораздо больше весит и тд) интересует исключительно скорость поиска точнее A+B для поиска по A и A+B и отдельно для B

Если надо искать по а+б и б (если правильно понял), то стоит сделать именно а+б и б. Поиск по а здесь достается бесплатно.

Если а не надо - достаточно б+а

ок, спасибо!

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

потому что если конкатенация, то по А отдельно уже не поискать

и если что, если мультиколумн, то по Б можно искать индексом, но хуево

Привет, есть куча вопросов касательно структуры бд. Есть система, которая принимает заказы, которые оцениваются согласно выбранному тарифу. В системе есть три типа пользователей: 1. Администратор. Пользуется веб-версией (возможно мобильное приложение в будущем). Авторизуется по username + password. Есть несколько уровней доступа к данным (мл.админ, ст.админ, ...). 2. Клиент. Пользуется мобильным приложением для клиентов. Авторизуется по phone + sms-code. Может оставлять заказы, отменять заказы. Просматривает свою историю заказов и тарифы. 3. Исполнитель. Пользуется мобильным приложением для исполнителей. Авторизуется по phone + sms-code. Может видеть активные заказы, закреплять за собой и изменять их статус. Просматривает свою историю заказов. В1. Как организовать таблицы пользователей? Сейчас есть три таблицы на каждый вид пользователей. С одной стороны - ок, т.к. семантически это абсолютно разные сущности. С другой - у Клиента и Исполнителя есть общие поля (номер, например). Но тут вот что - если исполнитель меняет номер, то номер клиента меняться не должен. Собственно, можно ли улучшить текущую структуру? В1.1. Как в связи со структурой организовать REST (авторизацию)? Сейчас есть отдельные endpoint'ы для каждого пользователя: admins/auth, customers/auth, employees/auth. Не могу в голове состыковать, как вести контроль доступа к ресурсам. Есть вариант прикладывать к токену какие-нибудь роли или привилегии. В2. Как сохранять изменения цен тарифов? Сейчас есть просто тариф с какими-то цифрами. Но если менять цену на тариф, то заказ, который связан с этим тарифом, начнет показывать другую стоимость - не клево. Мое решения - разделить на Tariff и TariffInfo, где TariffInfo содержит в себе все то, что может меняться и влиять на показатели, а Tariff - просто своеобразный якорь для цен. При редактировании данных создается копия TariffInfo с обновлением данных, а старая помечается значением canceled_at. На одном форуме предложили все те же действия проводить с Tariff, а чтобы не потерять историю изменений,вести два id - ID и UUID. Но при таком решении я вижу несколько недостатков: 1. Плодятся строки в таблице тарифов 2. Каждая копия тарифа - это отдельная сущность, с которой нельзя массово что-то сделать. В случае с TariffInfo любая массовая (отключить или еще что) операция применяется к Tariff.

Изменение цен - slowly change dimension (SCD), тип подберете сами

По поводу доступа, можно завести битные роли и рулить на уровне, на котором вам нужно просто добавляя проверку, в совокупе с scd сможете рулить историей в рамках ролей

Но это программный вариант, на уровне БД возможно что-то есть удобнее/интереснее

Так, но по сути какие могут быть роли, если клиент и исполнитель - абсолютно разные сущности?

Это скорее свойство сущности

Хм, можно подробнее?

и если что, если мультиколумн, то по Б можно искать индексом, но хуево

речь про мультиколумн а как искать по B?

и что значит "хуево" в вашей терминологии?

медленно

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

в то время как суммарное кол-во строк — несколько миллиардов на текущий момент

тут оптимальнее сделать партишонинг и делать индексы после заливки патриции

где можно про это почитать в рамках postgresql ?

https://habrahabr.ru/post/273933/ пойдет?

https://habrahabr.ru/post/273933/ пойдет?

Пойдет, но сейчас есть более вкусные вещи. В pg 10 есть встроенное секцинирование

>Индексы должны создаваться для каждой секции независимо

Как это влияет на скорость селекта? что-то подказывает мне что при 500 партиций скорость соответственно упадет

https://postgrespro.ru/docs/postgrespro/10/ddl-partitioning речь об этой штуке

Я насчитал следующее: 1. При единой таблице поиск по индексу O(log(N)) 2. При K таблицах с равным кол-вом строк поиск по индексам O(K*log(N/K)) Итого разница: K*log(N/K)/log(N) = K*log_N(N/K)=K(1-log_N(K)) т.е. при моих данных просадка где-то в 400 раз. или я не прав?

если тестовые данные: 700млн строк и 600 партиций

Я насчитал следующее: 1. При единой таблице поиск по индексу O(log(N)) 2. При K таблицах с равным кол-вом строк поиск по индексам O(K*log(N/K)) Итого разница: K*log(N/K)/log(N) = K*log_N(N/K)=K(1-log_N(K)) т.е. при моих данных просадка где-то в 400 раз. или я не прав?

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

>Индексы должны создаваться для каждой секции независимо

Вот да... это влияет еще на использование уникальных индексов и внешних ключей...

Много ограничений.

Я насчитал следующее: 1. При единой таблице поиск по индексу O(log(N)) 2. При K таблицах с равным кол-вом строк поиск по индексам O(K*log(N/K)) Итого разница: K*log(N/K)/log(N) = K*log_N(N/K)=K(1-log_N(K)) т.е. при моих данных просадка где-то в 400 раз. или я не прав?

Смотря по какому индексу и как партиционировано. Если первое поле индекса —- ключ партиционирования, то ненужные секции могут быть отброшены на этапе планирования запроса (что, соотвественно, замедляет планирование). Кроме того, subplan по каждой секции может быть при этом разным.

Я насчитал следующее: 1. При единой таблице поиск по индексу O(log(N)) 2. При K таблицах с равным кол-вом строк поиск по индексам O(K*log(N/K)) Итого разница: K*log(N/K)/log(N) = K*log_N(N/K)=K(1-log_N(K)) т.е. при моих данных просадка где-то в 400 раз. или я не прав?

если партиционирование сделано по произвольному рандомному значению - так

если нет, то в К-1 партициях сложность будет ~О(1) - сверить границы, плюнуть и пойти дальше

https://postgrespro.ru/docs/postgrespro/10/ddl-partitioning речь об этой штуке

Посмотри на pg_pathman, он возвращает все в лоно логарифма. Ну и, в 11м постгрессе тоже логарифм будет.

Я насчитал следующее: 1. При единой таблице поиск по индексу O(log(N)) 2. При K таблицах с равным кол-вом строк поиск по индексам O(K*log(N/K)) Итого разница: K*log(N/K)/log(N) = K*log_N(N/K)=K(1-log_N(K)) т.е. при моих данных просадка где-то в 400 раз. или я не прав?

Это если будет сканирование по всем партициям?

Вот да... это влияет еще на использование уникальных индексов и внешних ключей...

В будущих версиях pg_pathman обещали внешние ключи.

В будущих версиях pg_pathman обещали внешние ключи.

Думаю без глобальных индексов это невозможно

Думаю без глобальных индексов это невозможно

Посмотрим. Это же не оракла.

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

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

пока придумал по тупому: 0. Имеется индексированная таблица в продакшене 1. При заливке новой большой пачки данных копируем таблицу с помощью create table as (индексы не копируем) в таблицу _work 3. Заливаем искомую пачку данных в таблицу _work 4. Строим индексы для таблицы _work 5. Меняем их местами с продакшн таблицей, а старую удаляем Итого: юзер не заметит перерасчета индексов, а объема потребуется всего в ≈два раза больше размера таблицы на пике процесса

Посмотри на pg_pathman, он возвращает все в лоно логарифма. Ну и, в 11м постгрессе тоже логарифм будет.

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

пока придумал по тупому: 0. Имеется индексированная таблица в продакшене 1. При заливке новой большой пачки данных копируем таблицу с помощью create table as (индексы не копируем) в таблицу _work 3. Заливаем искомую пачку данных в таблицу _work 4. Строим индексы для таблицы _work 5. Меняем их местами с продакшн таблицей, а старую удаляем Итого: юзер не заметит перерасчета индексов, а объема потребуется всего в ≈два раза больше размера таблицы на пике процесса

сделал замеры: копируется табличка где-то за час с лишним вставляется пачка от 5 до 60 минут (смотря какой размер) индексы строятся достаточно много часов Но за ночь можно провернуть такую процедуру вполне И даже если кто-то ночью захочет заюзать систему — не будет в обиде т.к. пока будет использовать старую версию таблицы

Вообще, была бы возможность работать на внутреннем уровне СУБД то думаю нашелся бы способ хранить несколько версий индексов для одной таблицы (один фиг индексы хранят указатели на строку, поэтому добавление новых данных не испортило бы старый индекс за исключением того что та не актуальная информация). Тогда бы в пике процесса не пришлось копировать таблицу — на лицо выигрыш по времени (затраты на копирование) и по памяти (завтраты на копию)

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

Делай пулл реквест)

@ParadoxTelegram, а тебе надо побыстрее вставить и поэтому ты индексы потом хочешь строить?

насколько я понял юзеру ты данные покажешь вообще на следующий день может ты просто загрузишь батч от юзера в темп, скажешь ему что ок и будешь спокойно вставлять в рабочую таблицу нафига ребилдить индексы на рабочей таблице, я не понимаю

Евгений, вы имеете в виду почему бы не делать выставку пачки прямо в таблицу с индексами? Боюсь это очень дорогая операция при моих объемах

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

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

логарифм получается, если условие запроса затрагивает ключ партицирования и срабатывает partition pruning

я бы понял если там гист с мбр, который периодически требует

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

тут имеется в ввиду чтобы это еще было незаметно для юзера разве вставка в рабочую таблицу не замедлит работу с обращениями к этой таблице?

можно, конечно, сделать вставку очень медленной

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

логарифм получается, если условие запроса затрагивает ключ партицирования и срабатывает partition pruning

не шибко то оно затрагивает в моём случае: партицирование доступно только по идентификатору очередной пачки данных

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

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

ничотак индексов у тебя

индексов по меньшей мере 7

:)

и всё это на одном сервере живет?