Re: Loading table with indexed jsonb field is stalling

> On May 20, 2019, at 5:31 PM, Tom Lane wrote: > > Well, you're the only one who's seen this problem, and none of the > rest of us have any idea how to reproduce it. So if you want something > to get done in a timely fashion, it's up to you to show us a test case. So, we had success. We look

Re: Loading table with indexed jsonb field is stalling

On 5/20/19 7:21 PM, Will Hartung wrote: On May 20, 2019, at 5:15 PM, Ron wrote: Are there a sufficiently small number of elements in each traits object that you can do something like this, on the UNINDEXED table? SELECT traits->element1, traits->element2, count(*) from eis_entry group by trait

Re: Loading table with indexed jsonb field is stalling

On 5/20/19 5:33 PM, Adrian Klaver wrote: 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 thi

Re: Loading table with indexed jsonb field is stalling

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

Re: Loading table with indexed jsonb field is stalling

Will Hartung writes: >> 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

Re: Loading table with indexed jsonb field is stalling

> 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

Re: Loading table with indexed jsonb field is stalling

> On May 20, 2019, at 5:15 PM, Ron wrote: > > Are there a sufficiently small number of elements in each traits object that > you can do something like this, on the UNINDEXED table? > SELECT traits->element1, traits->element2, count(*) > from eis_entry > group by traits->element1, traits->eleme

Re: Loading table with indexed jsonb field is stalling

On 5/20/19 6:51 PM, Will Hartung wrote: On May 20, 2019, at 4:27 PM, Ron > wrote: 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 befo

Re: Loading table with indexed jsonb field is stalling

On 5/20/19 4:49 PM, Will Hartung wrote: On May 20, 2019, at 4:39 PM, Adrian Klaver > 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 js

Re: Loading table with indexed jsonb field is stalling

> On May 20, 2019, at 4:27 PM, Ron wrote: > > 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? Because the jsonb_path_ops index does not rebuild, and it

Re: Loading table with indexed jsonb field is stalling

> On May 20, 2019, at 4:39 PM, Adrian Klaver 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

Re: Loading table with indexed jsonb field is stalling

On 5/20/19 3: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 i

Re: Loading table with indexed jsonb field is stalling

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 indicat

Re: Loading table with indexed jsonb field is stalling

> 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

Re: Loading table with indexed jsonb field is stalling

On 5/20/19 4:48 PM, Will Hartung wrote: On May 20, 2019, at 2:36 PM, Ron wrote: I think you answered this earlier, but does the same stalling happen when indexes are dropped? No, the data loads fine. The way I originally stumbled upon this was that I had off loaded the data for some other ki

Re: Loading table with indexed jsonb field is stalling

> On May 20, 2019, at 2:36 PM, Ron wrote: > I think you answered this earlier, but does the same stalling happen when > indexes are dropped? No, the data loads fine. The way I originally stumbled upon this was that I had off loaded the data for some other kinds of testing. There are severa

Re: Loading table with indexed jsonb field is stalling

On 5/20/19 4:14 PM, Will Hartung wrote: [snip] Also to note, I tried just loading the table with no indexes, and I was getting a solid 22MB/s via iostat of just raw data load (just to proof that I/O system, while certainly not extraordinary, was functional). I think you answered this earlier,

Re: Loading table with indexed jsonb field is stalling

> On May 20, 2019, at 11:13 AM, Adrian Klaver wrote: > What do the below show: > > 1) ps ax | grep postgres $ ps -ax | grep postgres 1171 ?S 0:04 /usr/lib/postgresql/10/bin/postgres -D /var/lib/postgresql/10/main -c config_file=/etc/postgresql/10/main/postgresql.conf 1420 ?

Re: Loading table with indexed jsonb field is stalling

> On May 20, 2019, at 11:12 AM, Tom Lane wrote: > > Hmm, so not a deadlock (plus you're running a version that has that > bug fixed). It sounds like you might have hit an actual bug, perhaps > one that causes it to endlessly re-split an index page, or something > along that line. But it's goi

Re: Loading table with indexed jsonb field is stalling

> On May 20, 2019, at 11:13 AM, Adrian Klaver wrote: > > What do the below show: > > 1) ps ax | grep postgres > > 2) As superuser: > select * from pg_stat_activity ; I’ll restart the process and report back. Thanks.

Re: Loading table with indexed jsonb field is stalling

On 5/20/19 11:05 AM, Will Hartung wrote: I send this earlier, but it did not make it to the list, so I’m resending it. On May 18, 2019, at 11:02 AM, Tom Lane wrote: In addition to the questions about what PG version you're using, is the backend process that's doing the load actually consuming

Re: Loading table with indexed jsonb field is stalling

Will Hartung writes: >> On May 18, 2019, at 11:02 AM, Tom Lane wrote: >> In addition to the questions about what PG version you're using, is >> the backend process that's doing the load actually consuming CPU time, >> or is it just sitting? > It’s consuming both CPU and disk space. Hmm, so not

Re: Loading table with indexed jsonb field is stalling

I send this earlier, but it did not make it to the list, so I’m resending it. > On May 18, 2019, at 11:02 AM, Tom Lane wrote: > > In addition to the questions about what PG version you're using, is > the backend process that's doing the load actually consuming CPU time, > or is it just sitting?

Re: Loading table with indexed jsonb field is stalling

I send this earlier, but it did not make it to the list, so I’m resending it. > On May 18, 2019, at 6:55 AM, Adrian Klaver wrote: > Is the jsonb field the only field in the table? > > Can we see the table schema? # \d eis_entity Table "public.eis_entity" Column

Re: Loading table with indexed jsonb field is stalling

I sent these earlier, but they didn’t get to the list, so I’m resending — sorry about that. > On May 17, 2019, at 7:53 PM, Tim Cross wrote: > > Which version of postgres? PostgreSQL 10.8 (Ubuntu 10.8-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.3.0-16ubuntu3) 7.3.0, 64

Re: Loading table with indexed jsonb field is stalling

On 5/20/19 9:55 AM, Will Hartung wrote: Please reply to list also. Ccing list. On Sat, May 18, 2019 at 6:55 AM Adrian Klaver > wrote: In addition to what Tim asked: Is the jsonb field the only field in the table? Can we see the table schema? D

Re: Loading table with indexed jsonb field is stalling

Will Hartung writes: > I am trying to load data in to a table with a jsonb field that is indexed > as gin (field jsonb_path_ops). > ... > The current file is "stuck", pushing past 20hrs so far. In addition to the questions about what PG version you're using, is the backend process that's doing th

Re: Loading table with indexed jsonb field is stalling

On 5/17/19 12:10 PM, Will Hartung wrote: I am trying to load data in to a table with a jsonb field that is indexed as gin (field jsonb_path_ops). In addition to what Tim asked: Is the jsonb field the only field in the table? Can we see the table schema? -- Adrian Klaver adrian.kla...@ak

Re: Loading table with indexed jsonb field is stalling

Will Hartung writes: > I am trying to load data in to a table with a jsonb field that is indexed > as gin (field jsonb_path_ops). > > It's a large table, and I'm loading it 100K rows at a time. Eventually, it > would be 30M+ rows in the table. > > Originally I simply loaded the table and then t

Loading table with indexed jsonb field is stalling

I am trying to load data in to a table with a jsonb field that is indexed as gin (field jsonb_path_ops). It's a large table, and I'm loading it 100K rows at a time. Eventually, it would be 30M+ rows in the table. Originally I simply loaded the table and then tried to create the index, but it neve