| From: | Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> |
|---|---|
| To: | "'Tomas Vondra *EXTERN*'" <tomas(dot)vondra(at)2ndquadrant(dot)com>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
| Subject: | Re: Hash join gets slower as work_mem increases? |
| Date: | 2016-02-01 10:08:55 |
| Message-ID: | A737B7A37273E048B164557ADEF4A58B537DD013@ntex2010i.host.magwien.gv.at |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Jérôme Augé | 2016-02-04 09:12:48 | Understanding ANALYZE memory usage with "big" tsvector columns |
| Previous Message | Tomas Vondra | 2016-02-01 09:59:51 | Re: Hash join gets slower as work_mem increases? |