Re: Hash join gets slower as work_mem increases?

От: Albe Laurenz
Тема: Re: Hash join gets slower as work_mem increases?
Дата: ,
Msg-id: A737B7A37273E048B164557ADEF4A58B537DD013@ntex2010i.host.magwien.gv.at
(см: обсуждение, исходный текст)
Ответ на: Re: Hash join gets slower as work_mem increases?  (Tomas Vondra)
Список: pgsql-performance

Скрыть дерево обсуждения

Hash join gets slower as work_mem increases?  (Albe Laurenz, )
 Re: Hash join gets slower as work_mem increases?  (Pavel Stehule, )
 Re: Hash join gets slower as work_mem increases?  (Tomas Vondra, )
  Re: Hash join gets slower as work_mem increases?  (Albe Laurenz, )
   Re: Hash join gets slower as work_mem increases?  (Tomas Vondra, )
    Re: Hash join gets slower as work_mem increases?  (Albe Laurenz, )

Tomas Vondra wrote:
> Yes, that's clearly the culprit here. In both cases we estimate here are
> only ~4000 tuples in the hash, and 9.3 sizes the hash table to have at
> most ~10 tuples per bucket (in a linked list).
> 
> However we actually get ~3M rows, so there will be ~3000 tuples per
> bucket, and that's extremely expensive to walk. The reason why 100MB is
> faster is that it's using 2 batches, thus making the lists "just" ~1500
> tuples long.
> 
> This is pretty much exactly the reason why I reworked hash joins in 9.5.
> I'd bet it's going to be ~20x faster on that version.

Thank you for the explanation!

Yours,
Laurenz Albe


В списке pgsql-performance по дате сообщения:

От: Albe Laurenz
Дата:
Сообщение: Re: Hash join gets slower as work_mem increases?
От: Jérôme Augé
Дата:
Сообщение: Understanding ANALYZE memory usage with "big" tsvector columns