From: "Dirschel, Steve" <steve.dirschel@thomsonreuters.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: "pgsql-performance@lists.postgresql.org"
	<pgsql-performance@lists.postgresql.org>
Subject: RE: [EXT] Re: Problem getting query to use index inside a function
Thread-Topic: [EXT] Re: Problem getting query to use index inside a function
Thread-Index: AdxOayMhQeB/QpCCQ1+VoTZN+ZmgxwABLUWAAAEvnQA=
Date: Wed, 5 Nov 2025 16:55:38 +0000
Message-ID: 
 <BL1PR03MB60380B355BF275F767A24245FAC5A@BL1PR03MB6038.namprd03.prod.outlook.com>
References: 
 <BL1PR03MB60386561F48885BEEA14C575FAC5A@BL1PR03MB6038.namprd03.prod.outlook.com>
 <836752.1762359519@sss.pgh.pa.us>
In-Reply-To: <836752.1762359519@sss.pgh.pa.us>
Accept-Language: en-US
Content-Language: en-US
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0: 
 =?us-ascii?Q?lkBiABJGKYodeGSj3O7xwv6catJe3FcTIEKU0+Fl+V175feBIJArRMJYRT4s?=
 =?us-ascii?Q?Eijn0IvcE8GSADUa0lTj+OQ+tkwYhiHZcdJkVNwsjXByuknbLhkufEUKT7er?=
 =?us-ascii?Q?QusmWJLnIDDnrh6ILx9jY+8e9GgVDHMeBWJEufORmdae6Pbbg9QSoSkdiOCf?=
 =?us-ascii?Q?vJMaA/gHdhfh6RIFilqZjzKQmMN2V47ciFlzUq/jyniyZzX1SknQuilezRfd?=
 =?us-ascii?Q?oFH4fLYrB9RqYDlB3eeX5zDVXduhEe061CCNAGq/9SDl9BjY6EPMFSRpU6AB?=
 =?us-ascii?Q?z5QVjkQYtbj3zhdzF5ubEBqEHlW2VOhpsaz8vb3tYbEVZ55/LBJkfUayx0au?=
 =?us-ascii?Q?YZ+0pfY9OQ+JoUa9RZ1LioTvTBwcRba1oONV2dmVUr2xizBlJ/Y/+rSD2Jd4?=
 =?us-ascii?Q?Iofqkppi4yR6qH7nMO8f8ps8hptxRCwATPe5A7uhDyjmXrlkLwtalg0A8LkZ?=
 =?us-ascii?Q?2YSCOVapd+IryV24jvzdYuwJRZpKa4a5WSco//EKC7NiT8Vlj4OGS8nGjr6l?=
 =?us-ascii?Q?JBJ1GY4DUvIluHE5IaT/IlpG8X8b9tXz9VuuKAHr2yS4+axwYbxzf6tuzk7B?=
 =?us-ascii?Q?u77rmt2vzjIXgvlmAsNtWaY+2psoqjeR5abl0PkqMxLGwxe8Ndmt8JyUk4XL?=
 =?us-ascii?Q?Tqnrm2aH1eXhzYU307C6nV8m7VPfmmGQqE2R6QFOdScVfCPa7evZamIJqqcd?=
 =?us-ascii?Q?jigqK3GynRCUb73LDtvG9/nQVniJTA1ektGOSVt5R5M9j0pFTG5kf75QLUdS?=
 =?us-ascii?Q?Vf2A0qLx8L8B6X/PI8GoOPyfGLPvhG8Tf+lvZ0HXmzYsXPHfbTQ9syRt0s8B?=
 =?us-ascii?Q?3hOmZH+QiMRcGlm7r3MJ6Ck2kNZL+C0P2nOxN4E/Adn+zQvl6CrOGBiVGDlm?=
 =?us-ascii?Q?JFsqiFEIIQZKsgRZ8cL2KFBf5T2xp3at/JknmGuwNbsXJwjXxb0NmC+NV3oa?=
 =?us-ascii?Q?65pmdiYZvhaVHMqu8/yKnT8TESxtK+83oT5dXx76jFYl+CJv8CvNyEmzr+YD?=
 =?us-ascii?Q?eR0RGcFKWgIFZiNc1/KLBI7mgWaR+ylHdqR7QHw+PUSrLgjA1IPCvoptkQK4?=
 =?us-ascii?Q?EVbrH4crPTNfKXwpInW2JM1QXwvy0Rbi4H5W9L1fivnIkRVF4HA+HGhGyzw5?=
 =?us-ascii?Q?3JfhqResYH9fsbmmYjDS2axWcsAMnZtLduTwLpakns2ounpnPChy6BjV3jMI?=
 =?us-ascii?Q?rpZ5q5W5OpA2uzaG2N5sstmC+6Hjn/foLrCWpWo1WsH6IujjtB2S8E0po9oy?=
 =?us-ascii?Q?QGPXTdkoMK9iTEfNO9/eRQDKzKDD2pVwkz4NdaGKu1ONB0HA7vgrIQmEfRZz?=
 =?us-ascii?Q?3nA3Oo38WGVZeXB9wNa723WU/9WtQJe5zB08J5gdOSH++xGPfB5wkBjfBBRx?=
 =?us-ascii?Q?LiuwkMXKWG4dHRnZEkue6cfHKvktC90YxEBePx7jox4OWu9h/BJQnvWzezRH?=
 =?us-ascii?Q?tbjmr0qq/KmkNZsMz2o0x79ekgrInJJ8lTB24Zfx+s+mNZ+HA86V1hjTb81T?=
 =?us-ascii?Q?YGXXcMvD0ivF6hWvuApvWXqDlmJK56yYa22KzQ2XkliDczrN8JTqYUj7Xlha?=
 =?us-ascii?Q?I3VsqW4ZwuNcfWLMN3iVnq5+gTvY4vuuFGs3l00yPwVrEAzrLM7jzQfbJIp9?=
 =?us-ascii?Q?/g=3D=3D?=
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
X-MS-Exchange-AntiSpam-ExternalHop-MessageData-ChunkCount: 1
X-MS-Exchange-AntiSpam-ExternalHop-MessageData-0: 
	5bBGkJtk7Xj0QFuCzWc4EFAyH9P/54sBRtDjVEdHldwykKZvJqA+zjIya+eXX9qhWKf9XY7DI3gIvOPTzDQ4R+bcJc89k8Q0UHR/RHsQ8w+XM292jAlNXWsu3So9ipZTOz2pOQCclSXZyEfur65/fQ4egX5NkRTFtB1a8yiLs4FmJkYEsRq6YF5hi8zXUZ7AsMhGUipw5CpgPEYqqoc2U6TQNE0aHl1B8SWHabGaKA4raAXnwcPRtoW5P8v0iapJ0IgQaRN19oGBfpLW2zoKOdgMCuChwZNxkoGtD32LmuTlZz64t/tKPUyGZbQVgRu3xMixtRNXzjVkM0szKJ5JpIj+o6Ba48HEvfMoo0UdjfU6IDSZYrWyPYWgkXPIfnv8UmX9JwAMWwgkj1CKoc6clJXMvHUI1oMLoMMGyEFHRpvN2z7pKId5mryvRPC20ZUhf+51HI5EN3VwU6uO5cKJYBieVTKLcePAlUJvjCf1DRkC075jgzfZ8+42epnugESwdkXTSRTjPuqzhIIsZhFsj670RZ7XoN7hRwIQBlfPTaExJtGCP0GOuPQVGneZiUSV9Klmp4wul0hzpBkPfTN54KmzYEWX8Hq39AchVgGJzDGZv80I/OlgkakoO7742iDWZN0v5fkMlKmivcCWVbICIr07auhg1ro9rnR0xgg72a4=
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: 
 089f5fcf-5915-4b91-8e50-08de1c8c259f
