Почему Postgres выполняет хэш в этом запросе?

У меня есть две таблицы: A и P. Я хочу получить информацию обо всех строках в A, чей идентификатор находится во временной таблице, которую я создал, tmp_ids. Однако в таблице P есть дополнительная информация о A, foo, и я хочу получить и эту информацию. У меня есть следующий запрос:

SELECT A.H_id AS hid,
       A.id AS aid,
       P.foo, A.pos, A.size
FROM tmp_ids, P, A
WHERE tmp_ids.id = A.H_id
  AND P.id = A.P_id

Я заметил, что это происходит медленно, и когда я попросил Postgres объяснить, я заметил, что он объединяет tmp_ids с индексом A, который я создал для H_id, с вложенным циклом. Однако он хэширует все P перед выполнением хэш-соединения с результатом первого слияния. P довольно большой, и я думаю, что это то, что занимает все время. Зачем ему создавать там хэш? P.id является первичным ключом P, а A.P_id имеет собственный индекс.

ОБНОВЛЕНИЕ: Все типы данных — INTEGER, кроме A.size, который является ДВОЙНОЙ ТОЧНОСТЬЮ, и P.foo, который является VARCHAR. Я использую PostgreSQL версии 8.4.

Вот объяснение: http://explain.depesz.com/s/WBo.


postgresql sql optimization query-optimization
person Claudiu    schedule 17.06.2010    source источник
comment
вам, возможно, придется явно объявить тип данных полей соединения   -  person pcent    schedule 18.06.2010
comment
Опубликуйте анализ объяснения или, еще лучше, разместите объяснение на explain.depesz.com и предоставьте ссылку. И какая версия ПГ?   -  person rfusca    schedule 18.06.2010
comment
С некоторыми СУБД вы можете принудительно выполнить циклическое соединение, но я не думаю, что это возможно с PostrgresSQL. Это своего рода позор, потому что есть много случаев, когда оптимизатор не понимает реальность данных, подобную вашей.   -  person Pointy    schedule 18.06.2010
comment
@Pointy Вы можете установить enable_hashjoin = false, что в этом случае, вероятно, сделает это.   -  person rfusca    schedule 18.06.2010
comment
хм, я понимаю, что таблица P, которую он хеширует, имеет только 43000 записей, так что, возможно, запрос выполняется медленно не поэтому... возможно, это была другая часть программы   -  person Claudiu    schedule 18.06.2010
comment
@rfusca спасибо - но это все еще не кажется таким приятным, как INNER LOOP JOIN, который (например) позволяет SQL Server   -  person Pointy    schedule 18.06.2010
comment
wiki.postgresql.org/wiki/Todo#Features_We_Do_Not_Want   -  person Milen A. Radev    schedule 18.06.2010

Ответы (3)


arrow_upward
3
arrow_downward

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

person Stephen Denne    schedule 21.06.2010

arrow_upward
1
arrow_downward

Без анализа объяснения такие проблемы обычно возникают из-за отключенной статистики или необычной настройки, необходимой для random_page_cost или seq_page_cost.

Он может работать лучше с

set enable_hashjoin = false;
person rfusca    schedule 17.06.2010
comment
Когда вы сделаете это, оптимизатор выполнит объединение слиянием. Все еще неоптимально, так как он должен сначала отсортировать ключи перед сопоставлением. Мне нужны вложенные циклы, управляемые индексом. Возможно, вы захотите установить enable_hashjoin = true и поэкспериментировать с настройкой work_mem, maintenance_work_mem (а также shared_buffers) на postgresql.org/docs/current/static/ на меньший размер. Иногда оптимизатор думает, что памяти достаточно, и каждый раз выполняет хеш-соединение. - person ; 04.01.2013

arrow_upward
0
arrow_downward

Ваша проблема в том, что у оптимизатора нет правильной статистики, чтобы определить, сколько совпадений "A.H_id = tmp_ids.id" будет создано, что является распространенной проблемой с временными таблицами - у них нет статистики, как обычный делает. Он предполагает, что 21 строка будет совпадать из «Сканирования индекса с использованием idx_A_handid на A», но на самом деле их только 3. Это выделено в анализе объяснения, где стрелка вверх на самом низком уровне имеет рядом с собой 7, что дает множитель того, насколько ошибочной была оценка.

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

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

person Greg Smith    schedule 22.06.2010