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.96) (envelope-from ) id 1wHOG0-0078Jq-2K for pgsql-performance@arkaria.postgresql.org; Mon, 27 Apr 2026 15:52:25 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wHOFx-00ERSY-2M for pgsql-performance@arkaria.postgresql.org; Mon, 27 Apr 2026 15:52:21 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wHOFw-00ERSG-2c for pgsql-performance@lists.postgresql.org; Mon, 27 Apr 2026 15:52:21 +0000 Received: from mx0b-002a6b01.pphosted.com ([67.231.153.219]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1wHOFn-000000033Gk-3tVt for pgsql-performance@lists.postgresql.org; Mon, 27 Apr 2026 15:52:14 +0000 Received: from pps.filterd (m0098284.ppops.net [127.0.0.1]) by mx0b-002a6b01.pphosted.com (8.18.1.11/8.18.1.11) with ESMTP id 63RELiTr3578381 for ; Mon, 27 Apr 2026 15:52:11 GMT DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=bestbuy.com; h= content-type:date:from:in-reply-to:message-id:mime-version :references:subject:to; s=S1-20230630; bh=Ht7J1szGfOmNl0x1aPge8i nHc1AHQnC/gfhONW8JMck=; b=YKzXIikordKaGcHqXEWHu18E0rEk1rWfiq6rvC MxNUcOyr6CwLguB9P+7/J74iRuaFDqSwtn4oxAr0GIwZ5MGMkbk0aUH7z6JLExpi pwwd0T1dCLy1e1F9xlX6euucZK0rG39ftduz6LTxITE2dFdQ169pmNtHDsf0ml0K uDuHkRG093zpdg/YPTvtic8ig4+MWAQTStVsJol0GGVuEwq93GR3puIDTpzobCLt x85oaoYIHhkdeGNds1t2sp6/+BqmbknxFbFXh9oWOzE+RXG+KQ9+4wtGJOfbxNig BRGgToUQ3FCyH4mdyxbkWg18sE0EBIxv9hzracNvRUyp7xhQ== Received: from sj2pr03cu001.outbound.protection.outlook.com (mail-westusazon11012057.outbound.protection.outlook.com [52.101.43.57]) by mx0b-002a6b01.pphosted.com (PPS) with ESMTPS id 4dt79pacs2-1 (version=TLSv1.3 cipher=TLS_AES_256_GCM_SHA384 bits=256 verify=NOT) for ; Mon, 27 Apr 2026 15:52:10 +0000 (GMT) ARC-Seal: i=1; a=rsa-sha256; s=arcselector10001; d=microsoft.com; cv=none; b=Aq4l+z9xDpfBn5aGR8TmmMyq3oXydVkIsq9ZYbhDxrEZv5b165mZ/cfM8FDerUKP+zO1Gj6d1BTC+TENf38wfuMc0f2PyPyp7HJ+hcnj0GlTpTdqFK0KUnCvNdrxV8MtlExK8d1i4Pwsb7o6iUoZ8UrLUfXBz6GPu3g6LaEOcpSPbQiMPhwy+eLWazYWIjwEklepwzkeK8R+e25M9SQ6hZUlBxTijGYAXiPveyfNTzm9uXADmPRJ5VLxEGROreH86vmOP/NpObX0gLRhXHFKiBn+8eAXZcq7T0AqYztNkk8MCUHGsLKcqnucDeOr7f7bwMyMFSFdXkF8Wns7ziGfIA== 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=Ht7J1szGfOmNl0x1aPge8inHc1AHQnC/gfhONW8JMck=; b=JB76BfZ/Fd11EJ9GZzwsmYmhxTR4Arb9xrtfhga85s0ceXmf4gte9+xRm4XcrFOEE394Se9S3veFFAV54CYccialo+TDpjbTxKMK1sbt/0Yr/Rv5vsvDOPr4+hzK7TrAK7DDCMTuv8bkK1tcF1rrAUBG4BmujUInpr9KtPpkpJ+QxbOLNldUQ/OXsZxB0I9BpqcC/3Rd6TXd8HeX1hIil4WCrVbqMx7G2hu2JosHWqjvX9oMXoiyF3G3/WG3CHRrI4f8WQG9uZsHSn/1eK3nccdZCJPrAYZiBEV+2+x2HVZxPeooYEkjh5pTxFUUWkEVjuo2U+LMJysMCGmywpAU3w== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=pass smtp.mailfrom=bestbuy.com; dmarc=pass action=none header.from=bestbuy.com; dkim=pass header.d=bestbuy.com; arc=none Received: from BYAPR06MB4471.namprd06.prod.outlook.com (2603:10b6:a03:4b::28) by BY3PR06MB8083.namprd06.prod.outlook.com (2603:10b6:a03:3c6::19) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.9846.26; Mon, 27 Apr 2026 15:52:07 +0000 Received: from BYAPR06MB4471.namprd06.prod.outlook.com ([fe80::3079:706e:ea70:a376]) by BYAPR06MB4471.namprd06.prod.outlook.com ([fe80::3079:706e:ea70:a376%3]) with mapi id 15.20.9846.025; Mon, 27 Apr 2026 15:52:06 +0000 From: "Dirschel, Steve-CW" To: "pgsql-performance@lists.postgresql.org" Subject: Query performance Thread-Topic: Query performance Thread-Index: AQHc1l3MHW2pFas4mkKlIQ04Jl3BUQ== Date: Mon, 27 Apr 2026 15:52:06 +0000 Message-ID: References: In-Reply-To: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: msip_labels: MSIP_Label_e9c55f1f-f169-4db7-a264-a5084ccbb748_Enabled=True;MSIP_Label_e9c55f1f-f169-4db7-a264-a5084ccbb748_SiteId=135e8995-7d3b-4466-844b-a0d62ba5f495;MSIP_Label_e9c55f1f-f169-4db7-a264-a5084ccbb748_SetDate=2026-04-27T15:35:51.6654596Z;MSIP_Label_e9c55f1f-f169-4db7-a264-a5084ccbb748_Name=Confidential;MSIP_Label_e9c55f1f-f169-4db7-a264-a5084ccbb748_ContentBits=0;MSIP_Label_e9c55f1f-f169-4db7-a264-a5084ccbb748_Method=Standard x-ms-reactions: allow x-ms-publictraffictype: Email x-ms-traffictypediagnostic: BYAPR06MB4471:EE_|BY3PR06MB8083:EE_ x-ms-office365-filtering-correlation-id: c0687424-25f9-4518-e020-08dea474ef23 x-proofpoint-outbound: 2ABD5B1121654D948E7788CED1FE3D92 x-ms-exchange-senderadcheck: 1 x-ms-exchange-antispam-relay: 0 x-microsoft-antispam: BCL:0;ARA:13230040|376014|366016|1800799024|38070700021|8096899003|22082099003|18002099003|56012099003; x-microsoft-antispam-message-info: cQv0SLtdqN7EwFXP1PHXND+gx1gzLdWzIbDe8OwJsnFhxgewBEsPysOper0SnENSss2SdHJykjZcGvZlRNmOzSdvYnQxfnG+MlYt7LSi2ceP6x/73/M/naqZqc+j0TQN+IA+7MbiBfuPjkWpNghdkXQh7vGVrrsxAldqAWezcK5qJlp+HvSymzeius+qOZp612fCPwbRpZywyqBIgYD/vNl3SfK88mD37Tn8Y3NVmNy4K1YJZoVlXl34bzG9fcIJZectr8I7SKBp6s0fxFgSa2lzwTYAzJxvOiU+Q76loBVFG4vV4erGVYIQOPa0CFhxCEzPfDwy1TpL+RLA62glAi+pIshsFimJwhOGAIgH0boDS2EtYRLkuUso1jvpMBMlmoubJPQQ0tT5e4jHqZnURtUWzr1YMPdpU+K0vg3fpYqqoRz1j+IAzt8znTEAJCMncKEgTym4JyFnrLakNCFq1Sk5Yt0VnTnH+C8iG3bL0HRDCQ/emyAH/O2qeX/NO4tS0cMi944PaCNkJIdthJ0F7FmEf1JeNW/7+2NC99Te+Ck/tvYBWXeRkTGIzY2Cpgu3D+IGOd4QVu/J8KoxV7QBKcBFmEiJ+ZAteoYJDLF4HXoIfDnnLJ8ctX0cyVRu2W3QMdgQtUfWnA5g34RhNZHAUMNQTtGgPpTW+AyapwMcuqnTEt4/3KVSISgDKj+0Y/IDzJ6qdSSHNeIcOHZpXNIXnBsNzJwvsqb1ETUZGWhS1MrSIrNCsy2DGYvF1WFCLA29oacBRdNhL4fLEQXxNp4+9ni32DDy3abl/zyzJt33Mxg= x-forefront-antispam-report: CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:BYAPR06MB4471.namprd06.prod.outlook.com;PTR:;CAT:NONE;SFS:(13230040)(376014)(366016)(1800799024)(38070700021)(8096899003)(22082099003)(18002099003)(56012099003);DIR:OUT;SFP:1101; x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?Windows-1252?Q?6JuneBWy3OcWLAWSbD6eenuZmWBJqZ2oKzTiMqaXETV98pGOOrVRkzKL?= =?Windows-1252?Q?CBko6fV3XDnZlsKenuw+6vl5K3bHmGbgYx7LlSUes2PcZmQP5YSRDDx2?= =?Windows-1252?Q?OOzUHIinGn52XRWG3oYhuQ5vXnn6ytnVTOrw/wpRpOz+VB2NqPiQEvna?= =?Windows-1252?Q?zO4GDeqmuFbR/w30w/Ua127KbQ1uLcorRbdphO8jR14CIkb3CxmElOyr?= =?Windows-1252?Q?buSbWTclpNk8zgCcf16Jz/Mdd41xPKnqYFpICnHKkH/s+T2GxTRP5ZCa?= =?Windows-1252?Q?vOGlla9B624tKWx2mLpq4Q8YxTll9MXR5Zm5PivzRrtqeUDU9qHVX46W?= =?Windows-1252?Q?niXAAoZo9bwJe/0Oa2Xs9G2AvfUu/oJ5Y4CP0LQF2lPB3CCAcXv+Bd9w?= =?Windows-1252?Q?75Tjf965tzOUFBsICOGmq0NdgOkcLKaS/toDXCj1yier2gqzr0BsmnGP?= =?Windows-1252?Q?7W39rCheWbmgIaBv38uGTeIryQuBltx6DnxhPhgZoE7ODrV2Py5Aqc09?= =?Windows-1252?Q?q0B5Deo9mWB6HXXqBdUX5PT+SKa82B+jhrzNpU0kM9Wx050xkUHa8pTb?= =?Windows-1252?Q?5z4L5KU+YR7OK1CkCj8O7d8YDf5DvDDJCGZR+boCO6nYwnd39v4vlGeu?= =?Windows-1252?Q?p9rz/+RrtaDL/wAWF5/VBf3dKLmulTuzyl6a/wviPD0MSitTZfgq82O+?= =?Windows-1252?Q?ioqDicnx9cdC/cQbrjU4Ty8FswRVxruQcj33Txrd0PLi8SDPxchoKeQT?= =?Windows-1252?Q?NK+MMEMi3SV7rbGMsXlXITmURhH3rDXKDjciSqNzv8SBa9usuo2el40a?= =?Windows-1252?Q?L9n0cYi9UIS+uY0RUwyYCo+Jpw5o6s/A8GqjccNm+W7y5G5Q/wEHCKMR?= =?Windows-1252?Q?U5pCKYz0+bRxKEIwg6MoBLvM06ioyGhiSi3ETJc/7XouAd/lfTXboHyG?= =?Windows-1252?Q?VHcrk/S0JNB1a3qfX8KBYgKp8c/JJonQ1fbGc4Ce97JgDesuDiaKfdci?= =?Windows-1252?Q?k2n1KAfpR1O63C+zTlGBQL51C05fcloa5t32epBx+CCr0/wGSOcrOQoq?= =?Windows-1252?Q?MqawuXVUjitxPS9UhsYvPPVFJseR3VyUpBb8dvZJRqQ+IbALG/1q5Q9p?= =?Windows-1252?Q?KGa5V4uAaEtPH8rDtqRoqifyK5CyhWcrkhoasg4Ivvc5x/2n/+uZ9ysv?= =?Windows-1252?Q?HJDmztezzPedRtAw7GRf4Htv+jyi+OUG0LB3ZEL150TlC7ItxPhQPdMx?= =?Windows-1252?Q?XjXuwhQYPn6ZHsQoByqyuuAyAaDNCYdyVJKM19IYDt9+mehyVTlC9DdM?= =?Windows-1252?Q?/TKHfypVvAsOdFcwsV8y+ggvuFceH6N/5ozM5CZzgOmUvIpt7t/Bj3MK?= =?Windows-1252?Q?QA1yi4eFYPMUT5K3gsHyIy9piqIKDF9DLM7C9nC/zxvhSraI+SrlkUoe?= =?Windows-1252?Q?hFW6MQj2qKq/QeL1dWinDMwVw4wEUEkLhOYNIw5STbdrPVO16RXXxOJ5?= =?Windows-1252?Q?3rHCoizCJ1zpIU6P7JKm0Z4+tpsTcUtHwfxyfmfp3/mzisag10mzi9kL?= =?Windows-1252?Q?bUI1KsMmiveOMGHhuwqJAHaypIgHLhiQhBBCMtKeSpB28DLMp3bI4P0b?= =?Windows-1252?Q?sREhwu+wMvACvfnAd7//wGElfTMZ9kv3aUIfo29I9bK1Hcf7kn3hutBl?= =?Windows-1252?Q?woAzmCbmpTxlLSb9VIDl9l6r1YTDO/B/qGSkVFEjSpWnF4NYhfcxi/M+?= =?Windows-1252?Q?2nSF5rotzoytzRs0sNpK0K0CjfNB64Vhlp13Da38fwzGEl+k1Dmos83H?= =?Windows-1252?Q?3KP4rUx88Z9HPiDo4oDQ7HoFRSLIW3XEDgKGRLFRglA2KHznh7kuO+2l?= =?Windows-1252?Q?bjF4Z5lRa8tC+cpYTmufygHDztEVYBUnH9w=3D?= Content-Type: multipart/alternative; boundary="_000_BYAPR06MB447199DE1FCF363EF389EF1A8A362BYAPR06MB4471namp_" MIME-Version: 1.0 X-Exchange-RoutingPolicyChecked: NkAV81EF/CwaBBjkxKYloS6ZOo0qU/uM4qS9tsiLcSJg5VtNO3vNLZHOE9SUjTykuhs1ct7zWmqd9TEEiz+ye+MHVYsEngRwT6X45bySCqaoGuiYCwTyzW7IyNwxa+vjqdK6QmaNGBHEwn1i9M6djLl7i9uoaUmPkD2az/vdrjptWONN4tKQYGrr/20UeuZRxInLjtTAO9YebqFMW40WetWtT7fj1EHC3k5h7DmW75EoCeYufpgsuigLPbCxrEivygROtixxS8q7dzv2xPO5uNcGUDoBVnl+4yTivFmBxO4HxB+dRxbSd9js/z6lMxntR4Te72VKAUFXFWljomBtxw== X-MS-Exchange-AntiSpam-ExternalHop-MessageData-ChunkCount: 1 X-MS-Exchange-AntiSpam-ExternalHop-MessageData-0: dlM1smqJutnCKo4dpU1PcCA+bXyMzltyQ0AY/CaclwcaQj9ULPS9f3mJofO7Nos4aBytnYnDHajZR/qKJM+Kn8qxuhvYrqsjfMgJv7YHv1OCkLa2gMWJlOIXopLMm7Ux0fdUOpnvbLh0g80HTXU+1DuDhxZYIpj8oqkabWsH7V8aaMiBCObDrzzM4t1khn0y+CkAmVj9jpxDoz0LHZzd50jUJ7pOGQ9hTH73vkvzwH5U4UJVWIJeGfAoP6JuWWFiVr3yCQ+0g+spD+lVZhlF23QsuPUxvA+L6nhgh8+9+satglRLO8CL8Zi7+4rA1/HS7xt6aZ0jzc9F+reuvqSu7fXRouanBHhNjrhcprEuU/XSvJUSIP24J4tHlZCNK1NgfOGZuxKivFfj6gUOdzQO+xMysie6IUerg/p3xeadHKoXt8Jfy4+MWvnszlPD6svRtnSnVaJmpCMJ7o3bq4Eb3HX6PMZg3ax0fqWapXVM/RJeyiO/n1GmSyJMSXPUw8UlKhTHIHCjhmswwX6DlrhGzqs9dB9NkyyxpIqvLuY32TwIXQHh4oUrlxz9fQkIuu9gF94qmZCeM7hPbN42hiIWgyqwMWxhbrhwLtNW40vpH2UJoMC7h6HkeZB/IWRLOPMZ X-OriginatorOrg: BestBuy.com X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-AuthSource: BYAPR06MB4471.namprd06.prod.outlook.com X-MS-Exchange-CrossTenant-Network-Message-Id: c0687424-25f9-4518-e020-08dea474ef23 X-MS-Exchange-CrossTenant-originalarrivaltime: 27 Apr 2026 15:52:06.7869 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 135e8995-7d3b-4466-844b-a0d62ba5f495 X-MS-Exchange-CrossTenant-mailboxtype: HOSTED X-MS-Exchange-CrossTenant-userprincipalname: BM3wXEyydshPOZT72K5UdEsQwLqMWrGzJW/VDEV7lkNjz2IOmq+c9Mf+a/R5fRdCRL6fqbyeJbLvJxf1dHqlcg== X-MS-Exchange-Transport-CrossTenantHeadersStamped: BY3PR06MB8083 X-MS-Exchange-CrossPremises-AuthAs: Internal X-MS-Exchange-CrossPremises-AuthMechanism: 04 X-MS-Exchange-CrossPremises-AuthSource: BYAPR06MB4471.namprd06.prod.outlook.com X-MS-Exchange-CrossPremises-TransportTrafficType: Email X-MS-Exchange-CrossPremises-SCL: 1 X-MS-Exchange-CrossPremises-messagesource: StoreDriver X-MS-Exchange-CrossPremises-BCC: X-MS-Exchange-CrossPremises-originalclientipaddress: 168.94.238.45 X-MS-Exchange-CrossPremises-transporttraffictype: Email X-MS-Exchange-CrossPremises-antispam-scancontext: DIR:Originating;SFV:NSPM;SKIP:0; X-MS-Exchange-CrossPremises-processed-by-journaling: Journal Agent X-OrganizationHeadersPreserved: BY3PR06MB8083.namprd06.prod.outlook.com X-Authority-Analysis: v=2.4 cv=NcHWEWD4 c=1 sm=1 tr=0 ts=69ef862a cx=c_pps a=Y/Y9x3HtMOqQL+YbCjVQEg==:117 a=z/mQ4Ysz8XfWz/Q5cLBRGdckG28=:19 a=lCpzRmAYbLLaTzLvsPZ7Mbvzbb8=:19 a=xqWC_Br6kY4A:10 a=A5OVakUREuEA:10 a=YABJ2S72nEIA:10 a=VkNPw1HP01LnGYTKEx00:22 a=KeFp_MbVbeh7RJJDCzxV:22 a=xazT8dg1DAXQX9sMwihd:22 a=MsrqofZMeygxttoYJc4A:9 a=pILNOxqGKmIA:10 a=I3vv0rsJFCldhy7fNXEA:9 a=ZrNzIkPog90Pk65T:21 a=_W_S_7VecoQA:10 X-Proofpoint-ORIG-GUID: VXJ-WmV_-hScsejE8qY7S9OFZVheJGZb X-Proofpoint-Spam-Details-Enc: AW1haW4tMjYwNDI3MDE2OSBTYWx0ZWRfXzvjgkPqr1W/r 5J0IPIN1R4IMZ0n+uvl5LAKVGTKjGgMPp5ms2ayvLf3MPTUSSIIUMnKZvBe/v0EiqE+5bFFgx7E O/wDE73SAaiqRaL1JS7APOD61nf7P7hAg67byfa+d7L7Lp3NT2tjM/6YBV3HJcmFujORUDHqFsc 4MjtySxi2yTqJ/VWx7jk1TNvyx/l7uf2YdaBjDb8dtANhnxRJIRCx3igtUHa8PgAbdOYDwQy9Pu Qf2eembcVmQYDel1IGzSvpt1cOHovp5r63AcfQ9NwGpNgikmlSUxsqKyVVgrWcuDmTqVP9lXNi6 vgyP8KBL9c5afKNevcFrb78cS9LIaiQkMd8gs4ySZvxqTPTekjW7/b+Hv7Ie9zpUmOMq8rYyR25 Yva0KIJLwjNA1ZSB5j1kA2WBN9z2x43zVpUAwOBpQqoIuVLU4HZ5jWMjNCtdlUK3VBlKIItrsUb vFMxZY3PSz0sbQE3fYA== X-Proofpoint-GUID: VXJ-WmV_-hScsejE8qY7S9OFZVheJGZb X-Proofpoint-Virus-Version: vendor=baseguard engine=ICAP:2.0.293,Aquarius:18.0.1143,Hydra:6.1.51,FMLib:17.12.100.49 definitions=2026-04-27_04,2026-04-21_02,2025-10-01_01 X-Proofpoint-Spam-Details: rule=outbound_notspam policy=outbound score=0 priorityscore=1501 lowpriorityscore=0 phishscore=0 spamscore=0 clxscore=1011 bulkscore=0 adultscore=0 impostorscore=0 suspectscore=0 malwarescore=0 classifier=typeunknown authscore=0 authtc= authcc= route=outbound adjust=0 reason=mlx scancount=1 engine=8.22.0-2604200000 definitions=main-2604270169 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_BYAPR06MB447199DE1FCF363EF389EF1A8A362BYAPR06MB4471namp_ Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable Aurora Postgres version 17.4. Table in question: \d poslog_publisher_rms_stage Table "public.poslog_publisher_rms_stage" Column | Type | Collation | Nullable | Defa= ult -------------------+--------------------------+-----------+----------+-----= ---- stage_id | uuid | | not null | status | character varying(100) | | | message_body | text | | not null | error_code | character varying(100) | | | error_category | character varying(100) | | | error_message | text | | | error_retry_count | integer | | | 0 create_date | timestamp with time zone | | not null | now(= ) modified_date | timestamp with time zone | | not null | now(= ) Indexes: "poslog_publisher_rms_stage_pkey" PRIMARY KEY, btree (stage_id) "idx_poslog_publisher_stage_create_date_col" btree (create_date) "idx_poslog_publisher_stage_status_error_retry_count_modi_date_c" btree= (status, error_retry_count, modified_date) Referenced by: TABLE "poslog_publisher_rms_detail" CONSTRAINT "fk_poslog_publisher_det= ail_stage_id" FOREIGN KEY (stage_id) REFERENCES poslog_publisher_rms_stage(= stage_id) Publications: =93sashpsrms_publication" The table is constantly getting loaded into. Rows are inserted with a stat= us ready and then there is a different process looking for that status and = will update to processed after processing the row. We have multiple tables= like this. Then every 2 hours a different process runs this query looking for failed o= r unprocessed rows: select ppse.stage_id as stageId, ppse.status as status, ppse.message_body as messageBody from poslog_publisher_rms_stage ppse where ppse.status in ('UNPROCESSED','FAILED') and ppse.error_retry_count < 3 order by ppse.create_date limit 100; If I run that query with explain it is doing a ton of work to find 0 rows. = The index it uses seems appropriate for the query. = QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------------------------------------------- Limit (cost=3D10.92..10.93 rows=3D1 width=3D1019) (actual time=3D66566.82= 3..66566.824 rows=3D0 loops=3D1) Buffers: shared hit=3D1509768 read=3D2011479 I/O Timings: shared read=3D79792.017 -> Sort (cost=3D10.92..10.93 rows=3D1 width=3D1019) (actual time=3D665= 66.821..66566.821 rows=3D0 loops=3D1) Sort Key: create_date Sort Method: quicksort Memory: 25kB Buffers: shared hit=3D1509768 read=3D2011479 I/O Timings: shared read=3D79792.017 -> Index Scan using idx_poslog_publisher_stage_status_error_retry= _count_modi_date_c on poslog_publisher_rms_stage ppse (cost=3D0.57..10.91 = rows=3D1 width=3D1019) (actual time=3D66566.761..66566.761 rows=3D0 loops= =3D1) Index Cond: (((status)::text =3D ANY ('{UNPROCESSED,FAILED}'= ::text[])) AND (error_retry_count < 3)) Buffers: shared hit=3D1509765 read=3D2011479 I/O Timings: shared read=3D79792.017 Planning: Buffers: shared hit=3D195 read=3D1 I/O Timings: shared read=3D1.038 Planning Time: 2.909 ms Execution Time: 66581.498 ms The query did 3.5 million block reads when scanning the index of which 1.5 = million were in memory and 2 million were from disk. 5 seconds later I ran= the exact same query again: = QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------------------------------------- Limit (cost=3D10.92..10.93 rows=3D1 width=3D1019) (actual time=3D23.589..= 23.591 rows=3D0 loops=3D1) Buffers: shared hit=3D18736 -> Sort (cost=3D10.92..10.93 rows=3D1 width=3D1019) (actual time=3D23.= 588..23.589 rows=3D0 loops=3D1) Sort Key: create_date Sort Method: quicksort Memory: 25kB Buffers: shared hit=3D18736 -> Index Scan using idx_poslog_publisher_stage_status_error_retry= _count_modi_date_c on poslog_publisher_rms_stage ppse (cost=3D0.57..10.91 = rows=3D1 width=3D1019) (actual time=3D23.583..23.584 rows=3D0 loops=3D1) Index Cond: (((status)::text =3D ANY ('{UNPROCESSED,FAILED}'= ::text[])) AND (error_retry_count < 3)) Buffers: shared hit=3D18736 Planning Time: 0.118 ms Execution Time: 23.628 ms Now it only did 18k block reads all in memory. It used the same index, it = also returned 0 rows. Between those 2 runs I looked at pg_stat_user_tables= and could see the n_tup_ins increased by 13, n_tup_del increased by 13, n_= live_tup increased by 13, and n_dead_tup increased by 13. n_live_tup was 9= 6 million and n_dead_tup was 8.4 million. 18k logical reads to find 0 rows= is still high but I believe that is most likely caused by the 8.4 million = n_dead_tups. 15 minutes later I ran the query again: = QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------------------------------------------- Limit (cost=3D10.92..10.93 rows=3D1 width=3D1019) (actual time=3D59431.79= 5..59431.796 rows=3D0 loops=3D1) Buffers: shared hit=3D1286676 read=3D1734000 I/O Timings: shared read=3D70153.578 -> Sort (cost=3D10.92..10.93 rows=3D1 width=3D1019) (actual time=3D594= 31.794..59431.794 rows=3D0 loops=3D1) Sort Key: create_date Sort Method: quicksort Memory: 25kB Buffers: shared hit=3D1286676 read=3D1734000 I/O Timings: shared read=3D70153.578 -> Index Scan using idx_poslog_publisher_stage_status_error_retry= _count_modi_date_c on poslog_publisher_rms_stage ppse (cost=3D0.57..10.91 = rows=3D1 width=3D1019) (actual time=3D59431.789..59431.790 rows=3D0 loops= =3D1) Index Cond: (((status)::text =3D ANY ('{UNPROCESSED,FAILED}'= ::text[])) AND (error_retry_count < 3)) Buffers: shared hit=3D1286676 read=3D1734000 I/O Timings: shared read=3D70153.578 Planning Time: 0.114 ms Execution Time: 59431.839 ms Total blocks reads increased from 18k to 3 million. n_tup_ins, n_tup_del, = n_live_tup, and n_dead_tup all increased by 10,320 over that 15 minute peri= od of time. What is going on here where this query has to do 3+ million block reads to = find 0 rows? And how is it possible when I run the query 2 times in a row = the logical reads from the 2nd run comes down significantly? Is this someh= ow related to determining if rows are visible or something like that? When= I waited 15 minutes between runs the inserted/updated rows only increased = by 10.3k yet total block reads increased by 3 million. Thanks --_000_BYAPR06MB447199DE1FCF363EF389EF1A8A362BYAPR06MB4471namp_ Content-Type: text/html; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable
Aurora Postgres version 17.4.

Table in question:

\d poslog_publisher_rms_stage
                   Table = "public.poslog_publisher_rms_stage"
      Column       |       &nb= sp;   Type           | Collation | Nullable |= Default
-------------------+--------------------------+-----------+----------+-----= ----
 stage_id          | uuid      = ;               |       &= nbsp;   | not null |
 status            | character varying(1= 00)   |           |       &nbs= p;  |
 message_body      | text         &= nbsp;           |         &nbs= p; | not null |
 error_code        | character varying(100)  = |           |          |=
 error_category    | character varying(100)   |   =         |          |
 error_message     | text          =           |           | =          |
 error_retry_count | integer            =      |           |     &= nbsp;    | 0
 create_date       | timestamp with time zone |   =         | not null | now()
 modified_date     | timestamp with time zone |    = ;       | not null | now()
Indexes:
    "poslog_publisher_rms_stage_pkey" PRIMARY KEY, btre= e (stage_id)
    "idx_poslog_publisher_stage_create_date_col" btree = (create_date)
    "idx_poslog_publisher_stage_status_error_retry_count_mod= i_date_c" btree (status, error_retry_count, modified_date)
Referenced by:
    TABLE "poslog_publisher_rms_detail" CONSTRAINT &quo= t;fk_poslog_publisher_detail_stage_id" FOREIGN KEY (stage_id) REFERENC= ES poslog_publisher_rms_stage(stage_id)
Publications:
    =93sashpsrms_publication"

The table is constantly getting loaded into.  Rows are inserted with a= status ready and then there is a different process looking for that status= and will update to processed after processing the row.  We have multi= ple tables like this.

Then every 2 hours a different process runs this query looking for failed o= r unprocessed rows:

select
                     = ; ppse.stage_id as stageId,
                     = ; ppse.status as status,
                     = ; ppse.message_body as messageBody
                    from<= /div>
                     = ; poslog_publisher_rms_stage ppse
                    where=
                     = ; ppse.status in ('UNPROCESSED','FAILED')
                     = ; and ppse.error_retry_count < 3
                    order= by
                     = ; ppse.create_date
                    limit= 100;

If I run that query with explain it is doing a ton of work to find 0 rows. =  The index it uses seems appropriate for the query.

 &nbs= p;                    &nb= sp;                     &= nbsp;                    =                     &nbs= p;                QUERY PLAN=
---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------------------------------------------------------------
 Limit  (cost=3D10.92..10.93 rows=3D1 width=3D1019) (actual time= =3D66566.823..66566.824 rows=3D0 loops=3D1)
   Buffers: shared hit=3D1509768 read=3D2011479
   I/O Timings: shared read=3D79792.017
   ->  Sort  (cost=3D10.92..10.93 rows=3D1 width=3D1= 019) (actual time=3D66566.821..66566.821 rows=3D0 loops=3D1)
         Sort Key: create_date
         Sort Method: quicksort  Memory: 25kB=
         Buffers: shared hit=3D1509768 read=3D2011= 479
         I/O Timings: shared read=3D79792.017
         ->  Index Scan using idx_poslog_p= ublisher_stage_status_error_retry_count_modi_date_c on poslog_publisher_rms= _stage ppse  (cost=3D0.57..10.91 rows=3D1 width=3D1019) (actual time= =3D66566.761..66566.761 rows=3D0 loops=3D1)
               Index Cond: (((statu= s)::text =3D ANY ('{UNPROCESSED,FAILED}'::text[])) AND (error_retry_count &= lt; 3))
               Buffers: shared hit= =3D1509765 read=3D2011479
               I/O Timings: shared = read=3D79792.017
 Planning:
   Buffers: shared hit=3D195 read=3D1
   I/O Timings: shared read=3D1.038
 Planning Time: 2.909 ms
 Execution Time: 66581.498 ms

The query did 3.5 million block reads when scanning the index of which 1.5 = million were in memory and 2 million were from disk.  5 seconds later = I ran the exact same query again:

                     = ;                     &nb= sp;                     &= nbsp;                    =               QUERY PLAN
---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------------------------------------------------------
 Limit  (cost=3D10.92..10.93 rows=3D1 width=3D1019) (actual time= =3D23.589..23.591 rows=3D0 loops=3D1)
   Buffers: shared hit=3D18736
   ->  Sort  (cost=3D10.92..10.93 rows=3D1 width=3D1= 019) (actual time=3D23.588..23.589 rows=3D0 loops=3D1)
         Sort Key: create_date
         Sort Method: quicksort  Memory: 25kB=
         Buffers: shared hit=3D18736
         ->  Index Scan using idx_poslog_p= ublisher_stage_status_error_retry_count_modi_date_c on poslog_publisher_rms= _stage ppse  (cost=3D0.57..10.91 rows=3D1 width=3D1019) (actual time= =3D23.583..23.584 rows=3D0 loops=3D1)
               Index Cond: (((statu= s)::text =3D ANY ('{UNPROCESSED,FAILED}'::text[])) AND (error_retry_count &= lt; 3))
               Buffers: shared hit= =3D18736
 Planning Time: 0.118 ms
 Execution Time: 23.628 ms

Now it only did 18k block reads all in memory.  It used the same index= , it also returned 0 rows.  Between those 2 runs I looked at pg_stat_u= ser_tables and could see the n_tup_ins increased by 13, n_tup_del increased= by 13, n_live_tup increased by 13, and n_dead_tup increased by 13.  n_live_tup was 96 million and n_dead_tup was 8.4 mi= llion.  18k logical reads to find 0 rows is still high but I believe t= hat is most likely caused by the 8.4 million n_dead_tups.

15 minutes later I ran the query again:

 &nbs= p;                    &nb= sp;                     &= nbsp;                    =                     &nbs= p;                QUERY PLAN=
---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------------------------------------------------------------
 Limit  (cost=3D10.92..10.93 rows=3D1 width=3D1019) (actual time= =3D59431.795..59431.796 rows=3D0 loops=3D1)
   Buffers: shared hit=3D1286676 read=3D1734000
   I/O Timings: shared read=3D70153.578
   ->  Sort  (cost=3D10.92..10.93 rows=3D1 width=3D1= 019) (actual time=3D59431.794..59431.794 rows=3D0 loops=3D1)
         Sort Key: create_date
         Sort Method: quicksort  Memory: 25kB=
         Buffers: shared hit=3D1286676 read=3D1734= 000
         I/O Timings: shared read=3D70153.578
         ->  Index Scan using idx_poslog_p= ublisher_stage_status_error_retry_count_modi_date_c on poslog_publisher_rms= _stage ppse  (cost=3D0.57..10.91 rows=3D1 width=3D1019) (actual time= =3D59431.789..59431.790 rows=3D0 loops=3D1)
               Index Cond: (((statu= s)::text =3D ANY ('{UNPROCESSED,FAILED}'::text[])) AND (error_retry_count &= lt; 3))
               Buffers: shared hit= =3D1286676 read=3D1734000
               I/O Timings: shared = read=3D70153.578
 Planning Time: 0.114 ms
 Execution Time: 59431.839 ms 

Total blocks reads increased from 18k to 3 million.  n_tup_ins, n_tup_= del, n_live_tup, and n_dead_tup all increased by 10,320 over that 15 minute= period of time.  

What is going on here where this query has to do 3+ million block reads to = find 0 rows?  And how is it possible when I run the query 2 times in a= row the logical reads from the 2nd run comes down significantly?  Is = this somehow related to determining if rows are visible or something like that?  When I waited 15 minutes between= runs the inserted/updated rows only increased by 10.3k yet total block rea= ds increased by 3 million.

Thanks
--_000_BYAPR06MB447199DE1FCF363EF389EF1A8A362BYAPR06MB4471namp_--