Comments 32
это нам очень повезло, что
now()достаточно "легкая", но все-таки лучше эти вызовы не клонировать.
не повезло - а так и задумано.
Значит, у нас налицо просто техническая ошибка с повторным вычислением одного и того же, которая нам чего-то да стоила.
Неужели оптимизатор в PG настолько плохой, что не может вычислить эту функцию один раз? В чем гипотетический смысл повторных вычислений, если она
в рамках одного запроса всегда возвращает одно и то же значение.
? Ну кроме проведения бенчмарков по переносу ее с левой стороны FROM на правую?
Причем нужно вспомнить, что SQL — декларативный язык, и странно на нем оперировать императивными подходами. Проще тогда было бы взять нормальный язык, а не на птичьих диалектах SQL чего-то выражать.
Неужели оптимизатор в PG настолько плохой, что не может вычислить эту функцию один раз?
"Вычислит"-то он ее один раз (в смысле, сходит и посмотрит в системный таймер), а вот подставить это значение придется 100500 раз - в каждую из строк.
Причем нужно вспомнить, что SQL — декларативный язык, и странно на нем оперировать императивными подходами. Проще тогда было бы взять нормальный язык, а не на птичьих диалектах SQL чего-то выражать.
Видимо, есть некоторый универсальный язык, которым и БЛ писать, и с базой работать, и микроконтроллеры программировать, и "словами говорить" - все одинаково удобно... Жаль, я про него не знаю.
а вот подставить это значение придется 100500 раз — в каждую из строк.
А есть какой-то иной способ записать значения 100500 строк?
универсальный язык
Зачем же универсальный. Я говорю, что ненормально сначала разрабатывать язык для декларативного описания того, что мы хотим получить, а затем натягивать на него описания того, как мы это хотим получить. Для как существуют традиционные императивные языки. В декларативном SQL же вы только боритесь с самим языком таким подходом, пытаясь впихнуть невпихуемое.
какой-то иной способ записать значения 100500 строк?
Например, приведенный в конце статьи?
Каждый ЯП в чем-то хорош, а в чем-то не очень. Но если SQL в работе с данными хорош, то почему бы не придумать способы обхода некоторых не очень удачных моментов.
Например, приведенный в конце статьи?
Какая разница, как вы их вычислили, вы хотите сказать, этот запрос магически пишет значения на диск?
то почему бы не придумать способы обхода некоторых не очень удачных моментов.
И эти способы — использование другого, подходящего, языка.
Между "сформировать и записать" строки и просто "записать", очевидно, есть разница, которую мы можем попытаться уменьшить. А есть и общая часть, на которую мы, конечно, никак не повлияем.
использование другого, подходящего, языка
Так что же это за язык, например? Только сразу договоримся, что он должен быть столь же удобен, как SQL, при обработке данных.
Сформировать — это вызов now(). Вы сами утверждаете, что он делается только один раз, сколько бы он не был написан в запросе, а дальше только готовый результат используется. Т.е. now() в запросе нужно воспринимать просто как синтаксическую особенность сослаться на одно конкретное значение, а не выполнение каких-то вычислений. А раз так, то зачем его вручную выносить за пределы "цикла" (коим фактически является повторение в VALUES)? А если не так — то возвращаемся к изначальному вопросу — оптимизатор настолько глуп, что не видит очевидной оптимизации?
Только сразу договоримся, что он должен быть столь же удобен, как SQL, при обработке данных.
Вот что-то я не вижу в последнем листинге из статьи никакого удобства при решении элементарной задачи из первого листинга. Паковать в JSON только чтобы потом тут же из него извлечь — это не удобство.
Сформировать — это вызов
now(). Вы сами утверждаете, что он делается только один раз, сколько бы он не был написан в запросе, а дальше только готовый результат используется.
Я утверждал не это. Если внутри функции реализована мемоизация возвращаемого результата, это никак не влияет на количество ее вызовов. Ускоряет - да, но сделать 100500 вызовов "запоминающей" функции в любом случае дороже, чем 1.
я не вижу в последнем листинге из статьи никакого удобства
Это значит, что с проблемами генерируемых запросов вы не сталкивались: возможность SQL-инъекции, потеря производительности на передаче и парсинге объемного тела запроса, невозможность использовать prepared statements, ...
Я утверждал не это.
Очевидно, что в контексте этой ветки написав "вычислив" я имел ввиду "вызов", и не важно, делает функция внутри мемоизацию, или нет. Для своей работы оптимизатор может использовать концепцию "вычислителя выражений", поэтому и такое слово.
Так вот, спрашиваю снова — почему оптимизатор сам не вытащит повторяющийся вызов за пределы "цикла"? Информация о том, что это безопасно (вызов всегда возвращает один и тот же результат), у него есть. Это базовая оптимизация в любом языке — переиспользование результатов вычислений, вынос инвариантов.
Это значит, что с проблемами генерируемых запросов вы не сталкивались: возможность SQL-инъекции, потеря производительности на передаче и парсинге объемного тела запроса, невозможность использовать prepared statements
Для генерируемых запросов нужно пользоваться Bulk/batch execution. Никаких из указанных вами минусов при этом нет. Не знаю, зачем вместо этого генерировать запрос со вшитыми данными и иметь с ним проблемы, а потом героически их решать. Разве только драйвер БД не поддерживает батчи, ну так это опять вопрос, почему это не сделано?
Это базовая оптимизация в любом языке — переиспользование результатов вычислений, вынос инвариантов.
Если это актуально в любом ЯП, то почему проделать то же самое в SQL считается какой-то неуместной ересью?
Замечу, что такой вынос "за скобки" - это действие самого разработчика, а не компилятора/оптимизатора языка. Иногда они могут это взять на себя, иногда - нет, но упрекать их в этом несправедливо.
Для генерируемых запросов нужно пользоваться Bulk/batch execution.
Давно хочу увидеть пример реализации INSERT .. ON CONFLICT в операторе COPY, только без промежуточных триггеров.
... почему оптимизатор сам...? ... почему это не сделано?
Вероятно, потому что кто-то (может быть, вы?) пока не взял на себя реализацию какого-то данного конкретного функционала в PostgreSQL.
Если это актуально в любом ЯП, то почему проделать то же самое в SQL считается какой-то неуместной ересью?
Имеется ввиду конечно же, что это делает любой оптимизирующий компилятор любого языка, а не то, что вы должны делать это вручную. Вручную это можно делать, если от этого вырастает читабельность, а в SQL она от этого обычно падает.
Вероятно, потому что кто-то (может быть, вы?) пока не взял на себя реализацию какого-то данного конкретного функционала в PostgreSQL.
Вот это-то и странно. Сколько лет она уже разрабатывается, а такой простейшей вещи нет...
это делает любой оптимизирующий компилятор любого языка ... такой простейшей вещи нет
Правда-правда, это должен и делает любой компилятор любого языка?.. Возьмем какой-нибудь Brainfuck - чем не ЯП? Не нравится? Ок, BASIC, Pascal - вполне заслуженные ЯП.
Так откуда такая категоричность, что все это делают, один только PostgreSQL убогий?
Может все-таки, наоборот, - разного рода оптимизации (и не обязательно все) есть только в языках/компиляторах, в которые кто-то вложился (деньгами, временем, проработкой концепций и алгоритмики)? И наличие какой-либо оптимизации - это плюс, конечно, но ее отсутствие - не минус.
Во-первых, кто вам сказал, что только PostgreSQL убогий? Всякий компилятор, который не делает эти базовые вещи, не заслуживает право называться оптимизирующим, ИМХО. Они потому и базовые, что их проще всего сделать, и если их нет, то с большой вероятностью и остальные оптимизации тоже отсутствуют.
Теперь по пунктам:
- Brainfuck — понятия не имею, кто-то вообще задумывался о написании оптимизирующего компилятора для него? Если задумывался, то первым делом должен был сделать выявление общих подвыражений — просто потому, что это банально самое простое, что можно сделать
- BASIC — к сожалению, сложно что-то гуглить по его имени, это слишком общее слово. Кое-как нагугленный за 5 минут FreeBasic использует в качестве бекенда gcc или llvm, а значит и все их оптимизации
- Pascal — одна из первых ссылок по запросу "list of optimizations in free pascal" — https://www.freepascal.org/docs-html/prog/progsu58.html. Там есть слова
CSE Use common subexpression elimination
По крайней мере, компилятор знает об этом, хотя эта глава не о флажках командной строки, а о директивах внутри кода (если я правильно понял)
Всякий компилятор, который не делает эти базовые вещи, не заслуживает право называться оптимизирующим
О чем мы тогда спорим? У PG нет компилятора вообще, тем более оптимизирующего - так что наивно ждать от него их функций.
Большинство языков так или иначе оптимизируют поток выполнения (или как это все можно назвать). Но у меня вот сомнения по поводу now() - а есть уверенность, что в каждый момент выполнения запроса now() одна и та же - время ведь не остановилось, там даже 4 секунды прошло или сколтко там...
now() всегда возвращает время начала текущей транзакции. Тут чуть подробнее.
Возможно, для задачи сбора метрик лучше бы подошла time series database, а не rdbms. Разные типы баз данных предлагают разные модели работы с этим самыми данными, что отражается и в языках запросов.
Тут нет timeseries-данных - у нас тут сохраняется лишь последнее значение для ключевой пары, хоть оно и timestamp.
Теперь понятно. А почему не подходит key-value?
Вполне подходит, даже лучше, но не всегда архитектурно оправдано введение в проект еще одного элемента - будь то KV (Redis), columnar (Citus, ClickHouse), timeseries (TimeScale), ... - эксплуатационные издержки могут превысить профит от использования.
Но если оно уже есть и уже синхронизируется с основным - то, конечно.
Блин, я ничего непонял. Ни какие проблемы это вызвало, ни объяснения их решения. Что за "сортировать все", ни откуда взялось fk2_5. Похоже что весь смысл был - сходи и прочитай две моих другие статьи.
Спасибо за статью, простите, не увидел сколько в итоге сэкономим на проблеме #3?
В зависимости от объема и структуры данных (сколько там повторяющихся значений) мы можем двое сократить сетевой трафик до сервера, вынести константное тело запроса в prepared statement и вообще убрать издержки на его парсинг/планнинг.
В очень грубой оценке на исходный запрос размером 1MB это будет ускорение и снижение cpu-нагрузки сервера СУБД примерно вдвое:
передача по сети 40мс вместо 80мс
parse: 0мс вместо 10мс
plan: 0мс вместо 10мс
exec: 10мс вместо 5мс (запрос-то сложнее)
Хех, думал что в Postgres 15 INSERT ON CONFLICT можно заменить на MERGE, но почитал интернеты, оказалось что в общем случае нельзя
Спасибо вам за ваши труды, узнал очень многое с ваших статей. Очень полезная и нужная информация пишите и дальше)
А сериализация/десереализация данных в JSON бесплатная? Был простой и понятный запрос, стало что-то, в чем необходимо разбираться. Сколько выиграли? Про now(), вызванный 10 млн раз, тоже не показано, насколько сократится время.
PostgreSQL Antipatterns: простой(?) INSERT… VALUES