MIME-Version: 1.0
References: 
 <CH0PR03MB6034E9F7009F44CBE62A716CFA1DA@CH0PR03MB6034.namprd03.prod.outlook.com>
In-Reply-To: 
 <CH0PR03MB6034E9F7009F44CBE62A716CFA1DA@CH0PR03MB6034.namprd03.prod.outlook.com>
From: Sean M <sean.f.massey@gmail.com>
Date: Wed, 24 Sep 2025 07:12:58 +1000
Message-ID: 
 <CAJfn5C3pN723GJ2vLMjO6VihbmhOkBxgFZ=N99r8_DaVoZMYUg@mail.gmail.com>
Subject: Re: Very expensive update to update a single row
To: "Dirschel, Steve" <steve.dirschel@thomsonreuters.com>
Cc: "pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAJfn5C3pN723GJ2vLMjO6VihbmhOkBxgFZ%3DN99r8_DaVoZMYUg%40mail.gmail.com>
Precedence: bulk

Hi,

My first thought is pending list costs associated with the GIN indexes.
https://pganalyze.com/blog/gin-index

You may be able to use pageinspect's function gin_metapage_info to see what=
 the
https://www.postgresql.org/docs/16/pageinspect.html#PAGEINSPECT-GIN-FUNCS
https://gitlab.com/gitlab-com/gl-infra/production-engineering/-/issues/1438=
6

Are you seeing HOT updates on the good example but not able to get HOT
updates on the bad examples so all indexes need to get updated? You
are updating checkOutByUID and CheckOutDateTime which don't appear to
be indexes so HOT updates should be possible. Lowering the fill factor
on the table may allow more HOT updates. If you change the fill factor
you need to rebuild the table using pg_repack, etc. You could check
for hot updates on the table with something like this.
Select n_tup_upd, n_tup_hot_upd, round(100.0*n_tup_hot_upd /
NULLIF(n_tup_upd,0), 1) as hot_pct
from pg_stat_user_tables
where relname =3D 'request';

https://www.crunchydata.com/blog/postgres-performance-boost-hot-updates-and=
-fill-factor
https://pganalyze.com/blog/5mins-postgres-performance-HOT-updates-CLUSTER

HTH.

On Wed, 24 Sept 2025 at 05:41, Dirschel, Steve
<steve.dirschel@thomsonreuters.com> wrote:
>
> Postgres RDS running in AWS.  PostgreSQL 16.8 on aarch64-unknown-linux-gn=
u, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-17), 64-bit
>
>
>
> Table definition
>
>
>
> Table: request
>
>       Column      |            Type             | Collation | Nullable | =
   Default
