Ambassador Tablicek

#PostgreSQL #федичитальня #postgresql15

Все мои посты здесь собираются в этот гигапост в качестве сохранения от удаления. В бонус - небольшие редактирования, когда через неделю-две читаешь, что написал - диву даёшься, как так можно плохо писать.
https://strizhechenko.github.io/2023/02/18/postgres.html

Заметил в книжке пример о том, что можно делать хитрый WHERE по сочетанию параметров через VALUES и скобочки.

WHERE (x, y) IN (VALUES ('a', 'b'), ('c', 'd'))

Интересно насколько эффективно оно работает в сравнении с пачкой OR? Длина собранного запроса короче, больше шансы уложиться в один TCP PUSH/ACK пакет при средних размерах таких списков, это понятно. Читаемость - хз. А с точки зрения вычислений хз вдвойне. Композитным индексам, наверное, пофигу.

Заметки по книге PostgreSQL 15 изнутри

WAL

Блог Олега Стрижеченко
Jul 3, 2023 at 11:49amWeb
Show threadAmbassador TablicekJul 3, 2023

Если COALESCE для обыгрывания NULL-значений делать, наверное, вообще вся работа индексов по одному месту пойдёт. В 15й версии для этого даже специальную опцию сделали, но, уверен, под капотом там тот же COALESCE будет. А на работе 14, так что я даже не пробовал.

Это такая скучная херня, дрочиться с работающими SQL'никами ради 5-10% выигрыша производительности, но так успокаивает и умиротворяет, радует, когда в итоге получается, когда нет срочных задач, само собой.

Show threadAmbassador TablicekJul 3, 2023
Увидел что начиная с 12 версии можно пилить свои методы табличного доступа и загорелся идеей fine tuned custom приколов заточенных под специфику данных. Теоретически можно накостылить эдакий clickhouse внутри постгреса. Вот на старом проекте одну задачу можно было бы изящно утащить в постгрю, сделать бешенно быстрый парсинг и CDC. Вышло бы охуенно, очень смешно и не совсем понятно.
Show threadAmbassador TablicekJul 5, 2023
Обнаружил что #индексы не могут использоваться в случае если в запросе к полю применяется функция. Но и для этого есть обходной путь — можно индекс строить по этой же функции, а не по самому столбцу. При добавлении строки — правильно, будет вычисляться эта функция и результат запишется в индекс. Но при поиске, оно, скорее всего будет перепроверяться (re-check cond в explain). Ну, хоть не всю таблицу сканировать, конечно, но может это вычисление как-то можно избежать.
Show threadAmbassador TablicekJul 6, 2023

=> SELECT amname, amhandler FROM pg_am WHERE amtype = 'i';
amname | amhandler
−−−−−−−−+−−−−−−−−−−−−−
btree | bthandler
hash | hashhandler
gist | gisthandler
gin | ginhandler
spgist | spghandler
brin | brinhandler
(6 rows)

На этом книжку можно закрывать и в постгресе дальше не копаться. ОКР? Ну вот треснула бы жопа сделать btreehandler и spgisthandler?

Show threadAmbassador TablicekJul 7, 2023
Почитал про методы доступа, которые Index Scan, Index Only Scan, Bitmap Scan, Seq Scan. Общий вывод, который делаю - на этапе проектирования системы крайне желательно иметь представление какого рода запросы в ней будут преобладать и оптимизировать систему под них. К примеру OLAP-нагрузка с комбинированными запросами может перекашиваться в сторону Bitmap Scan, использующий несколько независимых индексов на отдельные колонки — такой системе нужно мало воркеров, но с большим work_mem.
Show threadAmbassador TablicekJul 7, 2023

... это позволит избегать загрубления битмапов и лишнего recheck cond, что сократит I/O.

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

Show threadAmbassador TablicekJul 7, 2023

... особенно если таблица широкая.

Если же таблица небольшая (10-20 строк, 3-5 столбцов, например, но точное число сильно зависит от, надо бы поиграться), редко меняется, но часто читается, может оказаться выгодным вообще не создавать для неё индексов – при частом чтении, она будет лежать целиком в буферном кэше, не будет вытесняться и расходы на последователное чтение будут ничтожны.

Show threadAmbassador TablicekJul 7, 2023

Про ширину таблиц и индексы. Я вот всё ещё не могу для себя сформулировать, насколько плохим может оказаться влияние created_at и modified_at колнок, которые частенько включают в базовую модель ORM. Обычно весь их смысл - служебный, для переливки в другие БД, витрины итд. Но чтобы по этим колонкам выгребать данные эффективно - нужны идексы по ним. Хоп - и вставка подорожала. В приложении семантику на них завязать тоже редко получается — обычно важна дата изменения конкретного столбца.

Мнения?

Show threadAmbassador TablicekJul 8, 2023

Про статистику.

Для оценки селективности и кардинальности используется статистика. Она состоит из n_distinct - число уникальных значений, MCV + MCF - самых популярных значений и их частоты (при больших разбросах там аж гистограмы подрубаются с бакетами), доли null значений, средний размер полей вариативной длины, корелляцию — насколько совпадает физическое расположение строк в таблице с их порядком выдачи из индекса, чем меньше, тем хуже, но в случае с SSD не ясно, _насколько_ хуже, типа проблема ж не в рандомном чтении без префетчей, а в (перепро)чтении лишних страниц, как я понял.

#Статистика - бро твоего планировщика и позволяет автоматически дрочить диск оптимальным способом, если она, конечно, есть и правдива. Статистика собирается полуслучайным образом, типа берём рандомные то ли 300, то ли 30 000 страниц, берём из них рандомные 30 000 (?) строк и АНАЛИЗИРУЕМ. Вакуум такой стоит, аж буферные кэши вытесняются.

Если честно, я порой охреневаю, какой это ебовый овер(?)килл и сколько всего порождает простая запись в табличку. И всё это нужно ведь прочитать, обсчитать, проанализировать перед непосредственным запуском читающего запроса. Вроде у планировщика есть кэш запросов, но ведь в него ещё и попадать надо. А ещё к нему надо обратиться перед планированием, вдруг есть чо, а если нет - это ж ещё одни накладные расходы. Зато SQL простой и декларативный язык, невероятно ведь круто описать что ты хочешь получить, не заморачиваясь вопросом "как".

Show threadAle Toshkentda!Jul 7, 2023
@strizhechenko Добавить без индексов про запас, если будут провисания — навесить индексы пост-фактум или подумать над архитектурой ещё
Show threadsattelliteJul 6, 2023
@strizhechenko и вот самый сложный продукт с открытым кодом, в котором патчи принимаются годами, чтобы соответствовать всем нормам. И на тебе, сэкономили на символах и теперь не вписывается в общий вид. А может они с них начинали и тогда общего видения не было?
Show threadsharikovJul 6, 2023
@strizhechenko там еще кстати не по всем функциям можно строить #индексы, функция должна быть детерменирована, а в случае с самописной функцией, так еще и промаркирована IMMUTABLE
Show threadRomanJul 3, 2023
@strizhechenko а где поглядеть такое?