Re: Should pg 11 use a lot more memory building an spgist index?

On Wed, Oct 24, 2018 at 10:21:11 +0100,
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>Bruno Wolff III <bruno(at)wolff(dot)to> writes:
>> If I have a substantial database dump file to provide for reproducing this
>> do you prefer it on a web server somewhere? I expect that mailing very
>> large attachments to the lists is a bad idea.
>
>No, don't do that. If you can make sample data available for download,
>or point to some accessible dataset somewhere, that'd work.
>
> regards, tom lane

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.

http://wolff.to/iplocation is a bit under 400mb. It should download at about
1MB/sec. It is a plain text dump of the iplocation table with the alter table
for the constaint / exclude index added at the end.

http://wolff.to/postgresql.conf is the config file I use on the server.

The server has the following installed (but you don't need plperl for the
test):
postgresql11-plperl-11.0-1PGDG.rhel7.x86_64
postgresql11-libs-11.0-1PGDG.rhel7.x86_64
postgresql11-docs-11.0-1PGDG.rhel7.x86_64
postgresql11-11.0-1PGDG.rhel7.x86_64
postgresql11-server-11.0-1PGDG.rhel7.x86_64

The output of
createdb -U postgres test
psql -U postgres -f iplocation test
is:
SET
SET
SET
SET
SET
set_config
------------

(1 row)

SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
COPY 4398722
psql:iplocation:4398789: ERROR: out of memory
DETAIL: Failed on request of size 6264 in memory context "PortalContext".

It is certainly possible that my postgresql.conf file is bad and that I
just got away with it under 10.5 by the. The server is a vm with 32GB of
memory allocated to it. I set vm.overcommit_memory = 2 to avoid the oom
killer after upgrading to 11. Before that I didn't have a problem.

On Fedora with a more vanilla postgresql.conf the exclude constraint
built fine, but creating an spgist index file is taking forever (near a
day now) creating a normal spgist index on an ip address column for a
table with a lot of rows (I think around 150 million), that ran in a
reasonable amount of time on the server.