X-MS-Exchange-CrossTenant-originalarrivaltime: 05 Nov 2025 16:55:38.4919
 (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: 
 oCjrc7/bg93LT1cna0mRh6kS8Ugzfnpc5Vb5iS0CA1PqABvHGzJ1rIkdQKr3MALYkkDhQ7NB1wafqDcqR/77xBxUxXiVM4ywARoeCTxSLHJ7ttA9SCYhkiXs9fHCS+Wi
X-MS-Exchange-Transport-CrossTenantHeadersStamped: BY5PR03MB5283
X-Authority-Analysis: v=2.4 cv=R58O2NRX c=1 sm=1 tr=0 ts=690b818f cx=c_pps
 a=NYN33YLW5Jwrh4oiy2kjMw==:117 a=z/mQ4Ysz8XfWz/Q5cLBRGdckG28=:19
 a=lCpzRmAYbLLaTzLvsPZ7Mbvzbb8=:19 a=o7_75jvl1dMYte63:21 a=xqWC_Br6kY4A:10
 a=kj9zAlcOel0A:10 a=6UeiqGixMTsA:10 a=pKFMK-pTKUcA:10
 a=VkNPw1HP01LnGYTKEx00:22 a=Lan83kc4PGmiSJddL_IA:9 a=CjuIK1q_8ugA:10
X-Proofpoint-GUID: V-CCQTszl1SAyVXa5siunJBVnfj3Z07u
X-Proofpoint-Spam-Details-Enc: AW1haW4tMjUxMTA0MDA2MCBTYWx0ZWRfX59s++ylIC5Cu
 tEvrauY3/qhiBfxvnidIDI8+3uRje2XSnBADTZlIHR1fQVKSCS/NJFrthWWOtYMbnZUCM3OXQS+
 scyHD58Y/AlIbU+gKTuvRl/A6TxVhPq2HUdE9MMalmKOf103TYGQiIMTk9Z6Ao6+wLbPl28SDnH
 2Wz1mNqW4V1OvJ/qDTn9lwegNrzEVYNU484GvogWdBZ2Tmd57E+HI7GUQ6FEk1QJ+vwq52Y82HS
 33JgrQOrggQI8xRB9NKh/lmCQdNtIrqKMXbKYo3pQr7zO5x71zKKRUn4dRBp87fSqcgPCpv2mpI
 Csog0BQIwACD/ZH2peMCnCaPutv+ZJLnF6eueP3sRKwLA1A+uOyZVVXvoZsUxoFRYBvubQ/MOPT
 nKWSfYKFirDhSxvg0wvl7Xag6Y4CMQ==
X-Proofpoint-ORIG-GUID: V-CCQTszl1SAyVXa5siunJBVnfj3Z07u
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
 priorityscore=1501 phishscore=0 clxscore=1011 adultscore=0 lowpriorityscore=0
 suspectscore=0 malwarescore=0 impostorscore=0 bulkscore=0 spamscore=0
 classifier=typeunknown authscore=0 authtc= authcc= route=outbound adjust=0
 reason=mlx scancount=1 engine=8.19.0-2510240000 definitions=main-2511040060
List-Id: <pgsql-performance.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-performance@lists.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-performance>
Archived-At: 
 <https://www.postgresql.org/message-id/BL1PR03MB60380B355BF275F767A24245FAC5A%40BL1PR03MB6038.namprd03.prod.outlook.com>
Precedence: bulk

> > Here is the function I'm having difficulties with:

> > CREATE OR REPLACE FUNCTION public.steve1(param_requestid text[],=20
> > param_productid integer DEFAULT 1) RETURNS TABLE(objectid text, n=20
> > 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=20
> > ANY($1)
> >              AND productid=3D$2

> > $function$
> > ;

> > Query:

> > shared=3D> explain (analyze, buffers)
> > shared-> SELECT objectid::text, n::text, v::text, vt::int FROM=20
> > shared-> steve1(ARRAY['5ab8e0ca-abb5-48c9-a95e-2bb2a375d903','adcbe251
> > shared-> -6723-48a8-8385-55133fab704a'], 1);
> >                                                    QUERY PLAN
> > ----------------------------------------------------------------------
> > ------------------------------------------
> > Function Scan on steve1  (cost=3D0.25..10.25 rows=3D1000 width=3D100)=20
> > (actual time=3D42.694..42.942 rows=3D3368 loops=3D1)

> I think you would have better luck if the planner were "inlining"
> this function, which we can see it's not since you get a Function Scan on=
 steve1 rather than the contained query.

> I think the only thing stopping that from happening is that the function =
is (by default) VOLATILE.  Try marking it STABLE so that it can share the c=
alling query's snapshot.

> (v18 should handle such cases better than previous versions, BTW.
> But you'd still be better off marking the function STABLE.)

>			regards, tom lane


Thanks for the reply, but that did not seem to help.  I tried using both th=
e {} and ARRAY[] syntax but both are still full scanning based on the share=
d buffers.  Any other ideas?

shared=3D> ALTER FUNCTION public.steve1 (param_requestid text[], param_prod=
uctid integer) STABLE;
ALTER FUNCTION

shared=3D> explain (analyze, buffers)
shared->        SELECT objectid::text, n::text, v::text, vt::int FROM steve=
1('{83e3326a-62fe-45bc-81e5-1e9fb9a84d31}', 'PJJ', 1, 1);
                                                 QUERY PLAN
---------------------------------------------------------------------------=
----------------------------------
 Function Scan on steve1  (cost=3D0.25..10.25 rows=3D1000 width=3D100) (act=
ual time=3D64.465..64.465 rows=3D0 loops=3D1)
   Buffers: shared hit=3D16572
 Planning Time: 0.033 ms
 Execution Time: 64.485 ms
(4 rows)


shared=3D>
shared=3D> explain (analyze, buffers)
shared-> SELECT objectid::text, n::text, v::text, vt::int FROM steve1(ARRAY=
['83e3326a-62fe-45bc-81e5-1e9fb9a84d31'], 'PJJ', 1, 1);
                                                 QUERY PLAN
---------------------------------------------------------------------------=
----------------------------------
 Function Scan on steve1  (cost=3D0.25..10.25 rows=3D1000 width=3D100) (act=
ual time=3D63.749..63.749 rows=3D0 loops=3D1)
   Buffers: shared hit=3D16569
 Planning Time: 0.043 ms
 Execution Time: 63.766 ms
(4 rows)