Решение для ускорения медленного запроса SELECT DISTINCT в Postgres

Запрос в основном:

SELECT DISTINCT "my_table"."foo" from "my_table" WHERE...

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

В рассматриваемой таблице 2,5 миллиона строк данных. DISTINCT необходим для целей, не перечисленных здесь (потому что я не хочу возвращать измененный запрос, а просто хочу получить общую информацию о том, как ускорить выполнение отдельных запросов на уровне СУБД. , если возможно).

Как я могу заставить DISTINCT работать быстрее (в частности, используя Postgres 9), не изменяя SQL (т.е. я не могу изменить этот входящий SQL, но имею доступ для оптимизации чего-либо на уровне БД)?


postgresql sql database database-optimization
person orokusaki    schedule 06.07.2011    source источник
comment
Чтобы дать ответ, потребуется полный запрос. Обычно по плохим причинам используют разные.   -  person Denis de Bernardy    schedule 06.07.2011
comment
@orokusaki: Запрос - это целое. Это не отдельная сторона, соединение на другой стороне и группа на третьей стороне. Проблема заключается не в том, что ваш запрос быстрее без различия ... На такой вопрос вы не можете ответить. Или да, вы можете: использовать Index.   -  person Cyril Gandon    schedule 06.07.2011
comment
@orokusaki: это действительно зависит от обстоятельств. Часто использование отличного в запросе отражает неоптимальное соединение где-то. Не всегда, но достаточно часто. В таких случаях идея состоит в том, чтобы переписать запрос так, чтобы подзапрос находился в подзапросе, который возвращает уникальные строки (или проверяется с помощью предложения in ()).   -  person Denis de Bernardy    schedule 06.07.2011
comment
@ Denis - есть неоптимальное соединение по производительности, но избежать этого невозможно. Я фильтрую по отношению M2M (получить всех пользователей, у которых есть [x, y или z] в их списке foos (m2m).   -  person orokusaki    schedule 06.07.2011
comment
Я не уверен, почему люди затрудняются сделать предположение о том, что это медленная часть. Относительно легко изолировать скорость запроса до чего-то вроде этого.   -  person Bagelstein    schedule 27.02.2018
comment
@Bagelstein, спасибо - я знаю, в SO это всегда расстраивало; вы должны прийти сюда на коленях и симулировать невежество, чтобы кто-нибудь заинтересовался вам помочь. Если вы пришли с какими-либо предшествующими знаниями, к вам относятся примерно так же, как если бы вы пошли к врачу и сказали: «Я думаю, у меня могут быть проблемы с X»; эго немедленно настораживает, и тебя считают идиотом.   -  person orokusaki    schedule 27.02.2018

Ответы (3)


arrow_upward
27
arrow_downward

Ваш DISTINCT заставляет его сортировать выходные строки для поиска дубликатов. Если вы поместите индекс в столбцы, выбранные запросом, база данных сможет прочитать их в порядке индекса и сохранить шаг сортировки. Многое будет зависеть от деталей запроса и задействованных таблиц - ваши слова «знаете, что проблема в DISTINCT» действительно ограничивают объем доступных ответов.

person antlersoft    schedule 06.07.2011
comment
Я знаю, что это ограничивает круг ответов, поэтому я и сделал это. Я ищу ответы только на уровне БД, который вы мне дали (+1). - person orokusaki; 06.07.2011
comment
Одного индекса недостаточно. У меня есть индекс для моего отдельного столбца, но запрос все еще занимает несколько минут, чтобы найти 8 миллионов строк и найти 4 различных значения. - person Cerin; 27.01.2014
comment
См. stackoverflow.com/a/14732410/32453, помещая выбранный отдельный запрос в подзапрос и подсчитывая, что сработало для меня, как ни странно . - person rogerdpack; 18.11.2014

arrow_upward
42
arrow_downward

Часто вы можете ускорить выполнение таких запросов, обойдя distinct, используя вместо этого group by:

select my_table.foo 
from my_table 
where [whatever where conditions you want]
group by foo;
person Community    schedule 06.07.2011
comment
Я не могу изменить SQL, поэтому я оставил большую часть запроса. - person orokusaki; 06.07.2011
comment
Что ж, либо попросите пользователей (либо разработчиков, пишущих приложение, которое выполняет эти запросы, либо пользователей, которые выполняют эти запросы ad hoc), чтобы они переключили свой SQL. Если вы не можете этого сделать, то, возможно, вы сможете немного сэкономить на индексации my_table на foo. - person ; 06.07.2011
comment
Для меня это было отличным решением. Сначала я думал, что из-за большого смещения мои запросы выполняются медленно, но после переключения с DISTINCT на GROUP BY они выполнялись в 20 раз быстрее. Спасибо! - person xaisoft; 08.08.2013
comment
Спасибо за отличный отзыв, Джек! Замена SELECT DISTINCT на GROUP BY сократила время выполнения моего конкретного запроса с 649 мс до 87 мс, что почти в 7,5 раз быстрее. Мое ВНУТРЕННЕЕ СОЕДИНЕНИЕ находится между таблицей, содержащей ~ 30 000 строк (из которых мне нужны совпадающие строки), и другой таблицей соединения, содержащей ~ 322 000 строк (которую я использовал для фильтрации первой). Я уже добавил индексы ко всем столбцам, используемым с обеих сторон моего INNER JOIN и одного предложения WHERE, поэтому я надеялся найти другую оптимизацию, которая поможет ускорить запрос, и в этом конкретном случае это сработало очень хорошо. - person bluebinary; 30.10.2013
comment
Вопрос в том ... Почему это ускоряет запрос? - person Ioan Alexandru Cucu; 15.11.2017
comment
@IoanAlexandruCucu Этот ответ может содержать некоторые подсказки по этому поводу. - person gerrit; 16.10.2019

arrow_upward
7
arrow_downward

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

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

Это означает, что если вы установили work_mem = 128MB, а вы установили max_connections = 100 (по умолчанию), у вас должно быть более 12,8 ГБ ОЗУ. По сути, вы говорите серверу, что он может использовать столько же для выполнения запросов (даже не учитывая другое использование памяти Postgres или иным образом).

person maniek    schedule 08.07.2011