>
> ------------------+-----------------------------+-----------+----------+-=
---------------
>
> objectid         | character(36)               |           | not null |
>
> data             | jsonb                       |           | not null | '=
{}'::jsonb
>
> clientid         | character(3)                |           | not null |
>
> active           | integer                     |           | not null |
>
> productid        | integer                     |           | not null |
>
> checkoutbyuid    | character(100)              |           |          |
>
> checkoutdatetime | timestamp without time zone |           |          |
>
> metadata         | jsonb                       |           | not null | '=
{}'::jsonb
>
> search_vector    | tsvector                    |           |          |
>
> requeststate     | text                        |           | not null | '=
Active'::text
>
> Indexes:
>
>     "requestkey" PRIMARY KEY, btree (objectid, productid)
>
>     "idx_request_gin_data" gin (data)
>
>     "idx_request_gin_data_i" gin ((data -> 'i'::text))
>
>     "idx_request_gin_data_r" gin ((data -> 'r'::text))
>
>     "idx_request_gin_data_t" gin ((data -> 't'::text))
>
>     "idx_request_gin_meta_identifiers" gin ((metadata -> 'identifiers'::t=
ext))
>
>     "idx_request_gin_metadata" gin (metadata)
>
>     "idx_request_meta_identifiers" btree ((metadata ->> 'identifiers'::te=
xt))
>
> Check constraints:
>
>     "require_jsonb_data" CHECK (jsonb_typeof(data) =3D 'object'::text)
>
>     "require_jsonb_metadata" CHECK (jsonb_typeof(metadata) =3D 'object'::=
text)
>
> Disabled user triggers:
>
>     audit_trigger_row AFTER INSERT OR DELETE OR UPDATE ON request FOR EAC=
H ROW EXECUTE FUNCTION audit.audit_event_processor('true')
>
>     audit_trigger_stm AFTER TRUNCATE ON request FOR EACH STATEMENT EXECUT=
E FUNCTION audit.audit_event_processor('true'
>
>
>
> Lots of gin indexes...
>
> Note the 2 triggers are disabled.
>
>
>
> Size of the table, indexes, toast:
>
>
>
> table_name      request
>
> row_estimate    227,771
>
> total_bytes     12,952,428,544
>
> index_bytes     6,088,523,776
>
> toast_bytes     6,614,458,368
>
> table_bytes     249,446,400
>
> total            12 GB
>
> index            5806 MB
>
> toast            6308 MB
>
> table           238 MB
>
>
>
>
>
> We are updating some sample rows.  Here are the sizes of the 2 JSONB colu=
mns for these sample rows:
>
>
>
> select objectid, pg_column_size(data) data, pg_column_size(metadata) meta=
data,  clientid
>
>   from request r
>
>  order by  pg_column_size(data)  desc
>
>  limit 4;
>
>
>
> objectid                                         data      metadata   cli=
entid
>
> f9077561-4b40-41f8-8a52-3a2b83014982             22172347   842          =
 VHW        <---- update #1 below
>
> 0103fe5d-7407-45b6-8966-32a8e4f6b67c             17814078   866          =
 OV9        <---- update #2 below
>
> 6009dbfc-8375-48b3-bbf6-921d48aad308             15971425   879          =
 VHW
>
> 3a0201bc-23f0-b648-8408-c21f81f6b974             14690119   785          =
 VHU        <---- update #3 below
>
>
>
>
>
> *** Update the top row above which is the largest for the DATA JSONB colu=
mn:
>
>
>
> explain(analyze, buffers)
>
> UPDATE request
>
> SET
>
>      checkOutByUID =3D 'shivatest',
>
>      CheckOutDateTime =3D '2022-01-10T16:24:10.2900556Z'
>
> WHERE
>
>      objectid =3D 'f9077561-4b40-41f8-8a52-3a2b83014982';
>
>
>
> Update on request  (cost=3D0.42..8.44 rows=3D0 width=3D0) (actual time=3D=
74.678..74.679 rows=3D0 loops=3D1)
>
>   Buffers: shared hit=3D2818 read=3D1 dirtied=3D1
>
>   I/O Timings: shared read=3D0.835
>
>   ->  Index Scan using requestkey on request  (cost=3D0.42..8.44 rows=3D1=
 width=3D418) (actual time=3D0.875..0.878 rows=3D1 loops=3D1)
>
>         Index Cond: (objectid =3D 'f9077561-4b40-41f8-8a52-3a2b83014982':=
:bpchar)
>
>         Buffers: shared hit=3D3 read=3D1
>
>         I/O Timings: shared read=3D0.835
>
> Planning Time: 0.093 ms
>
> Execution Time: 74.707 ms
>
>
>
> *** 2818 shared block hits, 1 read, 1 dirtied.  Excellent.
>
>
>
>
>
> *** Update the next largest row
>
>
>
> explain(analyze, buffers) UPDATE request
>
> SET
>
>      checkOutByUID =3D 'shivatest',
>
>      CheckOutDateTime =3D '2022-01-10T16:24:10.2900556Z'
>
> WHERE
>
>      objectid =3D '0103fe5d-7407-45b6-8966-32a8e4f6b67c';
>
>
>
> Update on request  (cost=3D0.42..8.44 rows=3D0 width=3D0) (actual time=3D=
34663.039..34663.040 rows=3D0 loops=3D1)
>
>   Buffers: shared hit=3D10787339 read=3D79854 dirtied=3D90531
>
>   I/O Timings: shared read=3D5083.198
>
>   ->  Index Scan using requestkey on request  (cost=3D0.42..8.44 rows=3D1=
 width=3D418) (actual time=3D0.908..0.912 rows=3D1 loops=3D1)
>
>         Index Cond: (objectid =3D '0103fe5d-7407-45b6-8966-32a8e4f6b67c':=
:bpchar)
>
>         Buffers: shared hit=3D3 read=3D1
>
>         I/O Timings: shared read=3D0.868
>
> Planning Time: 0.095 ms
>
> Execution Time: 34663.071 ms
>
>
>
> Wow-  10 million shared block reads...  90k dirtied...  update it again: =
 Same deal with shared blocks at 10M.  Dirtied came down but shared blocks =
is still extremely high.
>
>
>
> Update on request  (cost=3D0.42..8.44 rows=3D0 width=3D0) (actual time=3D=
27683.165..27683.166 rows=3D0 loops=3D1)
>
>   Buffers: shared hit=3D10846051 read=3D145 dirtied=3D1189
>
>   I/O Timings: shared read=3D73.607
>
>   ->  Index Scan using requestkey on request  (cost=3D0.42..8.44 rows=3D1=
 width=3D418) (actual time=3D0.021..0.033 rows=3D1 loops=3D1)
>
>         Index Cond: (objectid =3D '0103fe5d-7407-45b6-8966-32a8e4f6b67c':=
:bpchar)
>
>         Buffers: shared hit=3D6
>
> Planning Time: 0.095 ms
>
> Execution Time: 27683.197 ms
>
>
>
>
>
> *** Update the 4th largest row:
>
>
>
> explain(analyze, buffers) UPDATE request
>
> SET
>
>      checkOutByUID =3D 'shivatest',
>
>      CheckOutDateTime =3D '2022-01-10T16:24:10.2900556Z'
>
> WHERE
>
>      objectid =3D '3a0201bc-23f0-b648-8408-c21f81f6b974';
>
>
>
>
>
> Update on request  (cost=3D0.42..8.44 rows=3D0 width=3D0) (actual time=3D=
18179.058..18179.059 rows=3D0 loops=3D1)
>
>   Buffers: shared hit=3D2138731 read=3D37045 dirtied=3D50800
>
>   I/O Timings: shared read=3D6030.123
>
>   ->  Index Scan using requestkey on request  (cost=3D0.42..8.44 rows=3D1=
 width=3D418) (actual time=3D0.864..0.868 rows=3D1 loops=3D1)
>
>         Index Cond: (objectid =3D '3a0201bc-23f0-b648-8408-c21f81f6b974':=
:bpchar)
>
>         Buffers: shared hit=3D3 read=3D1
>
>         I/O Timings: shared read=3D0.825
>
> Planning Time: 0.085 ms
>
> Execution Time: 18179.090 ms
>
>
>
> And run it a second time:
>
>
>
> Update on request  (cost=3D0.42..8.44 rows=3D0 width=3D0) (actual time=3D=
10553.022..10553.023 rows=3D0 loops=3D1)
>
>   Buffers: shared hit=3D2159422 dirtied=3D17
>
>   ->  Index Scan using requestkey on request  (cost=3D0.42..8.44 rows=3D1=
 width=3D418) (actual time=3D0.022..0.032 rows=3D1 loops=3D1)
>
>         Index Cond: (objectid =3D '3a0201bc-23f0-b648-8408-c21f81f6b974':=
:bpchar)
>
>         Buffers: shared hit=3D6
>
> Planning Time: 0.093 ms
>
> Execution Time: 10553.056 ms
>
>
>
> 2.1 million shared block reads, the first update did 50k dirtied...
>
>
>
> At 2.1 million shared block reads with an 8k block size that is 16 GB of =
data read in memory.  At 10 million that is 76 GB of data read in memory.  =
The entire table, indexes, toast is only 12 GB so what is this reading thro=
ugh when it does 2 or 10 million shared block hits?  The amount of work for=
 the first update which did 2818 shared block reads is what I'd expect but =
I have no clue how to determine what this is doing when it's going through =
millions of blocks in memory.
>
>
>
> Any insight would be much appreciated.
>
>
>
> Regards
>
> Steve
>
>
>
> This e-mail is for the sole use of the intended recipient and contains in=
formation that may be privileged and/or confidential. If you are not an int=
ended recipient, please notify the sender by return e-mail and delete this =
e-mail and any attachments. Certain required legal entity disclosures can b=
e accessed on our website: https://www.thomsonreuters.com/en/resources/disc=
losures.html