Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1v18sf-004yxv-1L for pgsql-performance@arkaria.postgresql.org; Tue, 23 Sep 2025 19:40:53 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1v18sd-007gRT-N7 for pgsql-performance@arkaria.postgresql.org; Tue, 23 Sep 2025 19:40:51 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1v18sd-007gQz-4x for pgsql-performance@lists.postgresql.org; Tue, 23 Sep 2025 19:40:51 +0000 Received: from mx0b-00160c04.pphosted.com ([205.220.179.180]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1v18sX-002UVG-2A for pgsql-performance@lists.postgresql.org; Tue, 23 Sep 2025 19:40:50 +0000 Received: from pps.filterd (m0276500.ppops.net [127.0.0.1]) by mx0b-00160c04.pphosted.com (8.18.1.2/8.18.1.2) with ESMTP id 58NELtTK014190 for ; Tue, 23 Sep 2025 19:40:44 GMT DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= thomsonreuters.com; h=content-type:date:from:message-id :mime-version:subject:to; s=dkimtest; bh=cFh6zX6sCzLK30hjz5BPPLT 8F1+XYgU+Fc0NKwLkzl0=; b=DxRnT/JiJ3wOkK7NZOTKrIzMjVDmlaPmo6dF6lQ yEJPjUUVW9FJWT54mZx3eE/OHZ5RDn/ysIxkiCuG+usc1Ewe+areFaGzyLErGoKN 8HCsM/HSnmx+V7nLcNgYFBgHLdu32qtwbVXF7zFnE1764WXq3/TKSC07cZxmOw0g ZgwSqgdpDmbPyRHZSjZjNgf+1DxCRWXWhNnzdpReDgyn7mTqxnaLI0AkcnSMRx8w 35q26yN9VSTsMM1V8/0mlU/4N/dJoY1tfTKIcs7NCHJNHvIWdadZjrtUI+pf9lKT GtTk8sOzR3Y7YuZ/KeN+MBd/Q17ZcVyJ6lvXDPJrK51Hhxg== Received: from sn4pr2101cu001.outbound.protection.outlook.com (mail-southcentralusazon11012000.outbound.protection.outlook.com [40.93.195.0]) by mx0b-00160c04.pphosted.com (PPS) with ESMTPS id 49bnqhbrq9-1 (version=TLSv1.2 cipher=ECDHE-RSA-AES256-GCM-SHA384 bits=256 verify=NOT) for ; Tue, 23 Sep 2025 19:40:44 +0000 (GMT) ARC-Seal: i=1; a=rsa-sha256; s=arcselector10001; d=microsoft.com; cv=none; b=sPfYO1SZ8anxNHvlVNYxmxLFpwjCvPc2kE8mV4eFjtlVGS/j/qWoLIxNQYd8WN4ZR7fr1PUa3ujjV7FJb0FF9ATYaNqvoTVFYA9eaLXgig3US6E55GnEFNrhwsOncoOwpxY2RHDHGgsfIBp1auYiJaiKQxEneo82Lacjefp1n3/zsVnI78p/KNqeRV1uHq9SWb70GlKzKpK8IoSWdUm1VUwTXPjk/+KYYt6FRuaETNPIfsAznNdkT1BYubOP9wd06LjboBbp9wFLldr8Uv1oHtTXAwHpgxKvyinQvrF8cfhr0Qzd15u6L+/Wr90k02wGmv3IE916sHGLWc0nS1pJPw== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector10001; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-AntiSpam-MessageData-ChunkCount:X-MS-Exchange-AntiSpam-MessageData-0:X-MS-Exchange-AntiSpam-MessageData-1; bh=cFh6zX6sCzLK30hjz5BPPLT8F1+XYgU+Fc0NKwLkzl0=; b=YlTzErhFzDkl4d3eogumnNn1Hq9ST18tn8VTCZZfUSnJNnLLF+lXu7fUs77S3e1ijsFQHhb4Q/xBIrQvvm0S7zK6JmqKAer7DXifSfsYG3LDcXhSZFUtWToEu8TL/pGXLqZQaHGIvp7Z6ZdRBOU+ij16ZHyI0KdY5oy5hJTPCq48KYWQfDYYXHH7AmufuqfOxhWhkDLMkLntKdSNp/SCCXie5FnK9VP0SSjl08BAJwEYkeup1hKzvxhNn7pQiI0KMkOBw1lEuTxmYbG3o6ZSKq2zwILsYaSN6HD1wgeyisE2AZwN47NgQSsdX6Md5bZexSaJTyZZT+zyfZxoReq7mg== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=pass smtp.mailfrom=thomsonreuters.com; dmarc=pass action=none header.from=thomsonreuters.com; dkim=pass header.d=thomsonreuters.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=thomsonreuters.com; s=selector1; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=cFh6zX6sCzLK30hjz5BPPLT8F1+XYgU+Fc0NKwLkzl0=; b=C61DXXL1Mcct7UYihqqz8dcDeSZJ9K1/F7mj/j8eQmYu417iov6gD9cM+F3zj60QqKaUDI+hTjf4kal8ppYxZdaXTYIkS0/XeblJ3U2KfpOtgXAHmiSWPCwt6ME1pTMe6v7VB35D7hII71JRCyij4/w+mqtupOtdDf4W8r9I2wg= Received: from CH0PR03MB6034.namprd03.prod.outlook.com (2603:10b6:610:bd::18) by BY1PR03MB7309.namprd03.prod.outlook.com (2603:10b6:a03:52e::11) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.9160.9; Tue, 23 Sep 2025 19:40:41 +0000 Received: from CH0PR03MB6034.namprd03.prod.outlook.com ([fe80::314:3be1:ffdd:67f]) by CH0PR03MB6034.namprd03.prod.outlook.com ([fe80::314:3be1:ffdd:67f%6]) with mapi id 15.20.9160.008; Tue, 23 Sep 2025 19:40:40 +0000 From: "Dirschel, Steve" To: "pgsql-performance@lists.postgresql.org" Subject: Very expensive update to update a single row Thread-Topic: Very expensive update to update a single row Thread-Index: AdwswSEn+2/WcSGXR1aJv7WlCSISnQ== Date: Tue, 23 Sep 2025 19:40:40 +0000 Message-ID: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-ms-publictraffictype: Email x-ms-traffictypediagnostic: CH0PR03MB6034:EE_|BY1PR03MB7309:EE_ x-ms-office365-filtering-correlation-id: 617d5e88-e6f2-42b0-a208-08ddfad9141d x-ms-exchange-senderadcheck: 1 x-ms-exchange-antispam-relay: 0 x-microsoft-antispam: BCL:0;ARA:13230040|366016|376014|1800799024|38070700021|8096899003; x-microsoft-antispam-message-info: =?us-ascii?Q?dvp1fViOV5Zcb6m9ot2WSv2AGLp7XRD3+PSlJL+p1Mcojp2aJyweYXJk86JK?= =?us-ascii?Q?iWsNIZlBXIirA6ckWEhsHicIUn8fT0qGzQYw5P+Rr9l30zDmp6Qn/tHiDV+E?= =?us-ascii?Q?8Hy+E3fvmVG9pUz/xqLiwknFNeVpx6UYClbgMSWpFcCaVx2g4Wmw1U81oSVf?= =?us-ascii?Q?f8PrudRt3ILflJa63BnbpGFNnILpwsRrPr5G3ZQOQs7s7PnI0KZmCA4Sxf1T?= =?us-ascii?Q?sVx1WYOr//3t+aWxxju1yInTAWg8aOB+eMhLC7JZm3NsE7YP3tis86aR+wz/?= =?us-ascii?Q?GofhjIosMSj+JeKcejBlLlCre2fjfwziJ+M8v/TbAkQri05xukD5Q9zkPEGG?= =?us-ascii?Q?9mGStsNZ8QDMJy+NdqGum5US3M+O05MLQa7kepanITT9PvgoTjfCh4+QhJZS?= =?us-ascii?Q?KXFaWyGgGsOBMi0ppYVavVIqAmKCKGN+b1ZbzFJLbQVgibLVNdqHoRjeUGuR?= =?us-ascii?Q?Sw5gWSZ313KmXk4BD0rlyKoh/DwICudCW1RTwUsOysSREIF0R8fwa4c5hcFX?= =?us-ascii?Q?mvosiCNrq+dAOejobvys3AQZHIwxCUVxXBGDGSSMfy0A+rNku/+y8oSf27Wr?= =?us-ascii?Q?dDddOq8IQevEsvFhlTdnKwHnHUHPIM5/3ENV2+NNEZNaGYPc9eg18BPsmV1t?= =?us-ascii?Q?GltTWpfpcvusgbg7PJ+x2cy2dvbT9cPq0z0Qcc75B5az9xajtgj2bqlEx09l?= =?us-ascii?Q?esLtwFXRVU4ZLkniWFXLKTB6xdXtdQT+H6EbKP5j/xyo/ClJz7R4Boga0gYx?= =?us-ascii?Q?sdf6Kyz2CyoDXgK4KeGMyD5kA0I4+NyIJ3WlKzVtvyyGMv8CyAlxYYs6vd2F?= =?us-ascii?Q?N7pAkEcybhqITNBd5CWOsfKlYvsV+B0YIcoQFiO9DqN5tJnjXL4RX5IZ5f7A?= =?us-ascii?Q?/jS4sVBR6k4Yd98kxMb0lBg0Zv5mbSaO4EtVytUga/X6zivOYzaVD9JTJxcb?= =?us-ascii?Q?wbJHc6jqUrh1hj46E0Mid2KMmJ7Lv+FEL8HZw5eMJkCXWwGQDaWNXM8MUuVC?= =?us-ascii?Q?mrDUrTXSl2O/8w3atoxbZlWhW4YHscv9EqzUTiHRhGa22EIZYCm/+EOFQt1W?= =?us-ascii?Q?0GRianPVNX10jL5tjv4SJmQG+Rgty/RNdBMm1AzxcYkY/RoEZhwKWWyWxl3J?= =?us-ascii?Q?U2D6FLINC7w6Lila+pt/b8MMVkGT2PnoPvlEKFVYdbrF28E0hX/lpI/qhxOi?= =?us-ascii?Q?RRL9GbHfulIRlpw4udY1J3hYhr/LWbz2ufJfpi+BprcfvDJgX05wHzyx5H3W?= =?us-ascii?Q?E6OFL7W0PwRsJfvLmzUOQgFCxQ8H6nnEDAho9gn6BwomJp7mzf558n2QF43n?= =?us-ascii?Q?ArR4z5OdJfZye1zb8bZiseu/n1VrmZ5Jj58ouRGrk4ASO9ErUGmDZZGSJdMz?= =?us-ascii?Q?tIRuGYYKGJveRI5fIB2mIcKS3g40e4Ew6zEE54rnbF5AN5GUY9RNv5P0w7vh?= =?us-ascii?Q?tjypMYrmHx8/OA+2IHaEfON8OpgZfZnYJyRxbjAyAfOqtvDZJYXyRw=3D=3D?= x-forefront-antispam-report: CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:CH0PR03MB6034.namprd03.prod.outlook.com;PTR:;CAT:NONE;SFS:(13230040)(366016)(376014)(1800799024)(38070700021)(8096899003);DIR:OUT;SFP:1101; x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?us-ascii?Q?vYq5HJQMY7qTTcVvaTEL27jqosdFW9jd6wreDxfX76FoH6K8S+9IZXFAVJjY?= =?us-ascii?Q?6/L7B/sh6vNnnPlXvJUpQ2k8roHOutssD38gcm4TOlXI6q59TFnhM+esl8vf?= =?us-ascii?Q?y2q63t397vYeRI8Sq9z3BDvv2dmIG26l3iGNAJxAtnefPvInHQOuwdFTcR8Y?= =?us-ascii?Q?wMY4mpTM4V6qF1y1i1w9cd7/LefW004jkUMzGgLqH0Ndr8Qi+uFMbOUuzrNT?= =?us-ascii?Q?Z9F9A0tPnp/jbpJnLaNY3LQ+vENrG+s/af8LKzX8GscMAjxLzIrSY+Ja6CQG?= =?us-ascii?Q?UXgF3o2xkMtAEQ0RsLL5gWYzfx70VXnsLl4vDl5qKhBh5OtzobwtoNFCQVXo?= =?us-ascii?Q?LoN47CgkZT7fRk1i9Wohxs8Z5Bi7i07OE9e4K8fVAz61v9EJOsmvo0LDvyST?= =?us-ascii?Q?+8/lrwhmo0FPxhFRZkzCihPOJ1Oc7empu5GSIry+ctFT0wlofPTGvvqnoTDI?= =?us-ascii?Q?jIQbeH10bwCU+3QQZVS8RtltZ1t6S1YmnLXC0K+VpNkZHKOlZ/Vh2IjOEjk1?= =?us-ascii?Q?gEhr9L6o/LoSxIw0AgVZJzPS0ZV7aWkEMaQITsNHJd3OCNm2xcuLFUrQdWZn?= =?us-ascii?Q?zAZ/VC43fYw+8ka2EVNSv/1XtPTdTZXumePVfiN5n/FjdHoo3ZqZetuN8aw/?= =?us-ascii?Q?PTOkr3NRTC/PyfZgcbCgh2jrNyxPFslAft6+twsyTSqrjyBFJHjuHXuY6HKj?= =?us-ascii?Q?L1+kTe3DQif5Em1ex9vvCy/bImd7a5Is+RaKx66nTQ4UG6M307pAXEru735K?= =?us-ascii?Q?S6lTZUZ1z5XH3fFB79jcEns03UoU70XuzE2ZtbnUbmy21N0LdZIRRTXfF3gz?= =?us-ascii?Q?izs9mpVlhO+8w9YnLuVJ8rJ8WMNB802NVUqNe3vhfYX8Fjaw0hGtbWlUO8A5?= =?us-ascii?Q?Tt5/wHangyFFc0b+K1/JiK8TvllhX7vAN/Yc5FMoc321OJs+hu+lP9EnQUnn?= =?us-ascii?Q?8b7o5NkZkfAWbFcgbe5ZaBIIM2tI+3Sp40kd6gNcp8glPStexBldoZHnHdZB?= =?us-ascii?Q?NSU8shqAwQc45udBsHngINhx/YdPmID5OWtG+xMRcY09sCqAHC2q3AtzxdcG?= =?us-ascii?Q?uL0qiMRxFqni2OnvOazIEaO0E6QBeI6wa/O5Gg+OVRLB/WirjoCGGjIOwci4?= =?us-ascii?Q?v971s7yJEQHKqKpbATWOgMINeYnd1XBq6GQj6dqO3vPqXYEtayjQ8FEk4/E4?= =?us-ascii?Q?HdXh+/K/Tnz+jNYmW1BoWnyeKGNCnYn5iMOIAWjJ+ZRMYcsvPatblRpdswV6?= =?us-ascii?Q?AnMPc4Cj6xLdom4CjiRj2rt/fPPsKm8qUi7fBE/Fhc0c1LEo1M7IbpQ4iETF?= =?us-ascii?Q?+Qeq6ccktM2eoDshRdffjzT8+uwAQ9wAqUeOHdXcOQsGj5v/KSsDgNcdOzMc?= =?us-ascii?Q?XlB+mQBY14Pt6Q9Ffn8o5vpqeLrFP4f6sWEB5RFhFvFIdCtHuhTBC9oBwtht?= =?us-ascii?Q?oyiTBWcEXUqI/OqbyJg39wx/QPNvLg0pyLGctNjfmCUlrSKzjrAuAQER8J/6?= =?us-ascii?Q?jIPtbyYQXg9Wb0Lb+Sy0cjecio85TI+eDm/d1fZQyyIZQ6lQebVn2BIBpqZZ?= =?us-ascii?Q?o8QR9ArPjVTgU3CrAHIG26O/O3A0J4QxO3Y1BG7QnEcmt2BdqUfrjUwciqUJ?= =?us-ascii?Q?CA=3D=3D?= Content-Type: multipart/alternative; boundary="_000_CH0PR03MB6034E9F7009F44CBE62A716CFA1DACH0PR03MB6034namp_" MIME-Version: 1.0 X-MS-Exchange-AntiSpam-ExternalHop-MessageData-ChunkCount: 1 X-MS-Exchange-AntiSpam-ExternalHop-MessageData-0: +bo+Tvq3p9Q8KIFPsQ0hD7frziY9eiicA7Tbm0Uf3U95LrESumnUz6bSE9/F+rSG7BhrSHciRN0whUvFkG0W6n7YrRrIpaC/aKGxqtK9QLOf6wdTCW6ckMzvRDl5/CbQsPMNZhgHfujyLaOWdMJBi699XwjJShtcQTSkAETRB8TgdM9lHKPaeEUM/TgYHPI/mc28CH7fXJBjxN5Rw+PrDYcuCOnkmZmarPGy9RcVYKVdHcB2TzqT3TK3JvU4LHHMRhgNiRPsmo3HrmQVoFzZF6zI8yxlYpykZ8BOXap5R8/5flQuh0Hg8wXEA+JwkXBMipaX8FyhUqdAdArphJCaCvORFHKhxt0sya8Pd0zgTLMpMkeGPvlZX5FSrtu5ngp7vJlNAPTbkwEs5AvKhN9VDXroTgLD8pmjhNSzgVZTC4qu1AgRuFyTVMxB5Mlgz/wODUl+/w8a6LYoc85d59W3+DAPGv46Oh3OtSRnAC71oqwtLCaY9+3M5ZUkQQxFeXl0ufLIzQ5yCLBMJ3hW/VydNeC8qTf617srqpcfgKcLLjgYSNJoRf5bJaNxpct6oR4QzF7/11TUFkWLfQ6vekWbgSFrCJ91DNSKZIb6i1hUZT/0O03HBSmT0YBLsJxL9j9M7V/Ggu5cTf9eU4crGMU8fq0ABMQe6FOJNEcUY1xhQzA= X-OriginatorOrg: thomsonreuters.com X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-AuthSource: CH0PR03MB6034.namprd03.prod.outlook.com X-MS-Exchange-CrossTenant-Network-Message-Id: 617d5e88-e6f2-42b0-a208-08ddfad9141d X-MS-Exchange-CrossTenant-originalarrivaltime: 23 Sep 2025 19:40:40.8736 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 62ccb864-6a1a-4b5d-8e1c-397dec1a8258 X-MS-Exchange-CrossTenant-mailboxtype: HOSTED X-MS-Exchange-CrossTenant-userprincipalname: guX2ie8/UFR+1Wz+jPJHBCQAANeL/kPPwX2OE6OJkvOuutWAZ0PRi8qOQhu/TfWTWa3CSfTEcKdRMmhT2xvMnvc+SR5IhUmHRkEeEh53ISbBVfOVNnR8RoyQ9jBI9IAy X-MS-Exchange-Transport-CrossTenantHeadersStamped: BY1PR03MB7309 X-Authority-Analysis: v=2.4 cv=eM0TjGp1 c=1 sm=1 tr=0 ts=68d2f7bc cx=c_pps a=PXpErafpzP6WDESZDYpOyA==:117 a=z/mQ4Ysz8XfWz/Q5cLBRGdckG28=:19 a=lCpzRmAYbLLaTzLvsPZ7Mbvzbb8=:19 a=wKuvFiaSGQ0qltdbU6+NXLB8nM8=:19 a=Ol13hO9ccFRV9qXi2t6ftBPywas=:19 a=xqWC_Br6kY4A:10 a=yJojWOMRYYMA:10 a=pKFMK-pTKUcA:10 a=awlg0vDVAAAA:8 a=NIlUIi7oXp4oVj5Kn48A:9 a=CjuIK1q_8ugA:10 a=pAs9YAABlUgA:10 a=tMCeSvlf9uQA:10 a=yMhMjlubAAAA:8 a=SSmOFEACAAAA:8 a=I02FLU-FqpMl8EkQ6VQA:9 a=a2PJteuUOIH-8X9G:21 a=gKO2Hq4RSVkA:10 a=UiCQ7L4-1S4A:10 a=hTZeC7Yk6K0A:10 a=frz4AuCg-hUA:10 X-Proofpoint-Spam-Details-Enc: AW1haW4tMjUwOTIzMDA1MSBTYWx0ZWRfX6hjuzYNy3gzR jVBB6Z7NI3rCgzruMUxvnCWa/KD80UUwpMuo6BpVpjXCKL6PdYZjZgBDvq+hFgHwIF1OyqL11Rg hmcBAPYsV+vJm6TQSWCTmlPjyQ/aXYRGnox0OjyKfKF2j9z1/Vly9zd2A1v0i/T09rq00mHS0Aw R5qYX0zysmZ2kmh2W3Ph9SimQ1Eyf5+AUPloZ4+un+uomho+JbiCMoMYKC/ahruQ6Ju5yX2F+U9 sZ71eAqdXHLUMY7R24zJ+sO1wVAVTJc9zdFc48n1wbYjdaLdRGJji+8AB57VB07PUWG3XIWYRoD TPqm19KYOoq73kf/c1PaJEFYikCa4WEVUdbLW8ZkqS42XMR/GzNi+JXpfAVTYC7Zl5ByCgR8XdK vK8pEPve X-Proofpoint-ORIG-GUID: ASHkxIwtZor3jbsVqs07fwS-jNCak8mP X-Proofpoint-GUID: ASHkxIwtZor3jbsVqs07fwS-jNCak8mP X-Proofpoint-Virus-Version: vendor=baseguard engine=ICAP:2.0.293,Aquarius:18.0.1117,Hydra:6.1.9,FMLib:17.12.80.40 definitions=2025-09-23_05,2025-09-22_05,2025-03-28_01 X-Proofpoint-Spam-Details: rule=outbound_notspam policy=outbound score=0 phishscore=0 impostorscore=0 spamscore=0 malwarescore=0 priorityscore=1501 suspectscore=0 adultscore=0 bulkscore=0 clxscore=1011 classifier=typeunknown authscore=0 authtc= authcc= route=outbound adjust=0 reason=mlx scancount=1 engine=8.19.0-2507300000 definitions=main-2509230051 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_CH0PR03MB6034E9F7009F44CBE62A716CFA1DACH0PR03MB6034namp_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Postgres RDS running in AWS. PostgreSQL 16.8 on aarch64-unknown-linux-gnu,= 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 | 'Ac= tive'::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'::tex= t)) "idx_request_gin_metadata" gin (metadata) "idx_request_meta_identifiers" btree ((metadata ->> 'identifiers'::text= )) Check constraints: "require_jsonb_data" CHECK (jsonb_typeof(data) =3D 'object'::text) "require_jsonb_metadata" CHECK (jsonb_typeof(metadata) =3D 'object'::te= xt) Disabled user triggers: audit_trigger_row AFTER INSERT OR DELETE OR UPDATE ON request FOR EACH = ROW EXECUTE FUNCTION audit.audit_event_processor('true') audit_trigger_stm AFTER TRUNCATE ON request FOR EACH STATEMENT EXECUTE = 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 column= s for these sample rows: select objectid, pg_column_size(data) data, pg_column_size(metadata) metada= ta, clientid from request r order by pg_column_size(data) desc limit 4; objectid data metadata clien= tid f9077561-4b40-41f8-8a52-3a2b83014982 22172347 842 V= HW <---- update #1 below 0103fe5d-7407-45b6-8966-32a8e4f6b67c 17814078 866 O= V9 <---- update #2 below 6009dbfc-8375-48b3-bbf6-921d48aad308 15971425 879 V= HW 3a0201bc-23f0-b648-8408-c21f81f6b974 14690119 785 V= HU <---- update #3 below *** Update the top row above which is the largest for the DATA JSONB column= : 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=3D74= .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 w= idth=3D418) (actual time=3D0.875..0.878 rows=3D1 loops=3D1) Index Cond: (objectid =3D 'f9077561-4b40-41f8-8a52-3a2b83014982'::b= pchar) 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=3D34= 663.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 w= idth=3D418) (actual time=3D0.908..0.912 rows=3D1 loops=3D1) Index Cond: (objectid =3D '0103fe5d-7407-45b6-8966-32a8e4f6b67c'::b= pchar) 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: S= ame 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=3D27= 683.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 w= idth=3D418) (actual time=3D0.021..0.033 rows=3D1 loops=3D1) Index Cond: (objectid =3D '0103fe5d-7407-45b6-8966-32a8e4f6b67c'::b= pchar) 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=3D18= 179.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 w= idth=3D418) (actual time=3D0.864..0.868 rows=3D1 loops=3D1) Index Cond: (objectid =3D '3a0201bc-23f0-b648-8408-c21f81f6b974'::b= pchar) 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=3D10= 553.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 w= idth=3D418) (actual time=3D0.022..0.032 rows=3D1 loops=3D1) Index Cond: (objectid =3D '3a0201bc-23f0-b648-8408-c21f81f6b974'::b= pchar) 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 da= ta read in memory. At 10 million that is 76 GB of data read in memory. Th= e entire table, indexes, toast is only 12 GB so what is this reading throug= h when it does 2 or 10 million shared block hits? The amount of work for t= he 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 mi= llions 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 info= rmation that may be privileged and/or confidential. If you are not an inten= ded recipient, please notify the sender by return e-mail and delete this e-= mail and any attachments. Certain required legal entity disclosures can be = accessed on our website: https://www.thomsonreuters.com/en/resources/disclo= sures.html --_000_CH0PR03MB6034E9F7009F44CBE62A716CFA1DACH0PR03MB6034namp_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Postgres RDS runnin= g in AWS.  PostgreSQL 16.8 on aarch64-unknown-linux-gnu, compiled by g= cc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-17), 64-bit

 

Table definition

 

Table: request

      Column   &nb= sp;  |          &nbs= p; Type           &n= bsp; | Collation | Nullable |    Default

------------------+-----------------------------+---------= --+----------+----------------

objectid         |= character(36)          &= nbsp;    |        &n= bsp;  | not null |

data         =     | jsonb        &= nbsp;           &nbs= p;  |           | no= t null | '{}'::jsonb

clientid         |= character(3)          &n= bsp;     |       &nb= sp;   | not null |

active        &nbs= p;  | integer         &nb= sp;           | &nbs= p;         | not null |<= /span>

productid        | inte= ger            =          |    &= nbsp;      | not null |

checkoutbyuid    | character(100) &nbs= p;            | = ;          |   =        |

checkoutdatetime | timestamp without time zone | &nbs= p;         |    = ;      |

metadata         |= jsonb           &nb= sp;           | &nbs= p;         | not null | '{}'::jsonb=

search_vector    | tsvector  &nbs= p;            &= nbsp;    |        &n= bsp;  |          |

requeststate     | text  &nb= sp;            =          |    &= nbsp;      | not null | 'Active'::text<= /span>

Indexes:

    "requestkey" PRIMARY KEY, btr= ee (objectid, productid)

    "idx_request_gin_data" gin (d= ata)

    "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&= quot; gin ((metadata -> 'identifiers'::text))

    "idx_request_gin_metadata" gi= n (metadata)

    "idx_request_meta_identifiers"= ; btree ((metadata ->> 'identifiers'::text))

Check constraints:

    "require_jsonb_data" CHECK (j= sonb_typeof(data) =3D 'object'::text)

    "require_jsonb_metadata" CHEC= K (jsonb_typeof(metadata) =3D 'object'::text)

Disabled user triggers:

    audit_trigger_row AFTER INSERT OR DELET= E OR UPDATE ON request FOR EACH ROW EXECUTE FUNCTION audit.audit_event_proc= essor('true')

    audit_trigger_stm AFTER TRUNCATE ON req= uest FOR EACH STATEMENT EXECUTE 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    &= nbsp; 

total_bytes     12,952,428,544 

index_bytes     6,088,523,776 

toast_bytes     6,614,458,368

table_bytes     249,446,400=

total         &nbs= p;  12 GB

index         &nbs= p;  5806 MB

toast         &nbs= p;  6308 MB

table         = ;  238 MB

         &nbs= p;              = ;               = ;       

 

We are updating som= e sample rows.  Here are the sizes of the 2 JSONB columns for these sa= mple rows:

 

select objectid, pg_column_size(data) data, pg_column_size= (metadata) metadata,  clientid

  from request r

 order by  pg_column_size(data)  desc

 limit 4;

 

objectid        &n= bsp;            &nbs= p;            &= nbsp;      data      metadata&= nbsp;  clientid

f9077561-4b40-41f8-8a52-3a2b83014982    &nb= sp;        22172347   842 = ;          VHW  &nbs= p;     <---- update #1 below

0103fe5d-7407-45b6-8966-32a8e4f6b67c    &nb= sp;        17814078   866 = ;          OV9  &nbs= p;     <---- update #2 below

6009dbfc-8375-48b3-bbf6-921d48aad308    &nb= sp;        15971425   879 = ;          VHW

3a0201bc-23f0-b648-8408-c21f81f6b974    &nb= sp;        14690119   785 = ;          VHU  &nbs= p;     <---- update #3 below

 

 

*** Update the top = row above which is the largest for the DATA JSONB column:=

 

explain(analyze, buffers)

UPDATE request

SET

     checkOutByUID =3D 'shivatest',

     CheckOutDateTime =3D '2022-01-10T= 16: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=3D74.678..74.679 rows=3D0 loops=3D1)

  Buffers: shared hit=3D2818 read=3D1 dirtied=3D1

  I/O Timings: shared read=3D0.835<= /p>

  ->  Index Scan using requestkey on request&= nbsp; (cost=3D0.42..8.44 rows=3D1 width=3D418) (actual time=3D0.875..0.878 = rows=3D1 loops=3D1)

        Index Cond: (ob= jectid =3D 'f9077561-4b40-41f8-8a52-3a2b83014982'::bpchar)

        Buffers: shared= hit=3D3 read=3D1

        I/O Timings: sh= ared read=3D0.835

Planning Time: 0.093 ms

Execution Time: 74.707 ms

 

*** 2818 shared blo= ck hits, 1 read, 1 dirtied.  Excellent.

 

 

*** Update the next= largest row

 

explain(analyze, buffers) UPDATE request=

SET

     checkOutByUID =3D 'shivatest',

     CheckOutDateTime =3D '2022-01-10T= 16: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=3D34663.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&= nbsp; (cost=3D0.42..8.44 rows=3D1 width=3D418) (actual time=3D0.908..0.912 = rows=3D1 loops=3D1)

        Index Cond: (ob= jectid =3D '0103fe5d-7407-45b6-8966-32a8e4f6b67c'::bpchar)

        Buffers: shared= hit=3D3 read=3D1

        I/O Timings: sh= ared read=3D0.868

Planning Time: 0.095 ms

Execution Time: 34663.071 ms

 

Wow-  10 milli= on shared block reads...  90k dirtied...  update it again:  = Same deal with shared blocks at 10M.  Dirtied came down but shared blo= cks is still extremely high.

 

Update on request  (cost=3D0.42..8.44 rows=3D0 width= =3D0) (actual time=3D27683.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&= nbsp; (cost=3D0.42..8.44 rows=3D1 width=3D418) (actual time=3D0.021..0.033 = rows=3D1 loops=3D1)

        Index Cond: (ob= jectid =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-10T= 16: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=3D18179.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&= nbsp; (cost=3D0.42..8.44 rows=3D1 width=3D418) (actual time=3D0.864..0.868 = rows=3D1 loops=3D1)

        Index Cond: (ob= jectid =3D '3a0201bc-23f0-b648-8408-c21f81f6b974'::bpchar)

        Buffers: shared= hit=3D3 read=3D1

        I/O Timings: sh= ared 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=3D10553.022..10553.023 rows=3D0 loops=3D1)

  Buffers: shared hit=3D2159422 dirtied=3D17

  ->  Index Scan using requestkey on request&= nbsp; (cost=3D0.42..8.44 rows=3D1 width=3D418) (actual time=3D0.022..0.032 = rows=3D1 loops=3D1)

        Index Cond: (ob= jectid =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 shar= ed block reads with an 8k block size that is 16 GB of data read in memory.&= nbsp; 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 through 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 w= hen it's going through millions of blocks in memory.

 

Any insight would b= e much appreciated.

 

Regards<= /span>

Steve

 

This e-mail is for the sole use of the intended recipient and contains info= rmation that may be privileged and/or confidential. If you are not an inten= ded recipient, please notify the sender by return e-mail and delete this e-= mail and any attachments. Certain required legal entity disclosures can be accessed on our website: https://= www.thomsonreuters.com/en/resources/disclosures.html --_000_CH0PR03MB6034E9F7009F44CBE62A716CFA1DACH0PR03MB6034namp_--