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 1vGfy4-003xsb-9h for pgsql-performance@arkaria.postgresql.org; Wed, 05 Nov 2025 16:02:40 +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 1vGfy3-003Omm-8l for pgsql-performance@arkaria.postgresql.org; Wed, 05 Nov 2025 16:02:38 +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.94.2) (envelope-from ) id 1vGfy2-003Ome-F3 for pgsql-performance@lists.postgresql.org; Wed, 05 Nov 2025 16:02:37 +0000 Received: from mx0b-00160c04.pphosted.com ([205.220.179.180]) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vGfxx-005bn3-2h for pgsql-performance@lists.postgresql.org; Wed, 05 Nov 2025 16:02:35 +0000 Received: from pps.filterd (m0276502.ppops.net [127.0.0.1]) by mx0b-00160c04.pphosted.com (8.18.1.2/8.18.1.2) with ESMTP id 5A5Cu5t3019485 for ; Wed, 5 Nov 2025 16:02:32 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=ztxXdlAgPIZGNIdiOkPmmRu rNWoCEZFNYPeURCDjEYE=; b=M3kHcRIK+G0MqfnIhmtx8lnwaFGnVxrX4mG/qFk GdQrfiVIgOOQLXJI4tuCwSlkjFWqv7HniaOQtULUMZv4UusH2776ZT8rNk4XurMb IxGzfou8ZgysQIOc5AiWVMj85cO7fqau0kiKcAIrBDsMO++YZEaa5ecBJM9fP7iE p707u875Q8knYRIZosEMFBL9xb8uGamwao58YpifwX+65RE/Kk10kruYvqkgkYmW XrwBZLdeUpgcErrE9+OrV0/5/c3+LuS/0xQa0ZgxQCUcameuCGnIGOaGLEwQs1cM kx9AIXw9OYZ5RKUP2INH2f2Sjqr/f/nnO7flsilAwQa8SXQ== Received: from sa9pr02cu001.outbound.protection.outlook.com (mail-southcentralusazon11013061.outbound.protection.outlook.com [40.93.196.61]) by mx0b-00160c04.pphosted.com (PPS) with ESMTPS id 4a870t2ggf-1 (version=TLSv1.2 cipher=ECDHE-RSA-AES256-GCM-SHA384 bits=256 verify=NOT) for ; Wed, 05 Nov 2025 16:02:32 +0000 (GMT) ARC-Seal: i=1; a=rsa-sha256; s=arcselector10001; d=microsoft.com; cv=none; b=rhbwiLnbM6KdEmqxhShmQMrRUpxR8gZD6kZ3ucAXmqTfM8CWC6s6p89T7b3wPxy5zRfhTa9LQ0dBWSomeSH9AnkeWqm3Qz4+LMeZT/oXjnQDe+PkVVNmtxXGIbuGFV+ptVXc/rqfNmNr5OaTJG1b6iFde0Rjd5L3k2UmZmVA377o+2jkeAOEsMLRgokTEouQllR3QSpvF6wxlR/2u2MBE3kyl3McHxC7nRRQ5QuHz/7lNQgZ1Zw1Aw6jL9AEIueDVP98rkc+R470ONl0VXY3oowHoX1pHekNAFPke1pg2KzlLIxnkWCyQ/KPHvqHJED36rGf1IYaGir5zFB1dYBQBA== 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=ztxXdlAgPIZGNIdiOkPmmRurNWoCEZFNYPeURCDjEYE=; b=eHDWVH6dLoBYwNBV3wCB1ynSxMnH49IZCPWnL57lwyDue7EH3iNaGCx3eqF0oOlQop8/YumugBGm80M0rHRofwr8/XJnKOZdX+YldPJVrvzHvbHOA992GxKP4fzOScJWVr0hNP4/5YPqUI8JFwX3FnPbIjJk8I+dYuEhUD5ffeUstFjJ3pbJCs/+SS4o0FQWjcjANze4DtdHI+AVUdaEq332L9dLH3PlKhsqHlqPN9GVLLHJ366URpZL8wRr0DD7lzrh6dypuoEhhT4eq+RKlaZo9reGuw8zv9rvTTgk24vxGxHnMkobLinHMg+rPVe1p/yzsbXaYUDlco5W5jhjvA== 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=ztxXdlAgPIZGNIdiOkPmmRurNWoCEZFNYPeURCDjEYE=; b=h46klCJdkScdQujATWFEUEvJ34YsTe8N+J83jUHj9p24Bq7CsqKe4/IkNOxI7Qd/+1uWbhHPXcEUQ3sdx4bJmz8hNmnRFZDtaG1I48unSt4G4WeYwi5v+Nn0O++ryOt+yIJz0HC8HTDP2SPFuoW/cs6HD3gCdo4JOHUnHVxFxKE= Received: from BL1PR03MB6038.namprd03.prod.outlook.com (2603:10b6:208:312::23) by SJ0PR03MB6375.namprd03.prod.outlook.com (2603:10b6:a03:399::17) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.9275.15; Wed, 5 Nov 2025 16:02:28 +0000 Received: from BL1PR03MB6038.namprd03.prod.outlook.com ([fe80::c21b:3020:8bc6:602]) by BL1PR03MB6038.namprd03.prod.outlook.com ([fe80::c21b:3020:8bc6:602%6]) with mapi id 15.20.9275.016; Wed, 5 Nov 2025 16:02:28 +0000 From: "Dirschel, Steve" To: "pgsql-performance@lists.postgresql.org" Subject: Problem getting query to use index inside a function Thread-Topic: Problem getting query to use index inside a function Thread-Index: AdxOayMhQeB/QpCCQ1+VoTZN+Zmgxw== Date: Wed, 5 Nov 2025 16:02:27 +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: BL1PR03MB6038:EE_|SJ0PR03MB6375:EE_ x-ms-office365-filtering-correlation-id: c4b30efe-c696-4feb-5568-08de1c84b7f6 x-ms-exchange-senderadcheck: 1 x-ms-exchange-antispam-relay: 0 x-microsoft-antispam: BCL:0;ARA:13230040|376014|366016|1800799024|8096899003|38070700021; x-microsoft-antispam-message-info: =?us-ascii?Q?vqCWoIEaoZg6ZX1+oGsDAl8WYUQYJVO4cLaG5JM+T3o2PJFgtM7EdLIZJpZC?= =?us-ascii?Q?26o4pa8FhBwGWVH+fVeJTpSDPzVNGB7WKQQpP0g13y2QKc7unxAsUg71gBwa?= =?us-ascii?Q?F0FUAlBUKukWlVvLKD2X/wqrcS8VTvKeJlcxLkire9MYRQyoRyKSXJfx5SsK?= =?us-ascii?Q?54LjwdQXTOMaBgr9skzCPj3WwF+ZSh+Kfl3R5gGpRk14jJd8Wc/OPu052bYx?= =?us-ascii?Q?h4s60/v4bRGKHvr+HU8H0vze5DIppEHaQBU68RFcAQgzgJjz6QZrsimxJnip?= =?us-ascii?Q?pUzEeOKv1Y6eRluPowqh82T0SslI9W/Xu6Gbhg9A1EsNyxkFGg3f2oLBY13S?= =?us-ascii?Q?V6btJ5DpvZuST8b65cVzRV1jEC4JzdDqrjKzmjal9HIwaAwDvh/f5MoBab/j?= =?us-ascii?Q?cRIN8nhWKmRCVxI0/AuooWnMyt4uNllQBiD9osS/u70BQ/KrF5MDOSx96a9y?= =?us-ascii?Q?x+YJwRS4qzwyRcrL8f1MgmjNw1MWBuzPIhkfDZfKuZt/Rmn605Wtno3WsJHm?= =?us-ascii?Q?Y9tarkaiSy6oGGhQ0k9valIlPIa95GM42A+SXsiIoGjI9Ib0wp5cHrGwpqlO?= =?us-ascii?Q?bpIb10oIdZwyAMFQveMgKVtDeQzWWWYmnHpef+lpx7xjwv1JSqUFsniBdPjP?= =?us-ascii?Q?2+pP6ZY4AF2CBQ3gH1EVZ65uyvF8WWIPqTfYjboDn+RrV24H4siZfnhdCuwN?= =?us-ascii?Q?fNFaDYAMCG50cr0snGB1+lYT4FhUKtgrID8ZIhQ4Te3iM4sfkNeXZUNT9P6I?= =?us-ascii?Q?igeTs2RYZ60SOqFRGu7hXwM09Fx3E8mJXlPKYGu1oPAYuajxJ9FQtVPxAMQQ?= =?us-ascii?Q?S1ZwMG8QPo3JwmUzQ7fs7y6WGVkdzA9XSz6K5N6Agdc60VNEnYvC2J1XpIBs?= =?us-ascii?Q?TAWvCHQUbuOPn4wTHWwOm4EZ+74ksTo02fK4qNBdPpOx4PR9I33myCcut8EE?= =?us-ascii?Q?GFwDb342kM6AehzAzTNN0GFZOtYhvJv76Luz4SGamtjapwJbNG4xE4SiiSpp?= =?us-ascii?Q?9swOR787OyUC+h2z3xarnGqRPhHGP8/c6UaNR+Q8m9aEaNbAoDcE9O1NYg0b?= =?us-ascii?Q?Ln/1itRnEty2P6NxHO7tMvMzGwNER96vxXkA+xY0pEQW9NtWV9fmS+wk9sTZ?= =?us-ascii?Q?4lDyYxPjPfqqVSeslXkuR7mxJIdg5EhjPC2cemCoxNtw9jJZg+ggrvGC8Z0D?= =?us-ascii?Q?+DxwX/52QrR71hUCBZWv/RPc9D0SSCaJdVrs/hQQphdjdL1OILOfh4ymZXTf?= =?us-ascii?Q?D3CRlRqvIojB4TBnA1tAzzA4bsPxDRHcOLNs4tTQ/4nVqxGdHGAeV5rvNUCd?= =?us-ascii?Q?78rl9kmx709tRUdfAuHD/5J8TozC1yVVC/Y0NoNFPLIh/wuPf5YgmV9pBAkg?= =?us-ascii?Q?tOwPhcu/Sdn3CocndlOd2ChPsRd9LXsf7wz8V3Mh6L7eGKjcF+vbxsrh7l9z?= =?us-ascii?Q?LMKhpVnG70GvkfKQ3lJXDp1kvT/CRZ7mBgO+gcjaL5Jo414thhPd8GKh0kCq?= =?us-ascii?Q?W4frsKfL4415qIQT9Va/qOYV4aZr1x+9+uG5?= x-forefront-antispam-report: CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:BL1PR03MB6038.namprd03.prod.outlook.com;PTR:;CAT:NONE;SFS:(13230040)(376014)(366016)(1800799024)(8096899003)(38070700021);DIR:OUT;SFP:1101; x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?us-ascii?Q?TKmg4US/c12N0tlHU/1XniuYKCHSadoRddXmmhbzcX31zp9sFWF6Ch+pIxG4?= =?us-ascii?Q?RwDMVrMPGeaKaVcA74YWfu9bUXNgY+qNJlu5KzQECGnDOltSRKcSp9yLFy3H?= =?us-ascii?Q?JC5u3nXairRizc37tAUvt0hVi5NyhwBJ7Tr6pje5Gp/PD9I+g1T0eH/IVsnh?= =?us-ascii?Q?aOUnCQqWSSVywJQYmozaq442gab3h4bNo6vFluwAuJ5dGirZ6CcoXy0cjJ0W?= =?us-ascii?Q?Mr0PlGvvbLQwwigQ2Jsi96/uNIHRO/QIR2l3awf50sb/U9jbnOc9KaeQg658?= =?us-ascii?Q?Nk2Y/JiabmhWnOCoOjk/f9kcJfeUtcpdp+eEywQvmiSdHhVOviF/OrVgSQHa?= =?us-ascii?Q?ma3TFINvFPIfdaebKcQI7eajiR2ERblgEtPTjMwXYo+IbisvLOhDfiTUvH7z?= =?us-ascii?Q?fah6enuQ+EUSHkTzGTqcY1nfucFcZJyATUYtihlIVQO0ld4Xco9p7lEAagFY?= =?us-ascii?Q?xKVB0vax4AiMZmwD3i6hCul4rVPAjn9eVGJYrbBWwCRnpvl9igfUrUzqwS/y?= =?us-ascii?Q?RPdcPRF07C38EQmVCV+wZYV9uT8M2FBKyXiKpR+afwXABMCWjal7WNQ+NFfh?= =?us-ascii?Q?ZCVWAooAe/8FsCl1xS5nRg394fe8bQ5wC0tfVGJ02zjZ45mzSKgxEDpIFyyB?= =?us-ascii?Q?tIek7hjbWTjmK6/3w4lU30XpvJhn0c4RJ5a7J9bb7ECQ5x64k4bbpG0vOKDb?= =?us-ascii?Q?OrMk0upr97APzZQ1z4sn0VtbyGGUq8qS88+FqwrbxG68hX6kA9gWhE9Sesg/?= =?us-ascii?Q?xvryIdy5TxZEPMw2W15PIVZgMYIDyYw4Kyzsb7fNe644ukx43JwgkXR1+9yT?= =?us-ascii?Q?8KcR4ei+rgkwyZpndHF4XTCaUhDL+27XndLtufCcyp1S7PhBll7xS/M2V+e1?= =?us-ascii?Q?oK0lYrHoXiAFHtwYKrZfsi+vH8WejMvAxtLiX/Ty3dL1fu9NUVHKWws9nLrn?= =?us-ascii?Q?qC5m42u4X1Uu/qdEEck3xf+ebDZNAidf0eiPa3JT1mby3OGdAt3AKK2o0EGQ?= =?us-ascii?Q?ZAbCkfIU+gYFgWIqe5PK0v1raSP7KY8DXeBZiCoi504ijQiBAw26e5lE2Z14?= =?us-ascii?Q?EpeP3VXZmH/BbO61qVMM4cslnUpjxSgv/N2PbzDVj8m4ct6OaNuylLDM8O5/?= =?us-ascii?Q?X6gKOhVS038RFGO6dwrZd5lDin+lwMbOPdB23aedHnNmimOGPhQ5lB/1xw0u?= =?us-ascii?Q?W/344xtF6C20iOpjSetLrdyQsJGR/Aa9Zgo+MWsmUofjpbDgnpmKjjyvnuQf?= =?us-ascii?Q?5XWJEDao6iEcfyUbN2l8vY7GJacd4ulqKt64h9xWBbxHRap69cpMX7BPICkr?= =?us-ascii?Q?V+ZuaONPljhLvSI0F2CRLsQrZkAOvDTxLXqB5/q8X9+yWxzAvtvXkTqpRYpB?= =?us-ascii?Q?qMtgl/HwviQAnsCGicRr6OuYa1SIwrx2xTuk67XzSL1FAoj/lhOnb3JPSF3l?= =?us-ascii?Q?E63kAZ0dUM16+7lpU6p5VnS0t3zyzlpbOIOy6R2nRBPnSheZJWWZ4ZqhbUbf?= =?us-ascii?Q?cE3FPy7zXTOFjI3ZUKadP0sJZAM6jDfTHbEKHWcz6qAmmhsKCZFubARX8ttW?= =?us-ascii?Q?8sg3Xa00s5FgHDHiuprE0vQHu14WaOzoSi8g/GuwcBc6iJprFUHJWsZLnGSC?= =?us-ascii?Q?CA=3D=3D?= Content-Type: multipart/alternative; boundary="_000_BL1PR03MB60386561F48885BEEA14C575FAC5ABL1PR03MB6038namp_" MIME-Version: 1.0 X-MS-Exchange-AntiSpam-ExternalHop-MessageData-ChunkCount: 1 X-MS-Exchange-AntiSpam-ExternalHop-MessageData-0: 8UxSPPXaKsfYZLh/rIBgT1qdG5IFfogIV1XdbVDsu02eQQgYnL5sGCe4DXBNbhWOkcu5XEXJr/COkZToLDImwkWJCCdhGmzvyHEA40C+3WXu9Y6xq062RBm2Q1Qx2/+eLC2VCJ3BIaOF9obiEkhpNxRrsNn5kNmq6oaLD/Qa68JCxCw22g0v7Af6EcbjSCGcU9YxmzGew5upL2SPlMDYV4N/CSnRmA+Wj6apNJ4xmJfZJKBxjTkjsNAPXuRd515VBa1vClP1ztz0fGJf/IpydW5xUEGl9lJnLUDxo3zfmA7lh5/f2IBTgQqrlJzOSTsP+TWqkELNxjwmvOsBdFN3RLJ/ep41cZLjGRxspLTvHe9snEUGb2hPS+BWEJppFD25BLpJT/8dQAqWsxypXQx8dsFzmD9U3faD3C8XTsKn/uf7nSC4iNjwGqb2yIn9p4ywsDUDwWlb5x9WopFXZssL39TSL17ZKOFQgK3+KAqmJFsA+W/4AqZROtWHe7vEJjhk7yKuWL+9ZejxxYoCmqG2cgBrzs8/MXiEyibWhn2FEICYcM+DboyntKRGSuD+vsmWT57rGQfwPdPRFNhWuGGf4EWgdZZmhdgAq/j3dekjwUHRhQlGroynmcrQIOFrBdZA5XpqlR61ZP9eQ1DLaUM+oY87yE7VqH06xmli0KikXEs= X-OriginatorOrg: thomsonreuters.com X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-AuthSource: BL1PR03MB6038.namprd03.prod.outlook.com X-MS-Exchange-CrossTenant-Network-Message-Id: c4b30efe-c696-4feb-5568-08de1c84b7f6 X-MS-Exchange-CrossTenant-originalarrivaltime: 05 Nov 2025 16:02:28.0774 (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: 3wNZJOWq84Xo1ykeOG6yu4w4OS6V3Wwp6NDrfat3goocIpsqIHp2wtcctgGWiDa6gaOEEusCGTneAYVHB8UXD+1hLrX24VMqDNuF6VYCKFEmLvrpbmTAUUBbJ9B+GzyP X-MS-Exchange-Transport-CrossTenantHeadersStamped: SJ0PR03MB6375 X-Proofpoint-GUID: UtOqGTeAA2rcvNFP9YzM7NwVk5Ot8OQB X-Authority-Analysis: v=2.4 cv=AYW83nXG c=1 sm=1 tr=0 ts=690b7518 cx=c_pps a=DPCWJwWgIuHEo730oqIOaQ==:117 a=z/mQ4Ysz8XfWz/Q5cLBRGdckG28=:19 a=lCpzRmAYbLLaTzLvsPZ7Mbvzbb8=:19 a=xqWC_Br6kY4A:10 a=6UeiqGixMTsA:10 a=pKFMK-pTKUcA:10 a=VkNPw1HP01LnGYTKEx00:22 a=awlg0vDVAAAA:8 a=1gpoH_2XJU7Ajs2CrA0A:9 a=CjuIK1q_8ugA:10 a=pAs9YAABlUgA:10 a=tMCeSvlf9uQA:10 a=yMhMjlubAAAA:8 a=SSmOFEACAAAA:8 a=NIlUIi7oXp4oVj5Kn48A:9 a=dNVYG55X9gVh4V3L:21 a=gKO2Hq4RSVkA:10 a=UiCQ7L4-1S4A:10 a=hTZeC7Yk6K0A:10 a=frz4AuCg-hUA:10 X-Proofpoint-ORIG-GUID: UtOqGTeAA2rcvNFP9YzM7NwVk5Ot8OQB X-Proofpoint-Spam-Details-Enc: AW1haW4tMjUxMTA1MDA5NiBTYWx0ZWRfX1JGpVKipapFw yt9shV+0F5VHvEGClVRGgQnUC4jaKCPEbJf8yJHXFa6ioBB7WRdTR6s/yetIJMUwZLUZTVi5YwT P+dPhamHpCvZ9fSJAWedguhHYKnWFhXbLNZ4UBwd1DdcObkBAqrGTCEuuudGp6zIJuLK6ebkR/I k4fIrFWTcukOxc0sYVcQBzkeNBavmHSnFAoJ6INUrUHrgQaL32IU8do6azsuDozJllB6XRc7ixX dj4+O1lUAMJX30WU/M6ymgYscfrjF6x8gnl3KpzRkhRhmaos6pKTpExG6Y9scX2V99vUwFddPX+ SAdPKCHep5Qn/gxXy+o8a+x1169uC7NiH3HFWpzaDVvIi4ioIXWkwsEXxeoQtWLyT95s4AVxgBK mM0ZgdizsZd2sKP1MgR2iMHsXvnmTQ== X-Proofpoint-Virus-Version: vendor=baseguard engine=ICAP:2.0.293,Aquarius:18.0.1121,Hydra:6.1.9,FMLib:17.12.100.49 definitions=2025-11-05_06,2025-11-03_03,2025-10-01_01 X-Proofpoint-Spam-Details: rule=outbound_notspam policy=outbound score=0 malwarescore=0 phishscore=0 lowpriorityscore=0 spamscore=0 suspectscore=0 priorityscore=1501 adultscore=0 clxscore=1015 bulkscore=0 impostorscore=0 classifier=typeunknown authscore=0 authtc= authcc= route=outbound adjust=0 reason=mlx scancount=1 engine=8.19.0-2510240000 definitions=main-2511050096 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_BL1PR03MB60386561F48885BEEA14C575FAC5ABL1PR03MB6038namp_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable I'm not sure if I should send this to pgsql-performance or pqsql-general so= hopefully I'm sending to the correct one. Table definition: shared=3D> \d request Table "public.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) Here is the function I'm having difficulties with: CREATE OR REPLACE FUNCTION public.steve1(param_requestid text[], param_prod= uctid integer DEFAULT 1) RETURNS TABLE(objectid text, n text, v text, vt integer) LANGUAGE sql AS $function$ SELECT objectid::text , i->>'n'::text , i->>'v'::text , (i->>'vt') :: INT as vt FROM request r , jsonb_array_elements(data -> 'i') i WHERE objectid =3D ANY($1) AND productid=3D$2 $function$ ; Query: shared=3D> explain (analyze, buffers) shared-> SELECT objectid::text, n::text, v::text, vt::int FROM steve1(ARRAY= ['5ab8e0ca-abb5-48c9-a95e-2bb2a375d903','adcbe251-6723-48a8-8385-55133fab70= 4a'], 1); QUERY PLAN ---------------------------------------------------------------------------= ------------------------------------- Function Scan on steve1 (cost=3D0.25..10.25 rows=3D1000 width=3D100) (actu= al time=3D42.694..42.942 rows=3D3368 loops=3D1) Buffers: shared hit=3D16612 <------------------- Planning Time: 0.034 ms Execution Time: 43.279 ms (4 rows) The query is doing 16612 logical reads which implies it full scanning the t= able. I can also run the query providing the input values where you can cl= early see it's full scanning the table: shared=3D> explain (analyze, buffers) shared-> SELECT objectid::text shared-> , i->>'n'::text shared-> , i->>'v'::text shared-> , (i->>'vt') :: INT as vt shared-> FROM request r shared-> , jsonb_array_elements(data -> 'i') i shared-> WHERE objectid =3D ANY( ARRAY['5ab8e0ca-abb5-48c9-a95e-2bb2a375d9= 03','adcbe251-6723-48a8-8385-55133fab704a']) shared-> AND productid=3D1; QUER= Y PLAN ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -- Nested Loop (cost=3D0.01..19561.37 rows=3D57000 width=3D100) (actual time= =3D0.351..41.354 rows=3D3368 loops=3D1) Buffers: shared hit=3D16586 <------------- -> Seq Scan on request r (cost=3D0.00..17566.36 rows=3D570 width=3D67)= (actual time=3D0.010..38.341 rows=3D2 loops=3D1) Filter: ((productid =3D 1) AND ((objectid)::text =3D ANY ('{5ab8e0= ca-abb5-48c9-a95e-2bb2a375d903,adcbe251-6723-48a8-8385-55133fab704a}'::text= []))) Rows Removed by Filter: 57077 Buffers: shared hit=3D16569 -> Function Scan on jsonb_array_elements i (cost=3D0.01..1.00 rows=3D1= 00 width=3D32) (actual time=3D0.291..0.406 rows=3D1684 loops=3D2) Buffers: shared hit=3D17 Planning Time: 0.093 ms Execution Time: 41.607 ms (10 rows) I can get the query to use the PK index if I change the query from ARRAY []= to {}: shared=3D> explain (analyze, buffers) shared-> SELECT objectid::text shared-> , i->>'n'::text shared-> , i->>'v'::text shared-> , (i->>'vt') :: INT as vt shared-> FROM request r shared-> , jsonb_array_elements(data -> 'i') i shared-> WHERE objectid =3D ANY('{5ab8e0ca-abb5-48c9-a95e-2bb2a375d903,adc= be251-6723-48a8-8385-55133fab704a}') shared-> AND productid=3D1; QUERY= PLAN ---------------------------------------------------------------------------= --------------------------------------------------------------------------- Nested Loop (cost=3D0.42..23.87 rows=3D200 width=3D100) (actual time=3D0.3= 46..3.007 rows=3D3368 loops=3D1) Buffers: shared hit=3D27 <---------------- -> Index Scan using requestkey on request r (cost=3D0.41..16.87 rows= =3D2 width=3D67) (actual time=3D0.036..0.049 rows=3D2 loops=3D1) Index Cond: ((objectid =3D ANY ('{5ab8e0ca-abb5-48c9-a95e-2bb2a375= d903,adcbe251-6723-48a8-8385-55133fab704a}'::bpchar[])) AND (productid =3D = 1)) Buffers: shared hit=3D10 -> Function Scan on jsonb_array_elements i (cost=3D0.01..1.00 rows=3D1= 00 width=3D32) (actual time=3D0.274..0.388 rows=3D1684 loops=3D2) Buffers: shared hit=3D17 Planning: Buffers: shared hit=3D27 Planning Time: 0.176 ms Execution Time: 3.286 ms (11 rows) But if I change the call to the function to use similar syntax the shared b= uffer hits of 16,586 is telling me it's still full scanning the table: shared=3D> explain (analyze, buffers) shared-> SELECT objectid::text, n::text, v::text, vt::int FROM steve1('{5ab= 8e0ca-abb5-48c9-a95e-2bb2a375d903,adcbe251-6723-48a8-8385-55133fab704a}', 1= ); QUERY PLAN ---------------------------------------------------------------------------= ------------------------------------- Function Scan on steve1 (cost=3D0.25..10.25 rows=3D1000 width=3D100) (actu= al time=3D56.708..56.970 rows=3D3368 loops=3D1) Buffers: shared hit=3D16586 <--------------- Planning Time: 0.024 ms Execution Time: 57.316 ms (4 rows) What do I need to change to get the query to use the PK index when executed= inside the function? Thanks in advance 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_BL1PR03MB60386561F48885BEEA14C575FAC5ABL1PR03MB6038namp_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

I’m not sure = if I should send this to pgsql-performance or pqsql-general so hopefully I&= #8217;m sending to the correct one.

 

Table definition:

 

shared=3D> \d request

          = ;            &n= bsp;          Table "publ= ic.request"

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

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

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

data         &= nbsp;   | jsonb        &n= bsp;            = ;  |           | not= null | '{}'::jsonb

clientid         | = character(3)          &nb= sp;     |       &nbs= p;   | not null |

active         = ;  | integer         &nbs= p;           |  = ;         | not null |

productid        | integ= er            &= nbsp;        |    &n= bsp;      | not null |

checkoutbyuid    | character(100)  = ;            | =           |   &= nbsp;      |

checkoutdatetime | timestamp without time zone |  = ;         |    =       |

metadata         | = jsonb           &nbs= p;           |  = ;         | not null | '{}'::jsonb<= o:p>

search_vector    | tsvector   = ;            &n= bsp;    |        &nb= sp;  |          |

requeststate     | text  &nbs= p;            &= nbsp;        |    &n= bsp;      | not null | 'Active'::text

Indexes:

    "requestkey" PRIMARY KEY, btre= e (objectid, productid)

 

 

Here is the functio= n I’m having difficulties with:

 

CREATE OR REPLACE FUNCTION public.steve1(param_requestid te= xt[], param_productid integer DEFAULT 1)

RETURNS TABLE(objectid text, n text, v text, vt integer)

LANGUAGE sql

AS $function$

SELECT       objectid::text

          = ;   , i->>'n'::text

          = ;   , i->>'v'::text

          = ;   , (i->>'vt') :: INT as vt

FROM   request r

          = ;   , jsonb_array_elements(data -> 'i') i

WHERE objectid =3D ANY($1)

          = ;   AND productid=3D$2

 

$function$

;

 

Query:

 

shared=3D> explain (analyze, buffers)<= /p>

shared-> SELECT objectid::text, n::text, v::text, vt::in= t FROM steve1(ARRAY['5ab8e0ca-abb5-48c9-a95e-2bb2a375d903','adcbe251-6723-4= 8a8-8385-55133fab704a'], 1);

          = ;            &n= bsp;            = ;            &n= bsp;   QUERY PLAN

-----------------------------------------------------------= -----------------------------------------------------

Function Scan on steve1  (cost=3D0.25..10.25 rows=3D10= 00 width=3D100) (actual time=3D42.694..42.942 rows=3D3368 loops=3D1)

   Buffers: shared hit=3D16612  ß= -------= ----------

Planning Time: 0.034 ms

Execution Time: 43.279 ms

(4 rows)

 

The query is doing = 16612 logical reads which implies it full scanning the table.  I can a= lso run the query providing the input values where you can clearly see it&#= 8217;s full scanning the table:

 

shared=3D> explain (analyze, buffers)<= /p>

shared-> SELECT       = ;  objectid::text

shared->        =         , i->>'n'::text=

shared->        =         , i->>'v'::text=

shared->        =         , (i->>'vt') :: INT as vt<= o:p>

shared-> FROM   request r

shared->        =         , jsonb_array_elements(data ->= ; 'i') i

shared-> WHERE  objectid =3D ANY( ARRAY['5ab8e0ca-a= bb5-48c9-a95e-2bb2a375d903','adcbe251-6723-48a8-8385-55133fab704a'])

shared->        =         AND productid=3D1;

          = ;            &n= bsp;            = ;            &n= bsp;            = ;           QUERY PLAN

-----------------------------------------------------------= ---------------------------------------------------------------------------= ------------------

Nested Loop  (cost=3D0.01..19561.37 rows=3D57000 width= =3D100) (actual time=3D0.351..41.354 rows=3D3368 loops=3D1)

   Buffers: shared hit=3D16586  ß= -------= ----

   ->  Seq Scan on request r  (cost= =3D0.00..17566.36 rows=3D570 width=3D67) (actual time=3D0.010..38.341 rows= =3D2 loops=3D1)

         Filter: ((= productid =3D 1) AND ((objectid)::text =3D ANY ('{5ab8e0ca-abb5-48c9-a95e-2= bb2a375d903,adcbe251-6723-48a8-8385-55133fab704a}'::text[])))

         Rows Remov= ed by Filter: 57077

         Buffers: s= hared hit=3D16569

   ->  Function Scan on jsonb_array_eleme= nts i  (cost=3D0.01..1.00 rows=3D100 width=3D32) (actual time=3D0.291.= .0.406 rows=3D1684 loops=3D2)

         Buffers: s= hared hit=3D17

Planning Time: 0.093 ms

Execution Time: 41.607 ms

(10 rows)

 

 

I can get the query= to use the PK index if I change the query from ARRAY [] to {}:<= /span>

 

shared=3D> explain (analyze, buffers)<= /p>

shared-> SELECT       = ;  objectid::text

shared->        =         , i->>'n'::text=

shared->        =         , i->>'v'::text=

shared->        =         , (i->>'vt') :: INT as vt<= o:p>

shared-> FROM   request r

shared->        =         , jsonb_array_elements(data ->= ; 'i') i

shared-> WHERE  objectid =3D ANY('{5ab8e0ca-abb5-48= c9-a95e-2bb2a375d903,adcbe251-6723-48a8-8385-55133fab704a}')

shared->        =         AND productid=3D1;

          = ;            &n= bsp;            = ;            &n= bsp;            = ;          QUERY PLAN

-----------------------------------------------------------= ---------------------------------------------------------------------------= ----------------

Nested Loop  (cost=3D0.42..23.87 rows=3D200 width=3D10= 0) (actual time=3D0.346..3.007 rows=3D3368 loops=3D1)

   Buffers: shared hit=3D27  ß= -------= -------

   ->  Index Scan using requestkey on req= uest r  (cost=3D0.41..16.87 rows=3D2 width=3D67) (actual time=3D0.036.= .0.049 rows=3D2 loops=3D1)

         Index Cond= : ((objectid =3D ANY ('{5ab8e0ca-abb5-48c9-a95e-2bb2a375d903,adcbe251-6723-= 48a8-8385-55133fab704a}'::bpchar[])) AND (productid =3D 1))

         Buffers: s= hared hit=3D10

   ->  Function Scan on jsonb_array_eleme= nts i  (cost=3D0.01..1.00 rows=3D100 width=3D32) (actual time=3D0.274.= .0.388 rows=3D1684 loops=3D2)

         Buffers: s= hared hit=3D17

Planning:

   Buffers: shared hit=3D27

Planning Time: 0.176 ms

Execution Time: 3.286 ms

(11 rows)

 

But if I change the= call to the function to use similar syntax the shared buffer hits of 16,58= 6 is telling me it’s still full scanning the table:=

 

shared=3D> explain (analyze, buffers)<= /p>

shared-> SELECT objectid::text, n::text, v::text, vt::in= t FROM steve1('{5ab8e0ca-abb5-48c9-a95e-2bb2a375d903,adcbe251-6723-48a8-838= 5-55133fab704a}', 1);

          = ;            &n= bsp;            = ;            &n= bsp;   QUERY PLAN

-----------------------------------------------------------= -----------------------------------------------------

Function Scan on steve1  (cost=3D0.25..10.25 rows=3D10= 00 width=3D100) (actual time=3D56.708..56.970 rows=3D3368 loops=3D1)

   Buffers: shared hit=3D16586 ß= -------= ------

Planning Time: 0.024 ms

Execution Time: 57.316 ms

(4 rows)

 

 

What do I need to c= hange to get the query to use the PK index when executed inside the functio= n?

 

Thanks in advance

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_BL1PR03MB60386561F48885BEEA14C575FAC5ABL1PR03MB6038namp_--