Re: DBT-3 with SF=20 got failed

On 09/24/2015 07:42 PM, Robert Haas wrote:
> On Thu, Sep 24, 2015 at 12:40 PM, Tomas Vondra
> <tomas(dot)vondra(at)2ndquadrant(dot)com> wrote:
>> There are two machines - one with 32GB of RAM and work_mem=2GB, the other
>> one with 256GB of RAM and work_mem=16GB. The machines are hosting about the
>> same data, just scaled accordingly (~8x more data on the large machine).
>>
>> Let's assume there's a significant over-estimate - we expect to get about
>> 10x the actual number of tuples, and the hash table is expected to almost
>> exactly fill work_mem. Using the 1:3 ratio (as in the query at the beginning
>> of this thread) we'll use ~512MB and ~4GB for the buckets, and the rest is
>> for entries.
>>
>> Thanks to the 10x over-estimate, ~64MB and 512MB would be enough for the
>> buckets, so we're wasting ~448MB (13% of RAM) on the small machine and
>> ~3.5GB (~1.3%) on the large machine.
>>
>> How does it make any sense to address the 1.3% and not the 13%?
>
> One of us is confused, because from here it seems like 448MB is 1.3%
> of 32GB, not 13%.

Meh, you're right - I got the math wrong. It's 1.3% in both cases.

However the question still stands - why should we handle the
over-estimate in one case and not the other? We're wasting the same
fraction of memory in both cases.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services