Почему агрегатные функции SQL намного медленнее, чем Python и Java (или OLAP для бедняков)

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

Выполняется ли запрос Postgres быстрее во второй раз после того, как он получил возможность кэшировать данные? Чтобы быть немного более справедливым, ваш тест на Java и Python должен в первую очередь покрывать затраты на получение данных (в идеале - загрузку их с диска).

Если этот уровень производительности является проблемой для вашего приложения на практике, но вам нужна СУБД по другим причинам, вы можете посмотреть memcached. Тогда у вас будет более быстрый кэшированный доступ к необработанным данным и вы сможете выполнять вычисления в коде.

Я бы сказал, что ваша тестовая схема бесполезна. Чтобы выполнить запрос к базе данных, сервер базы данных проходит несколько шагов:

1. проанализировать SQL
2. разработать план запроса, т.е. е. решить, какие индексы использовать (если есть), оптимизировать и т. д.
3. если используется индекс, найдите в нем указатели на фактические данные, затем перейдите в соответствующее место в данных или
4. если индекс не используется, просканируйте весь стол, чтобы определить, какие строки необходимы
5. загрузить данные с диска во временное место (надеюсь, но не обязательно, в память)
6. выполнить вычисления count () и avg ()

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

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

• Сравните время выполнения вашего запроса с SELECT без агрегирующих функций (т.е. отрезок шага 5)
• Если вы обнаружите, что агрегация приводит к значительному замедлению, попробуйте, если Python сделает это быстрее, получив необработанные данные с помощью простого SELECT из сравнения.

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

Для этого есть несколько способов:

• Кэшировать данные (в памяти!) Для последующего доступа либо через собственные возможности движка db, либо с помощью таких инструментов, как memcached
• Уменьшите размер хранимых данных
• Оптимизируйте использование индексов. Иногда это может означать полный отказ от использования индекса (в конце концов, это тоже доступ к диску). Что касается MySQL, я, кажется, помню, что рекомендуется пропускать индексы, если вы предполагаете, что запрос извлекает более 10% всех данных в таблице.
• Если в вашем запросе хорошо используются индексы, я знаю, что для баз данных MySQL это помогает размещать индексы и данные на отдельных физических дисках. Однако я не знаю, применимо ли это к Postgres.
• Также могут возникнуть более сложные проблемы, такие как перестановка строк на диск, если по какой-то причине результирующий набор не может быть полностью обработан в памяти. Но я бы оставил подобные исследования до тех пор, пока не столкнусь с серьезными проблемами производительности, которые я не смогу найти другим способом, поскольку это требует знания множества мелких деталей вашего процесса.

Обновлять:

Я только что понял, что вам, похоже, не нужны индексы для вышеуказанного запроса и, скорее всего, вы их тоже не используете, поэтому мои советы по индексам, вероятно, не помогли. Извини. Тем не менее, я бы сказал, что проблема не в агрегировании, а в доступе к диску. В любом случае, я оставлю индексный материал, он может еще пригодиться.

Еще одна вещь, которую СУБД обычно делает для вас, - это обеспечение параллелизма, защищая вас от одновременного доступа со стороны другого процесса. Это делается путем установки блокировок, и это связано с некоторыми накладными расходами.

Если вы имеете дело с полностью статическими данными, которые никогда не меняются, и особенно если вы в основном работаете в «однопользовательском» сценарии, то использование реляционной базы данных не обязательно принесет вам много пользы.

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

Можно ли предварительно составить план запроса и оптимизации? Я бы подумал, что хранимая процедура сделает это, но на самом деле это не помогает.

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

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

Думаю, можно оценить стоимость построения запроса с пустой таблицей (тайминги от 20 до 60 мс)

Я до сих пор не понимаю, почему тесты Java / Python недействительны. Postgres просто не выполняет намного больше работы (хотя я до сих пор не рассмотрел аспект параллелизма, а только кеширование и построение запросов).

ОБНОВИТЬ: Я не думаю, что будет справедливо сравнивать SELECTS, как это предлагается, потянув 350 000 через драйвер и шаги сериализации в Python для запуска агрегации, или даже опустить агрегацию, поскольку накладные расходы при форматировании и отображении трудно отделить от времени. Если оба движка работают с данными в памяти, это должно быть сравнение яблок с яблоками, хотя я не уверен, как гарантировать, что это уже происходит.

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

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

Postgres делает намного больше, чем кажется (для начала, поддерживая согласованность данных!)

Если значения не должны быть на 100% точными или таблица обновляется редко, но вы часто выполняете это вычисление, вы можете захотеть изучить Материализованные представления, чтобы ускорить его.

(Обратите внимание, я не использовал материализованные представления в Postgres, они выглядят немного хакерскими, но могут соответствовать вашей ситуации).

Материализованные представления

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

Я бы посчитал, что 200 мс для чего-то вроде этого довольно неплохое. Быстрый тест на моем сервере oracle, та же структура таблицы с примерно 500 тыс. Строк и без индексов, занимает от 1 до 1,5 секунд, что почти все просто оракул, высасывающий данные с диска.

Реальный вопрос: достаточно ли 200 мс?

-------------- Более --------------------

Мне было интересно решить эту проблему с помощью материализованных представлений, поскольку я никогда особо не играл с ними. Это в оракуле.

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

create materialized view mv_so_x 
build immediate 
refresh complete 
START WITH SYSDATE NEXT SYSDATE + 1/24/60
 as select count(*),avg(a),avg(b),avg(c),avg(d) from so_x;

