Thread by @komzpa

по просьбам трудящихся, тредик про SQL (postgres/postgis), разберём ваши вопросы и что-нибудь поломаем, погнали:

начинать нужно с азов. самый короткий запрос - ";". ломает людям мониторинги, потому что нагрузку создаёт, а текста не имеет.

в запросе обычно нужны какие-нибудь ключевые слова, которые бы что-нибудь вернули. из базы данных получать таблицы помогает запрос select.

весь SQL - он про кортежи и множества. кортежи - они же строки, они же tuple, они же row, они же записи - это по горизонтали, а множества - когда таких кортежей много.

в SQL множества не математические, в них случаются дубли. это сносит крышу теоретикам (они начинают делать id).

множество кортежей можно писать прямо в код как есть через values. кортеж пишется в скобочках через запятую.

люди черезвычайно плохи в том, чтобы помнить сто тыщ колонок по номерам, поэтому в SQL придумали привязывать к данным схему. важно: схема есть всегда, и ее привязка - это оверрайд прошлой. таких мест есть несколько (union, insert, copy, ..), в них надо следить, чтобы не покусало.

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

именованное сохранённое множество кортежей называют таблицей. для примера сохраним в таблицу🐈 прошлый запрос.

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

забрать всю таблицу как есть в множество можно с помощью table. теперь можно просто нажать на кнопку 🐈 на клавиатуре вместо печатания всей таблицы в запрос:

до сих пор мы ничего не делали с типами данных. в postgres есть тип данных unknown как раз для таких случаев. ближе к записи на диск постгрес попробует вывести типы, что вы имели в виду.

любые буковки в кавычках - это unknown, до тех пор, пока вы не натянете на него схему, явно или неявно. обратите внимание: в кавычках не text, в кавычках - unknown, который есть заготовка для чего угодно дальше по тексту.

почему это важно? потому что есть очень много перегруженных функций с разной семантикой для типов данных. "<->" есть расстояние, но в тексте, геометрии и географии расстояние между двумя величинами считается по разным правилам. указывать тип можно через два двоеточия.

множества (таблицы) не обладают упорядоченностью. порядок есть только в специальной их части под названием Ordered Set.

пишется как ORDER BY после множества.

иногда к элементам кортежа можно обращаться просто по номерам, это часто удобно в ORDER BY.

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

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

оптимизации: sychronized seq scans, parallel seq scan, index scan, ...

SQL - декларативный язык для операций над множествами. он не говорит, _как_ что-то надо делать, _как_ - решает база.

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

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

профайлер тоже всегда под рукой - можно написать explain (analyze, verbose, buffers) и посмотреть

итак, мы научились писать параллельные многопоточные программы на SQL. если хочется ещё и на много компов параллелить, то Citus - экстеншен, который и это вам в постгресе включит

рандомный видос и скрин из него:

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

ru.wikipedia.org/wiki/%D0%9D%D0…

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

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

en.wikipedia.org/wiki/Database_…

почему это важно: в современном мире всем нужен ARTIFICIAL INTELLIGENCE. если вы делаете магазин, то ваш пользователь точно хочет то, чего у вас нет. если вы не посоветуете что-то взамен, он точно ничего не купит.

тут в игру вступают расстояния как мера похожести. левенштейна.

кто хочет продолжение, задавайте вопросы и ставьте лайки :)

(также приходите с нами работать: kontur.io/join)

про postgis:

в 2022 году типичная таблица в GIS выглядит как две колонки: geometry для геометрии и jsonb для свойств.

geometry - это тип для хранения геометрического места точек объекта. наружу виден как EWKB, Extended Well Known Binary.

самое важное в геометрии - не изобрести нечаянно свою. это регулярно делают фронтендеры, это регулярно делают неначитанные алгоритмисты. потом получается что-то, в чём нет полигонов с дырками или админграниц с эксклавами.

почитайте OGC Simple Features.

ogc.org/standards/sfa

jsonb - это такой тип данных, в который можно положить джейсонки. в гис принято складывать свойства плоской джейсонкой, чтобы совместимость была с как можно большим количеством софта.

у jsonb есть вкусные операторы. ? позволяет проверить, есть ли там такой ключ вообще. #gischat

-> вытащит поддерево как json, а ->> - как текст.

выглядит будто отличаются только кавычками, но это обманчиво: отличаются ещё и типы данных. ручками вы напишете unknown в апострофах и сработает, из базы придёт text и не сработает, можно долго отлаживать, пока заметишь.

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

их есть много разных типов и каждый год придумывают ещё. можно запутаться, если не знать:

в постгресе индекс ускоряет не "всё подряд, это магия, я создам и тут индекс и там индекс и чтобы было быстрее", а только одну структуру в запросе:

[колонка индекса] [индексно-ускоряемый оператор] [константа]

(и симметричную).

ВСЁ. магии нет.

типично в лоб запрос к jsonb пишут как

jsonb ->> 'key' = 'value'. тут два оператора. индекс не включится.

надо переформулировать так, чтобы оператор был один:

jsonb @> '{"key":"value"}'

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

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

есть два контекста, где операторы ускоряются индексами:

- в WHERE - те, что возвращают boolean, подходит или нет строка

- в ORDER BY - те, что возвращают дистанцию.

всему остальному индекс не поможет*.