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 1hSs2X-0004uw-8n for pgsql-general@arkaria.postgresql.org; Mon, 20 May 2019 23:49:57 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hSs2V-0000Dv-Hb for pgsql-general@arkaria.postgresql.org; Mon, 20 May 2019 23:49:55 +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 1hSs2V-0000Do-5Q for pgsql-general@lists.postgresql.org; Mon, 20 May 2019 23:49:55 +0000 Received: from mail-qk1-x741.google.com ([2607:f8b0:4864:20::741]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hSs2R-0006gp-6j for pgsql-general@lists.postgresql.org; Mon, 20 May 2019 23:49:54 +0000 Received: by mail-qk1-x741.google.com with SMTP id p18so9406236qkk.0 for ; Mon, 20 May 2019 16:49:50 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:subject:from:in-reply-to:date:cc:message-id:references :to; bh=3baCn2ZYVTzenFaqmWAoMLK7hv2AGHN7UiAhijevL78=; b=H9ta8F+fsvIwH7WKv9jltXP0X9SY4IOuNGV0UwwQhTirHgUBcf3E2MvLkLLYiBidyb NHZ4bYndngDO3RqEch5hMBCImHGnnmeDDocx176teXSPve969FWS0QRXNUIMpLp9PY1w irJWN4V7BW5UffPghS3RzCGR99+g9VwJrSPEnET+wFwJMG1vCINN0SC8odMqLOh1p6e5 Cn0JmJmygGZq4DMgW/jDy+0ZavArwnZDPQP5zw7+SP+Im3BqPE3lZmaZT42KC+4TX/cQ EeQxFUs4bSI6hAOmC7Ez7iGjv9ZoNUWKbkBu42WOXcSQYCc02tnN08tGv42ADU0T4PnN b2tw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:subject:from:in-reply-to:date:cc :message-id:references:to; bh=3baCn2ZYVTzenFaqmWAoMLK7hv2AGHN7UiAhijevL78=; b=kLkWA0/13+3YVu42R+IGqDuMYbHALpGoujIxR93x7LSU8YRXhzucM5uuMx8bB7XNxp pGmFitfEvsAALJJ4mSkbtDE60m8UcNSvEjxjki7m6kUZdj+0yl9EAlSllBKAa+q2b8E3 AmshfzjBHP0RVtavMwWmFNnwGlxVCps5/b6FISm9RolIXIDAoIeBzUQP8X9zc86DCenF APHZbtDbcKQt9CIvSyrC3UfAUHD1UPF5pESlKiCWykvle751yqndJRfsqjRmP8MyYfPc Iw/06/JFqRLxIjlVe1TdyUwbP+7X/SsaCvHquI9xwAewIOYsoQ0F1652DBfWOB6ZhuHR zazA== X-Gm-Message-State: APjAAAWFJiRzKoPcpAThE6m4XTZu/UVFeQjwVleHDdNLFGAJAF08Kosd d6h2zMyWaG5/smD8GLPQM88= X-Google-Smtp-Source: APXvYqxMkN3VMWQlhAjabMzBN74KplPcj6GgRIaYGb6BnYqBaKWIoO0TpfmzP15NCMnSunq74oOZhA== X-Received: by 2002:a37:b8c:: with SMTP id 134mr61301843qkl.121.1558396189328; Mon, 20 May 2019 16:49:49 -0700 (PDT) Received: from [10.35.60.64] ([64.164.57.170]) by smtp.gmail.com with ESMTPSA id j19sm5382015qkl.82.2019.05.20.16.49.48 (version=TLS1 cipher=ECDHE-RSA-AES128-SHA bits=128/128); Mon, 20 May 2019 16:49:48 -0700 (PDT) Content-Type: multipart/alternative; boundary="Apple-Mail=_F8409377-4313-45DA-BDF3-098AE8B2CBD1" Mime-Version: 1.0 (Mac OS X Mail 9.3 \(3124\)) Subject: Re: Loading table with indexed jsonb field is stalling From: Will Hartung In-Reply-To: <7c0a001e-d3d5-c521-1a27-77d05f547fef@aklaver.com> Date: Mon, 20 May 2019 16:49:45 -0700 Cc: Will Hartung , Ron , pgsql-general@lists.postgresql.org Message-Id: <375E86FF-5B4D-4896-B4ED-FCA8C1D3A60B@gmail.com> 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> To: Adrian Klaver X-Mailer: Apple Mail (2.3124) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_F8409377-4313-45DA-BDF3-098AE8B2CBD1 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii > On May 20, 2019, at 4:39 PM, Adrian Klaver = wrote: >=20 > So the issue is building the index(s) not loading the data, correct? Correct. Since I have been exploring this, I have not been able to successfully = create a gin jsonb_path_ops index on a jsonb field either as a new index = on a table with existing data, or as a pre-existing index on an empty = table and loading the data in. The create index does not finish on the existing data, and loading data = does not finish when the index pre-exists. We currently have a 68M row table loaded in an Aurora instance on AWS (I = do not know the instance/machine size), and the index has been building = for 48 hours now. The pg_restore is currently working on that index: CREATE INDEX eis_ent_traits ON public.eis_entity USING gin (traits jsonb_path_ops); --Apple-Mail=_F8409377-4313-45DA-BDF3-098AE8B2CBD1 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii
On May 20, 2019, at 4:39 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

So the issue is building the = index(s) not loading the data, correct?

Correct.

Since I have been exploring this, I have not been able to = successfully create a gin jsonb_path_ops index on a jsonb field either = as a new index on a table with existing data, or as a pre-existing index = on an empty table and loading the data in.

The create index does not finish on the = existing data, and loading data does not finish when the index = pre-exists.

We = currently have a 68M row table loaded in an Aurora instance on AWS (I do = not know the instance/machine size), and the index has been building for = 48 hours now.

The pg_restore is currently working on that index:


CREATE = INDEX eis_ent_traits
  = ON public.eis_entity
  = USING gin
  (traits = jsonb_path_ops);


= --Apple-Mail=_F8409377-4313-45DA-BDF3-098AE8B2CBD1--