Пока он обновляется, строки не возвращаются

SQL> select * from mv_so_x;

no rows selected

Elapsed: 00:00:00.00

Как только он обновляется, это НАМНОГО быстрее, чем выполнение необработанного запроса.

SQL> select count(*),avg(a),avg(b),avg(c),avg(d) from so_x;

  COUNT(*)     AVG(A)     AVG(B)     AVG(C)     AVG(D)
---------- ---------- ---------- ---------- ----------
   1899459 7495.38839 22.2905454 5.00276131 2.13432836

Elapsed: 00:00:05.74
SQL> select * from mv_so_x;

  COUNT(*)     AVG(A)     AVG(B)     AVG(C)     AVG(D)
---------- ---------- ---------- ---------- ----------
   1899459 7495.38839 22.2905454 5.00276131 2.13432836

Elapsed: 00:00:00.00
SQL> 

Если мы вставим в базовую таблицу, результат не будет сразу виден просмотром MV.

SQL> insert into so_x values (1,2,3,4,5);

1 row created.

Elapsed: 00:00:00.00
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL> select * from mv_so_x;

  COUNT(*)     AVG(A)     AVG(B)     AVG(C)     AVG(D)
---------- ---------- ---------- ---------- ----------
   1899459 7495.38839 22.2905454 5.00276131 2.13432836

Elapsed: 00:00:00.00
SQL> 

Но подождите минуту или около того, и MV будет обновляться за кулисами, и результат будет возвращен так быстро, как вы могли бы пожелать.

SQL> /

  COUNT(*)     AVG(A)     AVG(B)     AVG(C)     AVG(D)
---------- ---------- ---------- ---------- ----------
   1899460 7495.35823 22.2905352 5.00276078 2.17647059

Elapsed: 00:00:00.00
SQL> 

Это не идеально. для начала, это не в реальном времени, вставки / обновления не будут видны сразу. Кроме того, у вас запущен запрос на обновление MV независимо от того, нужен он вам или нет (это можно настроить на любой временной интервал или по запросу). Но это действительно показывает, насколько быстрее MV может казаться конечному пользователю, если вы можете жить со значениями, которые не совсем точны до секунды.

Спасибо за тайминги Oracle, это то, что я ищу (хотя и разочаровывает :-)

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

Я не думаю, что время прохождения запроса должно быть очень большим, поскольку я выполняю запросы на том же компьютере, на котором работает Postgres, поэтому это не может увеличить задержку?

Я также провел некоторую проверку размеров кеша, и, похоже, Postgres полагается на ОС для обработки кеширования, они специально упоминают BSD как идеальную ОС для этого, поэтому я думаю, что Mac OS должна быть довольно умной при переносе таблицы в объем памяти. Если кто-то не имеет в виду более конкретные параметры, я думаю, что более конкретное кеширование вне моего контроля.

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

Я только что проверил тайминги с помощью MySQL 5, и они немного хуже, чем Postgres. Итак, за исключением некоторых крупных достижений в кешировании, я думаю, это то, чего я могу ожидать, идя по реляционному маршруту базы данных.

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

Я сам являюсь специалистом по MS-SQL, и мы бы использовали DBCC PINTABLE, чтобы держать таблицу в кеше, и УСТАНОВИТЬ СТАТИСТИКУ IO, чтобы видеть, что она читается из кеша, а не с диска.

Я не могу найти в Postgres ничего, чтобы имитировать PINTABLE, но pg_buffercache, кажется, дает подробную информацию о том, что находится в кеше - вы можете проверить это и посмотреть, действительно ли ваша таблица кэшируется.

Быстрый подсчет конверта заставляет меня подозревать, что вы просматриваете страницы с диска. Предполагая, что Postgres использует 4-байтовые целые числа, у вас есть (6 * 4) байта на строку, поэтому ваша таблица занимает минимум (24 * 350 000) байтов ~ 8,4 МБ. Предполагая, что стабильная пропускная способность вашего жесткого диска составляет 40 МБ / с, вы видите примерно 200 мс для чтения данных (которые, как указано, должны быть тем местом, где тратится почти все время).

Если я где-то не облажался с математикой, я не понимаю, как это возможно, что вы можете прочитать 8 МБ в свое приложение Java и обработать его в то время, которое вы показываете, - если этот файл уже не кэширован либо на диске, либо на вашем ОПЕРАЦИОННЫЕ СИСТЕМЫ.

Я повторно протестировал MySQL, указав ENGINE = MEMORY, и это ничего не меняет (все еще 200 мс). Sqlite3, использующий базу данных в памяти, также дает аналогичные тайминги (250 мс).

Математика здесь выглядит правильной (по крайней мере, размер, как и размер sqlite db :-)

Я просто не верю аргументу, что диск вызывает медленность, поскольку есть все признаки того, что таблицы находятся в памяти (все ребята из postgres предупреждают, что не следует слишком сильно пытаться закрепить таблицы в памяти, поскольку они клянутся, что ОС сделает это лучше, чем программист )

Чтобы прояснить тайминги, код Java не считывается с диска, что делает его совершенно несправедливым сравнением, если Postgres читает с диска и вычисляет сложный запрос, но это действительно не важно, БД должна быть достаточно умной, чтобы приносить небольшой таблицу в память и предварительно скомпилировать хранимую процедуру ИМХО.

ОБНОВЛЕНИЕ (в ответ на первый комментарий ниже):

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

Вы используете TCP для доступа к Postgres? В таком случае Нэгл не рассчитывает время.