Date: Fri, 26 Oct 2018 09:54:37 -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: <20181026145437.GA11390@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>
 <20181026112314.GA8199@wolff.to>
 <25927.1540557847@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Disposition: inline
In-Reply-To: <25927.1540557847@sss.pgh.pa.us>
User-Agent: Mutt/1.10.1 (2018-07-13)
Precedence: bulk

On Fri, Oct 26, 2018 at 13:44:07 +0100,
  Tom Lane <tgl@sss.pgh.pa.us> wrote:
>Bruno Wolff III <bruno@wolff.to> writes:
>
>> 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?
>
>Yes; it might also be that you don't even need to break it up into
>separate statements.

It was time to refresh the geolite data anyway so I tried this. I needed 
to turn memory_overcommit back on (0) to avoid an error, but the load went OK 
without the oom killer doing anything. So things are fully working again.

Thanks for your help.

>> 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?
>
>This issue only applies if it was an exclusion constraint.  If you saw
>slowness or bloat with a plain index, that would be worth investigating
>separately.

I'll start a seperate thread if I get something to reasonably ask about. 
The current dataset is probably a lot larger then needed to demonstrate the 
issue. The difference might be do to configuration or how Fedora built it. 
And I'll want to compare back to version 10. In the end I'll probably ask 
why it is slower in one case as opposed to the other and it might not even 
be a real bug.