Re: 9.5: Better memory accounting, towards memory-bounded HashAgg

On 12/28/14, 2:45 PM, Peter Geoghegan wrote:
> On Sun, Dec 28, 2014 at 12:37 PM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
>> Do others have similar numbers? I'm quite surprised at how little
>> work_mem seems to matter for these plans (HashJoin might be a different
>> story though). I feel like I made a mistake -- can someone please do a
>> sanity check on my numbers?
>
> I have seen external sorts that were quicker than internal sorts
> before. With my abbreviated key patch, under certain circumstances
> external sorts are faster, while presumably the same thing is true of
> int4 attribute sorts today. Actually, I saw a 10MB work_mem setting
> that was marginally faster than a multi-gigabyte one that fit the
> entire sort in memory. It probably has something to do with caching
> effects dominating over the expense of more comparisons, since higher
> work_mem settings that still resulted in an external sort were slower
> than the 10MB setting.
>
> I was surprised by this too, but it has been independently reported by
> Jeff Janes.

I haven't tested for external faster than internal in a while, but I've certainly seen this effect before. Generally, once you get beyond a certain size (maybe 100MB?) you run the risk of a tapesort being faster than an internal sort.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com