Re: Loading table with indexed jsonb field is stalling

> On May 20, 2019, at 11:13 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> 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 ? Ds 0:21 postgres: 10/main: checkpointer process
1421 ? Ss 0:38 postgres: 10/main: writer process
1422 ? Ss 0:22 postgres: 10/main: wal writer process
1423 ? Ss 0:04 postgres: 10/main: autovacuum launcher process
1424 ? Ss 0:04 postgres: 10/main: stats collector process
1425 ? Ss 0:00 postgres: 10/main: bgworker: logical replication launcher
15917 tty1 S 0:00 su - postgres
16300 ? Rs 10:39 postgres: 10/main: postgres willtest 10.35.60.64(54594) COPY
16444 ? Ss 0:08 postgres: 10/main: autovacuum worker process willtest waiting
16633 tty1 S+ 0:00 /usr/lib/postgresql/10/bin/psql
16641 ? Ss 0:00 postgres: 10/main: postgres postgres [local] idle

> 2) As superuser:
> select * from pg_stat_activity ;

datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change | wait_event_type | wait_event | state | backend_xid | backend_xmin | query | backend_type
-------+----------+-------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-----------------+---------------------+--------+-------------+--------------+---------------------------------------+---------------------
| | 1425 | 10 | postgres | | | | | 2019-05-17 12:00:17.659235-07 | | | | Activity | LogicalLauncherMain | | | | | background worker
| | 1423 | | | | | | | 2019-05-17 12:00:17.658936-07 | | | | Activity | AutoVacuumMain | | | | | autovacuum launcher
16384 | willtest | 16444 | | | | | | | 2019-05-20 12:16:14.564982-07 | 2019-05-20 12:16:14.641913-07 | 2019-05-20 12:16:14.641913-07 | 2019-05-20 12:16:14.641914-07 | Lock | page | active | 624 | 623 | autovacuum: ANALYZE public.eis_entity | autovacuum worker
13051 | postgres | 16889 | 10 | postgres | psql | | | -1 | 2019-05-20 13:44:50.84062-07 | 2019-05-20 13:46:17.209382-07 | 2019-05-20 13:46:17.209382-07 | 2019-05-20 13:46:17.209387-07 | | | active | | 623 | select * from pg_stat_activity; | client backend
16384 | willtest | 16300 | 10 | postgres | psql | 10.35.60.64 | | 54594 | 2019-05-20 11:24:59.865383-07 | 2019-05-20 12:15:42.494372-07 | 2019-05-20 12:15:42.494372-07 | 2019-05-20 12:15:42.494378-07 | LWLock | WALWriteLock | active | 623 | 612 | COPY eis_entity FROM STDIN ; | client backend
| | 1421 | | | | | | | 2019-05-17 12:00:17.557683-07 | | | | Activity | BgWriterMain | | | | | background writer
| | 1420 | | | | | | | 2019-05-17 12:00:17.557992-07 | | | | | | | | | | checkpointer
| | 1422 | | | | | | | 2019-05-17 12:00:17.554268-07 | | | | | | | | | | walwriter
(8 rows)

It’s been running for about an hour and a half when I took these.

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).

Thanks.