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 1hSsjC-00079v-TQ for pgsql-general@arkaria.postgresql.org; Tue, 21 May 2019 00:34:03 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hSsjB-0000vY-Ow for pgsql-general@arkaria.postgresql.org; Tue, 21 May 2019 00:34:01 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hSsjB-0000vR-FC for pgsql-general@lists.postgresql.org; Tue, 21 May 2019 00:34:01 +0000 Received: from out2-smtp.messagingengine.com ([66.111.4.26]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hSsj6-0007k9-RA for pgsql-general@lists.postgresql.org; Tue, 21 May 2019 00:34:01 +0000 Received: from compute6.internal (compute6.nyi.internal [10.202.2.46]) by mailout.nyi.internal (Postfix) with ESMTP id 1820D24647; Mon, 20 May 2019 20:33:54 -0400 (EDT) Received: from mailfrontend1 ([10.202.2.162]) by compute6.internal (MEProxy); Mon, 20 May 2019 20:33:54 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= subject:to:cc:references:from:message-id:date:mime-version :in-reply-to:content-type:content-transfer-encoding; s=fm3; bh=1 WqM37+gYi85viAOgoHYJvDoJtXoVTdMyRZLK3jr66M=; b=cv336JtRiQla29fi3 Qv7OxGm9xx4qdOS2gXg5RPtfXtINUDCJTYaKPXjX95s4uO81cXbKEzN6tIVWvDHQ KedKxrbembBHGrnqSj2RYUtMBUpaHFTfISaSK0nB0JNobaMLEudp8y43W/bLdHZG vjFjlxn6nfz+eikdjGmRf+Wh5MZRmbTH0mnTtcV5yPoyQkHNOiZyHgW29zc6248u h9OqKHVSHhkEqVRYJRFqe1EYDQm9ab2T2n9Sfur5ChgAWluyYWSe+wGYOFbgQ3I/ LMisxUsIiLCDJLiPJG+XLH0GN5Lk9iEi0D+2POig7hs1xLqEOFVLacH8NhPgmfzO QzMew== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-transfer-encoding:content-type :date:from:in-reply-to:message-id:mime-version:references :subject:to:x-me-proxy:x-me-proxy:x-me-sender:x-me-sender :x-sasl-enc; s=fm2; bh=1WqM37+gYi85viAOgoHYJvDoJtXoVTdMyRZLK3jr6 6M=; b=ltNUxQq/IvVlwk1OKMnuDQyQUxtSJY27jxvPjym7XWiXW5DkZTIopoH+0 oFnzt0JpdA5cOCNG6P0/TkiOSE2Cjy1187eBUjLO3M3v1rE1yDseux2vlvnNzrZn Lvpnli1BlM92wxi4gxqyd5EvqnUEfiOiviEsa0D0qCgf/7hkSAyz1nU/kqRrwNrR PlnVnRuU22QQtplFcWGzEtz74qPueCgxZtvB+tnPM0i7jtZIJ0TFrpvGJrQSvJit QBW6c9lxNhyIVGBloJQeh/zF/rFXAglhewbehnS+ejfSXu+tictTnNBaQcS9OclF j9wZCoIqYOao9hZU3218sW41DxIPw== X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgeduuddruddtledgfeehucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurhepuffvfhfhkffffgggjggtgfesthekredttdefheenucfhrhhomheptegurhhi rghnucfmlhgrvhgvrhcuoegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtoh hmqeenucffohhmrghinhepphhoshhtghhrvghsqhhlrdhorhhgnecukfhppeejuddrvddu vddrudehtddrvddtvdenucfrrghrrghmpehmrghilhhfrhhomheprggurhhirghnrdhklh grvhgvrhesrghklhgrvhgvrhdrtghomhenucevlhhushhtvghrufhiiigvpedt X-ME-Proxy: Received: from [192.168.1.4] (unknown [71.212.150.202]) by mail.messagingengine.com (Postfix) with ESMTPA id 3C6498005B; Mon, 20 May 2019 20:33:53 -0400 (EDT) Subject: Re: Loading table with indexed jsonb field is stalling To: Will Hartung Cc: Ron , 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> <7c0a001e-d3d5-c521-1a27-77d05f547fef@aklaver.com> <375E86FF-5B4D-4896-B4ED-FCA8C1D3A60B@gmail.com> <703a6c76-854d-3d1c-8be6-30864c873d05@aklaver.com> <52ACFC7C-47B8-4BF1-BDF1-8810F02BD297@gmail.com> From: Adrian Klaver Message-ID: <9aa4804b-a5fe-9036-bb7d-07732c9fb99b@aklaver.com> Date: Mon, 20 May 2019 17:33:52 -0700 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: <52ACFC7C-47B8-4BF1-BDF1-8810F02BD297@gmail.com> Content-Type: text/plain; charset=windows-1252; format=flowed Content-Language: en-US Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On 5/20/19 5:22 PM, Will Hartung wrote: > >> On May 20, 2019, at 5:14 PM, Adrian Klaver wrote: >> >> Well looks like you are down to Tom's suggestion of creating a test case. Given that it seems confined to the jsonb field and corresponding index, I would think that is all that is needed for the test case. Start with some smaller subset, say 10,000 rows and work up till you start seeing an issue. > > This will take quite some work, and I wouldn’t attempt it with less than 5M rows to load. > From this post: https://www.postgresql.org/message-id/CAKMEDdxE95SC76wadMzrH454HpvCV4tOdEmuZRO%3DpwArEw6YVg%40mail.gmail.com You started seeing longer times by the time you got to 500,000 rows and then it fell off by 1.1 million. Creating the data: CREATE TABLE AS gin_test SELECT traits FROM eis_entity LIMIT 1000000; Then: pg_dump -t gin_test > > -- Adrian Klaver adrian.klaver@aklaver.com