Comments 15
Моя конфигурацияА
Поддерживаю, и насколько это быстрее традиционного метода?
Данный конкретный пример выдает что то вроде 44 сек. vs 32 cек. Однако, на работе приходится иметь дело с менее объемными выборками, но более сложные запросы (работа без индексов, таблица содержит порядка 100 полей). Вот там прирост просто потрясает от 50 сек. при использовании стандартного "SELECT" до 1-2 сек. с использованием вышеприведенной техники.
Хотелось бы уточнить, правильно ли я Вас понял?
Вы выгружаете данные с запроса в CSV файл и потом для работы с этими данными просто читаете этот файл, так?
Если так, то почему бы не использовать какую-нибудь временную таблицу, в которую можно сбросить эти данные, поработать с ними и потом удалить. Там же можно и индексы и любые плюшки реляционных БД.
Вы выгружаете данные с запроса в CSV файл и потом для работы с этими данными просто читаете этот файл, так?
Если так, то почему бы не использовать какую-нибудь временную таблицу, в которую можно сбросить эти данные, поработать с ними и потом удалить. Там же можно и индексы и любые плюшки реляционных БД.
Данные просто представлены в csv формате (по сути обычная строка). Можно использовать различные подходы. Из за специфики моей работы, я не могу менять структуру таблицы или даже дабовлять индексы, поэтому приходится добиваться прироста производительности альтернативными методами.
Все равно я за то, чтобы использовать временную таблицу с выбранными данными. Хоть на локальной машине разверните тот же Postgresql, а Ваши дальнейшие алгоритмы работать с этим будут наверняка быстрее, например, если требуется дополнительная фильтрация этих данных или поиск.
Не покидает чувство, что я неправильно понимаю ваши мотивы использовать именно CSV.
Не покидает чувство, что я неправильно понимаю ваши мотивы использовать именно CSV.
Тут вот в чем дело, действительно для ускорения выборки по 2м таблицам я использую materizlized view что по сути является временной таблицей. Но даже так, приведенный выше метод работает быстрее. Завтра доберусь до рабочей машины, постараюсь выложить конкретные цифры.
Результаты замеров выборка (14к) записей:
1) Прямой select, Where условие по неиндексированному полю — 21,4с
2) COPY предыдущего запроса — 13,1с
3) Выборка того же select но из materialized view с индексом по полю — 12,6с
4) COPY materialized view — 1.8с
собственно как я и упоминал выше, скорость просто сумасшедшая
1) Прямой select, Where условие по неиндексированному полю — 21,4с
2) COPY предыдущего запроса — 13,1с
3) Выборка того же select но из materialized view с индексом по полю — 12,6с
4) COPY materialized view — 1.8с
собственно как я и упоминал выше, скорость просто сумасшедшая
PostgreSQL 9.5.2, Python 3.5.1 — может пора обновиться?
зачем считывать миллионы записей я не увидел ни одной задачи и проблемы?
наверное вы пытались сделать бэкапилку вручную или еще что-то типа того, а может статистику посчитать за 20 лет
зачем считывать миллионы записей я не увидел ни одной задачи и проблемы?
наверное вы пытались сделать бэкапилку вручную или еще что-то типа того, а может статистику посчитать за 20 лет
Любопытный способ. Было бы приятно увидеть результаты синтетических тестов SELECT с условиями на примере трех ситуаций:
Простой SELECT из большой таблицы
Тот же SELECT из большой таблицы, разбитой на партиции с грамотно построенными индексами и использованием constraint exclusion
Тот же SELECT, обернутый в COPY по вашей методике.
В рамках статьи ожидал также объяснения, почему этот метод работает. Как в вашем случае строится план запроса? В моем понимании при таком сценарии все равно должен сначала выполняться внутренний селект, результат которого будет отправлен в копи. КМК, ускорение может быть разве что психологическое — в том случае, когда результат вложенного селекта будет отправляться в поток вывода пачками, по мере выполнения запросами.
И еще. Как выглядит ваш postgresql.conf? Размер оперативной памяти ни о чем не говорит в отрыве от размера таблицы и размера work_mem и shared_buffers.
Простой SELECT из большой таблицы
Тот же SELECT из большой таблицы, разбитой на партиции с грамотно построенными индексами и использованием constraint exclusion
Тот же SELECT, обернутый в COPY по вашей методике.
В рамках статьи ожидал также объяснения, почему этот метод работает. Как в вашем случае строится план запроса? В моем понимании при таком сценарии все равно должен сначала выполняться внутренний селект, результат которого будет отправлен в копи. КМК, ускорение может быть разве что психологическое — в том случае, когда результат вложенного селекта будет отправляться в поток вывода пачками, по мере выполнения запросами.
И еще. Как выглядит ваш postgresql.conf? Размер оперативной памяти ни о чем не говорит в отрыве от размера таблицы и размера work_mem и shared_buffers.
Не могли бы вы пояснить, каким образом грамотно построенные индексы и партиционирование могли бы ускорить процесс при выполнении схожего приведённому в примере запроса?
Я не специалист в Postgresql, но есть подозрение, что в приведённом примере индексы могут только ухудшить время выполнения запроса, а партиционирование скорее всего повлияет никак.
В других случаях — возможно, но для приведённых примеров с математической точки зрения и то и другое выглядит бесполезно. Что я пропускаю?
Я не специалист в Postgresql, но есть подозрение, что в приведённом примере индексы могут только ухудшить время выполнения запроса, а партиционирование скорее всего повлияет никак.
В других случаях — возможно, но для приведённых примеров с математической точки зрения и то и другое выглядит бесполезно. Что я пропускаю?
Я тоже не специалист в Postgresql, всего лишь энтузиаст с небольшим опытом работы с этой СУБД в личном проекте-печочнице. Мой проект предполагал работу с достаточно крупными таблицами-хранилищами, содержащими порядка 10^8 строк. Я столкнулся с тем, что даже простые селекты с условием на индексированное при помощи кластеризованного btree-индекса поле работали очень медленно. Это при том, что результат селекта предположительно должен был возвращать не более миллиона строк. Понятное дело, сложные запросы, включающие inner join тоже работали медленно.
Анализируя ситуацию, я убедился, что на больших таблицах идет раздувание древовидного индекса, когда его размеры становятся сравнимы с размерами самих данных. Одной из хороших практик в таких ситуациях является дробление таблицы на партиции, когда есть пустая мастер-таблица, задающая структуру данных, и набор наследниц, собственно хранящих информацию. Обычно дробят данные по полю (или комбинации полей), по которым обычно задаются условия запроса.
К примеру, есть таблица, в которую постоянно добавляются данные. Каждая запись промаркирована отметкой времени timestamp. Если предполагается делать запросы, извлекающие данные для конкретного дня, разумно дробить таблицу именно по этому условию. Один день — одна партиция.
В этой схеме зачастую на каждую партицию вешается check constraint, дабы при исполнении запроса отсекать партиции, в которых заведомо нет интересующих данных.
Такое решение сильно снижает общий размер таблицы с индексами, с которой будет работать СУБД во время исполнения одного или пачки похожих запросов. Я предполагаю, что это может положительно повлиять на производительность, т.к. вся таблица, ожидающая присоединения, может быть закеширована в RAM. Поэтому, в частности, я попросил уточнить сайзинг и настройки work_mem и shared_buffers.
Безусловно, в своих предположениях я могу ошибаться, т.к. я достоверно не знаю, что находится у постгреса под капотом. Да и не СУБДист я. Поэтому было бы действительно интересно увидеть в рамках статьи анализ и некие best practices. Но, как я понял из комментариев, у автора не было возможности менять структуру хранения и вообще была иная задача.
Кстати, насколько я помню, наличие индексов в постгрес приводит к замедлению только на маленьких таблицах. Да и то, если планировщик их по какой-то причине использует.
Анализируя ситуацию, я убедился, что на больших таблицах идет раздувание древовидного индекса, когда его размеры становятся сравнимы с размерами самих данных. Одной из хороших практик в таких ситуациях является дробление таблицы на партиции, когда есть пустая мастер-таблица, задающая структуру данных, и набор наследниц, собственно хранящих информацию. Обычно дробят данные по полю (или комбинации полей), по которым обычно задаются условия запроса.
К примеру, есть таблица, в которую постоянно добавляются данные. Каждая запись промаркирована отметкой времени timestamp. Если предполагается делать запросы, извлекающие данные для конкретного дня, разумно дробить таблицу именно по этому условию. Один день — одна партиция.
В этой схеме зачастую на каждую партицию вешается check constraint, дабы при исполнении запроса отсекать партиции, в которых заведомо нет интересующих данных.
Такое решение сильно снижает общий размер таблицы с индексами, с которой будет работать СУБД во время исполнения одного или пачки похожих запросов. Я предполагаю, что это может положительно повлиять на производительность, т.к. вся таблица, ожидающая присоединения, может быть закеширована в RAM. Поэтому, в частности, я попросил уточнить сайзинг и настройки work_mem и shared_buffers.
Безусловно, в своих предположениях я могу ошибаться, т.к. я достоверно не знаю, что находится у постгреса под капотом. Да и не СУБДист я. Поэтому было бы действительно интересно увидеть в рамках статьи анализ и некие best practices. Но, как я понял из комментариев, у автора не было возможности менять структуру хранения и вообще была иная задача.
Кстати, насколько я помню, наличие индексов в постгрес приводит к замедлению только на маленьких таблицах. Да и то, если планировщик их по какой-то причине использует.
А вот такая штука не быстрее будет? Было бы интересно посмотреть на сравнение: http://blog.cloudera.com/blog/2016/03/feather-a-fast-on-disk-format-for-data-frames-for-r-and-python-powered-by-apache-arrow/
Sign up to leave a comment.
Чтение больших объемов данных в Python/Postgresql