Date: Fri, 26 Oct 2018 06:23:14 -0500
From: Bruno Wolff III <bruno@wolff.to>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-general@lists.postgresql.org, pgsql-hackers@lists.postgresql.org
Subject: Re: Should pg 11 use a lot more memory building an spgist index?
Message-ID: <20181026112314.GA8199@wolff.to>
Mail-Followup-To: Bruno Wolff III <bruno@wolff.to>,
	Tom Lane <tgl@sss.pgh.pa.us>, pgsql-general@lists.postgresql.org,
	pgsql-hackers@lists.postgresql.org
References: <20181024012314.GA27428@wolff.to>
 <19003.1540370028@sss.pgh.pa.us>
 <20181024083748.GA3296@wolff.to>
 <19088.1540372871@sss.pgh.pa.us>
 <20181025174306.GA3184@wolff.to>
 <24288.1540545369@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Disposition: inline
In-Reply-To: <24288.1540545369@sss.pgh.pa.us>
User-Agent: Mutt/1.10.1 (2018-07-13)
Precedence: bulk

On Fri, Oct 26, 2018 at 10:16:09 +0100,
  Tom Lane <tgl@sss.pgh.pa.us> wrote:
>Bruno Wolff III <bruno@wolff.to> writes:
>> I have something that seems to produce it on rhel7. Fedora isn't working
>> well either, but the difference may be due to postgresql.conf being
>> different or some difference in the Fedora build.
>
>Hmm, in my hands this produces the same size leak (~28GB) in either v10
>or v11.  In HEAD, somebody's made it even worse (~43GB).  So this is
>certainly pretty broken, but I'm not sure why it seems worse to you in
>v11 than before.

As a short term work around, could I create the index first and use 
insert statements, each in their own transaction, to get the table loaded 
with the index?

Is the issue on Fedora taking very long to build a normal spgist index for 
network addresses worth pursuing separately, or is it likely to be the same 
underlying cause? I don't really need to get this working there, as that was 
just to help with testing.

I could also try adjusting memory limits temporarily. If the leak is 28GB 
on a 32GB system I might be able to get the index built if less memory 
is tied up in other things. My workstation also has 32GB and the exclude 
index did build there with the postgresql.conf having lower memory limits.