Slow: execution over 5 min
update only entity e set jsondoc = jsondoc ||
jsonb_build_object(‘BeaOutstandingProposal’,
(select count(1) || ’ Proposals’ from proposal p
where p.jsondoc ->> ‘EntityId’ = e.jsondoc ->>‘EntityId’
));

Fast: Completed within 10s
update entity e set jsondoc = jsondoc ||
jsonb_build_object(‘BeaOutstandingProposal’,
(select count(1) || ’ Proposals’ from proposal p
where p.fkid_entity = e.pkid
));

Prepare PG

$ cd /opt/postgresql-11.7/
$ ./configure --prefix=/opt/pg11
$ make && make install
$ cd ~
$ /opt/pg11/bin/initdb -D /home/postgres/data15432 -Upostgres
$ vi data15432/postgresql.conf
port=15432
$ /opt/pg11/bin/pg_ctl -D /home/postgres/data15432 -l pg11.log start &
$ /opt/pg11/bin/psql -p15432 -Upostgres
psql (11.7)
Type "help" for help.

postgres=# 

Create table and index

create table entity(id bigint primary key,pkid bigint,isdeleted boolean, jsondoc jsonb);
create index idx_pkid_entity ON entity USING btree (pkid);
create index idx_entity ON entity USING gin (jsondoc jsonb_path_ops);

create table proposal(id bigint primary key,fkid_entity bigint,jsondoc jsonb);
create index idx_fkid_entity_proposal ON proposal USING btree (fkid_entity);
CREATE index idx_proposal ON proposal USING gin (jsondoc jsonb_path_ops);

Insert table entity(100k records),table proposal(10k)

insert into entity
select s.id,(random()*100000)::bigint,random() < 0.01,
 ('{"EntityId":"'||(random()*100000)::bigint||'", "company":"enmotech"}')::jsonb
from generate_series(1,100000) as s(id);

insert into proposal
select s.id,(random()*10000)::bigint,
 ('{"EntityId":"'||(random()*10000)::bigint||'", "company":"BEA"}')::jsonb
from generate_series(1,10000) as s(id); 

test case1: update base on condition with int type

begin;

explain analyze update entity e set jsondoc = jsondoc ||
jsonb_build_object('BeaOutstandingProposal',
(select count(1) || ' Proposals' from proposal p
where p.fkid_entity = e.pkid
));

QUERY PLAN                                                                     
------------------
 Update on entity e  (cost=0.00..1158063.06 rows=100000 width=55) (actual time=2149.525..2149.525 rows=0 loops=1)
   ->  Seq Scan on entity e  (cost=0.00..1158063.06 rows=100000 width=55) (actual time=0.024..644.350 rows=100000 loops=1)
         SubPlan 1
           ->  Aggregate  (cost=11.54..11.55 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=100000)
                 ->  Bitmap Heap Scan on proposal p  (cost=4.30..11.53 rows=2 width=0) (actual time=0.002..0.002 rows=0 loops=100000)
                       Recheck Cond: (fkid_entity = e.pkid)
                       Heap Blocks: exact=9960
                       ->  Bitmap Index Scan on idx_fkid_entity_proposal  (cost=0.00..4.30 rows=2 width=0) (actual time=0.001..0.001 ro
ws=0 loops=100000)
                             Index Cond: (fkid_entity = e.pkid)
 Planning Time: 0.192 ms
 Execution Time: 2149.556 ms
(11 rows)

Time: 2150.167 ms (00:02.150)

rollback;

test case2: update base on condition with json type

begin;

explain analyze update entity e set jsondoc = jsondoc ||
jsonb_build_object('BeaOutstandingProposal',
(select count(1) || ' Proposals' from proposal p
where p.jsondoc ->> 'EntityId' = e.jsondoc ->>'EntityId'
));

QUERY PLAN                                                           
----------------------------------------
 Update on entity e  (cost=0.00..28916985.00 rows=100000 width=55) (actual time=516371.433..516371.433 rows=0 loops=1)
   ->  Seq Scan on entity e  (cost=0.00..28916985.00 rows=100000 width=55) (actual time=6.654..514437.362 rows=100000 loops=1)
         SubPlan 1
           ->  Aggregate  (cost=289.12..289.14 rows=1 width=32) (actual time=5.138..5.139 rows=1 loops=100000)
                 ->  Seq Scan on proposal p  (cost=0.00..289.00 rows=50 width=0) (actual time=4.938..5.135 rows=0 loops=100000)
                       Filter: ((jsondoc ->> 'EntityId'::text) = (e.jsondoc ->> 'EntityId'::text))
                       Rows Removed by Filter: 10000
 Planning Time: 0.839 ms
 Execution Time: 516371.565 ms
(9 rows)

rollback;

optimization

create index idx_entity_entityid ON entity USING btree((jsondoc ->> 'EntityId'));

create index idx_proposal_entityid ON proposal USING btree((jsondoc ->> 'EntityId'));

test case2: update base on condition with json type

begin;

explain analyze update entity e set jsondoc = jsondoc ||
jsonb_build_object('BeaOutstandingProposal',
(select count(1) || ' Proposals' from proposal p
where p.jsondoc ->> 'EntityId' = e.jsondoc ->>'EntityId'
));

QUERY PLAN                                                                     
------------------
 Update on entity e  (cost=0.00..9724075.07 rows=100000 width=55) (actual time=2986.270..2986.270 rows=0 loops=1)
   ->  Seq Scan on entity e  (cost=0.00..9724075.07 rows=100000 width=55) (actual time=0.032..967.366 rows=100000 loops=1)
         SubPlan 1
           ->  Aggregate  (cost=97.20..97.21 rows=1 width=32) (actual time=0.006..0.006 rows=1 loops=100000)
                 ->  Bitmap Heap Scan on proposal p  (cost=4.68..97.07 rows=50 width=0) (actual time=0.005..0.005 rows=0 loops=100000)
                       Recheck Cond: ((jsondoc ->> 'EntityId'::text) = (e.jsondoc ->> 'EntityId'::text))
                       Heap Blocks: exact=10151
                       ->  Bitmap Index Scan on idx_proposal_entityid  (cost=0.00..4.66 rows=50 width=0) (actual time=0.004..0.004 rows
=0 loops=100000)
                             Index Cond: ((jsondoc ->> 'EntityId'::text) = (e.jsondoc ->> 'EntityId'::text))
 Planning Time: 0.226 ms
 Execution Time: 2986.304 ms
(11 rows)

rollback;