Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hSrga-0003dN-2L for pgsql-general@arkaria.postgresql.org; Mon, 20 May 2019 23:27:16 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hSrgX-0000A0-FS for pgsql-general@arkaria.postgresql.org; Mon, 20 May 2019 23:27:13 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hSrgX-00009t-0X for pgsql-general@lists.postgresql.org; Mon, 20 May 2019 23:27:13 +0000 Received: from mail-ot1-x32b.google.com ([2607:f8b0:4864:20::32b]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hSrgT-0007Dz-GD for pgsql-general@lists.postgresql.org; Mon, 20 May 2019 23:27:11 +0000 Received: by mail-ot1-x32b.google.com with SMTP id n14so14650477otk.2 for ; Mon, 20 May 2019 16:27:09 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=gLAdLDG9xlZw3Uz+3DECeAwtR2u6QoH+fCoiKNTEjig=; b=ZHCABjAYeoUIHyEQmiD1hUfJs+R9gM9Gq+lSLfFJQ5TNJoBuMlgoD6agFk8sgi41M0 JBIJ7WpCludHJ45/uhnV2MWKdgtQMDex16wHwFsS4IUlHW89Vqo5LaZAa0GPFQZEoQuq i/kQSqmnwGm7wH72+5GLPCuhjFTjOvQzVmhgA1SGq1TVBuBTdcIXQ2yI9S7atY62kRE2 zhT+d1O1HkzB/vJ4MmbRWIdBKAHX7+x6HpeR1V7D+J/xUqmSsPkH8DlhfAr/R3FTfUsS Qz3c8Mpb7M1LWxM+tqT6TZJHWDUxnhB71FJ8J/qiUbSNyPo/bkNhLvPyJs6c2rMJwP04 qfhw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=gLAdLDG9xlZw3Uz+3DECeAwtR2u6QoH+fCoiKNTEjig=; b=XveoVCVC9s9q4sBSCwsWiLxxGMUpPboIbznKtpuaq2H070gMg7Zqjg1IJDU+tDz6Tk xA+/pFqKLYzMJ6lHNQtX/H/w2nvM+Lw4spvh+rOHfuw43GF/5nkdpoi1V6G6GrXwpgVL jSzKVKDkQmytUyKPj43X6pQwALkVEgbxME2fFL0O1S1k/kHEph3eIatmpktDRfqRCywX VPwmLohtcpm1nQyD3ERAN2lFQSUGYZfXm40OJCoAuneXtutwyDuh/93E55Gazygar1bq wD3lRvwxLmiO7NY5NUVGdj+xXkF8sK++OUL5eGLtCN9i36o6e5Hc6ZZ4t7cQgc64b/Tb gMiQ== X-Gm-Message-State: APjAAAUmM7vK3aTgKMqtZpeyaAHFT6yFQxn2YsTh6bvqsqxsPyhRgEq7 Nhn03zUHAM2qBUVwY+2g+duF73MpVgX4wg== X-Google-Smtp-Source: APXvYqzqMGmJKQn+ADvdFXOY9LDfpk6gMljrY6FImDJM2MU72ZbTwLP2+5rvbdf1vI3triDRHdFYgw== X-Received: by 2002:a9d:77c1:: with SMTP id w1mr17883683otl.269.1558394828445; Mon, 20 May 2019 16:27:08 -0700 (PDT) Received: from [192.168.1.10] (ip70-171-116-89.no.no.cox.net. [70.171.116.89]) by smtp.googlemail.com with ESMTPSA id 189sm7039887oih.26.2019.05.20.16.27.07 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Mon, 20 May 2019 16:27:07 -0700 (PDT) Subject: Re: Loading table with indexed jsonb field is stalling To: pgsql-general@lists.postgresql.org References: <20505.1558202555@sss.pgh.pa.us> <90835D2B-1FBA-4F26-8157-7C831C24B266@gmail.com> <3A35A039-D8F5-4B40-9B41-42E70BEF2C2E@gmail.com> <806d4915-f05d-8e75-0a42-e11b48596ab5@gmail.com> <12E9FEDD-37E9-4D51-9BA7-94FF5039BB1B@gmail.com> <563e995f-d7b1-7e2f-c1f1-b00db7a18589@gmail.com> <3EF519CA-17FE-4B68-8B7A-78200E0A408F@gmail.com> From: Ron Message-ID: <71e50367-2375-86a8-f795-97c894728998@gmail.com> Date: Mon, 20 May 2019 18:27:07 -0500 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Thunderbird/60.6.1 MIME-Version: 1.0 In-Reply-To: <3EF519CA-17FE-4B68-8B7A-78200E0A408F@gmail.com> Content-Type: multipart/alternative; boundary="------------04DD11734E50DCDD1030A490" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------04DD11734E50DCDD1030A490 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 5/20/19 5:43 PM, Will Hartung wrote: >> On May 20, 2019, at 2:55 PM, Ron wrote: >> >> And it pathologically loads even when there's just a PK on the numeric field? > Yea, that works fine. > >> Drop all indexes, load data, recreate indexes? > No, I use the incremental load as it gives a much better indication when the process has gone off the deep end. That's fine for tracing errors in a test environment, but. >> But pg_restore only creates indexes AFTER the data loads. > Yes. Demonstrating that it doesn’t work either incrementally or all at once. > > (No, I have no confidence that the Aurora build will ever finish in any reasonable amount of time.) I'm confused.  You wrote above that loading without indexes and with just the PK works just fine; if you *really* need it loaded in Aurora or production, just drop the indexes beforehand? -- Angular momentum makes the world go 'round. --------------04DD11734E50DCDD1030A490 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit On 5/20/19 5:43 PM, Will Hartung wrote: 
On May 20, 2019, at 2:55 PM, Ron <ronljohnsonjr@gmail.com> wrote:


And it pathologically loads even when there's just a PK on the numeric field?

Yea, that works fine.


Drop all indexes, load data, recreate indexes?

No, I use the incremental load as it gives a much better indication when the process has gone off the deep end.

That's fine for tracing errors in a test environment, but.

But pg_restore only creates indexes AFTER the data loads.

Yes. Demonstrating that it doesn’t work either incrementally or all at once.

(No, I have no confidence that the Aurora build will ever finish in any reasonable amount of time.)

I'm confused.  You wrote above that loading without indexes and with just the PK works just fine; if you really need it loaded in Aurora or production, just drop the indexes beforehand?

--
Angular momentum makes the world go 'round.
--------------04DD11734E50DCDD1030A490--