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+ZmgxwABLUWAAAEvnQAABRDrAAAB/Dfg
Date: Wed, 5 Nov 2025 20:18:18 +0000
Message-ID: 
 <BL1PR03MB6038D428E038A78B7807F749FAC5A@BL1PR03MB6038.namprd03.prod.outlook.com>
References: 
 <BL1PR03MB60386561F48885BEEA14C575FAC5A@BL1PR03MB6038.namprd03.prod.outlook.com>
 <836752.1762359519@sss.pgh.pa.us>
 <BL1PR03MB60380B355BF275F767A24245FAC5A@BL1PR03MB6038.namprd03.prod.outlook.com>
 <873496.1762370260@sss.pgh.pa.us>
In-Reply-To: <873496.1762370260@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?GieMewuB1WLixhWXg25ODPmpW3ajCMJsOWG9jolK8wEp6iLmWpSmwtbYd6hq?=
 =?us-ascii?Q?qCa3cX6thbEFQMmzd0EluWR/7P8SxwjsSvZXeb1YrwEs3geKSAnJMcu4kgTv?=
 =?us-ascii?Q?kSLg3Elgxsna6U3odLXGfXHZ5GYZ/phb3AojiIH+22HHMFmIJYJFm4N/AhGp?=
 =?us-ascii?Q?oL4bxDil6UP1mohUkC6WZu/U72svhID0w8W4/3Nk0kx8awtSNe94HfND/imC?=
 =?us-ascii?Q?xKyhRUYxrD1xFP/0KvtkXkfW3yYjwrYSzLadzgoQ1934tx0yKs/p0NiLvB7q?=
 =?us-ascii?Q?ri3AGWELyaQ0g6B/b3KJa01Zw1JljF8rozcUHtYzaYdu1weB9cikq0Wf+DPq?=
 =?us-ascii?Q?yapLy+BrBOxD+xdUMWyVjRdV2VXmqWQowTGveLDyU7QjxfzSctukiEg+W8uw?=
 =?us-ascii?Q?GVnYioxh4KPEA+2Grz3zTCnfDm+UVdrBx6nbLqj9VLeMpNpxCFP4EY338RpH?=
 =?us-ascii?Q?Ak9SM6+iwvqrD1p9G/NKlSrJ8nZcrrmdeQ9p/erbEt4frQrWE8hYjZJ9SQfh?=
 =?us-ascii?Q?iZmIsaoT2L61wJ477ifgINtHBe3Jhf+AqGziZmTWWrrefPF8avT0/TZdJLS6?=
 =?us-ascii?Q?M5lpEGVrvgouqCm4qs5+Ij5YCQQZ3DcSPPIVrshQsTjYU/5Nnaames/6ZOvS?=
 =?us-ascii?Q?8lsAe1Zme41NuzVpsy3uIYRWqRjbKLvoIT0aIm5G2hT3FssEgTiQSRN9V3IJ?=
 =?us-ascii?Q?RG9ri2GB1fqeYaPJpZv7bc0EXJo8n8egcQWpwE5H18giqDSKl/ldqXOihOsa?=
 =?us-ascii?Q?odsI7G04pnyFlOMRFlxRyfI/JnqCvSZMWj3KGs8QLm+ZpwQpcHOpqnTcF3hm?=
 =?us-ascii?Q?s6B7VApvbD+f83cZu5jcvF5+1cyQd6mtnsyLe6boprigNC8+wDiPYg7ewWoD?=
 =?us-ascii?Q?uAEaTLQMaiEDS2m7gKVnqQwCTgvya26/RJ+gNZVhRgM8O8DElebpuLMcSxxe?=
 =?us-ascii?Q?KnWwqBwvQmdQQsMs+rmDPyKcPQ4l31bIILciubVVQuVzoNsLltu5gRx7waYJ?=
 =?us-ascii?Q?qypl024mNKn7RWbbyOHEOP2DbWTtre7z9wWw0OdFlQAZAAlL1BuGXX3AldjL?=
 =?us-ascii?Q?T305N0XB3p93ruDznmTgrf/k84Mq2jgf/rbZR4Cw2AH5qsLHaB4K+u9JuxHl?=
 =?us-ascii?Q?fqq5qT/AQN8KK2Wg3h1ZWcWpf3KqdhLhdNpfqr1C0RCmA/wtk9c/X+QZinLO?=
 =?us-ascii?Q?Bv6fg01XeYYfnq3ZiqujfHDswb8z1oidHdDfUMpbGdBL4e1XSRrxpgNq0aKx?=
 =?us-ascii?Q?igCF4qNP4V7GL5eYwbytpHysXpqosUay0GhD9yGsacjUfKWxjvlwoMvnGmY/?=
 =?us-ascii?Q?jtAJlx7iXlr/voY89epeVvPFoyq4DDSOsjQEa/a+ZvI1Gxks79YvxQ8Koif7?=
 =?us-ascii?Q?40yGt1YmfPNEAeZNSLIsgg2wcXMQ9Q86sByrlUVWTm5NdrPE1e1L6agHifjc?=
 =?us-ascii?Q?FhN41skwwhPdq1kMI9eiBdNQrOaUgOr4+7JYQmnpp2M3KahIdlpmlSJkgljS?=
 =?us-ascii?Q?uzGPcXybb7WdrUgTKRs3dSJvyVPi+SwERv6Hs2QGZbZ/k+xICPnNxCoCYINw?=
 =?us-ascii?Q?nZt0utKkjW6kKlpWmKETM/0AQOz7aRt7268iCWVWG1yFJva0UbQh2xB6SLbv?=
 =?us-ascii?Q?/A=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: 
	mpfQ5iNfb6qBcyKaqP44sh6XbY+lYu1en4QBybWA9w4ZALXV/cAeib647IDpEVjq2LOobP4rWsActRGj6CMsfxUoxjhwDPr/eKYchgisU7aMOWGic2YqHEzdwqTsGbd8AUbLoK44bQOF436NgKPIM5pjkbVopW04t7V5RrNTXe5mFNj1C1r4uqSk4aFa9fRat+P0FZsdB1dc25OF6IroDGgPuKyHbBMlpH4iI+S/TPfASYQUxx2IKKFs5PZtlQJcrmzIt7OsQo2XU4yD/m09sp7UsMb4ORwA4QnWrqt3PmpeRu1aq3XTtsG0sN72jUB+/9xetSZUDp0pBymMRFQNwSr9eMVNPVmKBimBlod2Zv89lTiImYtSUvJWvykiJ0bQASqkMa2caS/BMzFXHMf++u5S97viKrYheo2dIiPq875Ob7B4oou/6fwOwpzCYtkpOQlfIuPSb16bwH6P8YcCp2KVB7w3GPe5cjkbwcyV0jVSSosQq2o20CiCOPN+IljJk+kmVkHMnrv6AGEdWDq0aN4b0OwX0tD6xbNbRhFTzse6Vx0ReGvi9sbedhtwCDV5lYWAAOyzUT6M0KjzJhRryj01wdzSa/9qKAwfK96/cYMSahNi/WbEzUAgm8/7K/TJB4ENf6QMjnBs6fmfKXiz8+biKDQ5LKO6d0XYqM9cbtY=
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: 
 ac3242fe-ef35-4b4e-fa0d-08de1ca87582
X-MS-Exchange-CrossTenant-originalarrivaltime: 05 Nov 2025 20:18:18.4771
 (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: 
 eXuzw5YWzl9WC8uB0r3ULvvNS+r4kmY7h4NP08SZ/Ut6LSV1sUUGhbidjK3ksMwNrQ1zm6FjJ3Xm+HuFb2WuOLAikgwDdxzXys0z/Qo2ua9Uj5gdBR177ZtGqgyL7ur6
X-MS-Exchange-Transport-CrossTenantHeadersStamped: LV9PR03MB8389
X-Proofpoint-Spam-Details-Enc: AW1haW4tMjUxMTA1MDExNCBTYWx0ZWRfX2NzHqgiru2Km
 LieZz0ssNmW8x1DtXPxYuFNXobehL8Zryk183Y+OpNUOafhI2JDNt1nRnOgTxXCp1cZLm+GWw1S
 Djo77NHWCBAMG+Rx4YKUuKJcVt+6R4Sa5NbYDbnTijgUSf/1LoahYnp07FzpAQvSUCath9Gzl99
 j1tdPa9/8tTGvCDB9cmlpVRg9CW50QYqzhNGoVDzFP3Pqa48r0BZGxpAXDocCxN9J+jJ9fKbQkc
 crn64FMambANZoH0KB+5HioG1+JBBJDi+BnZmqzJoGGqLrlJQRZBHJzikqxOdunD8hzdL9jwNdK
 ZujdrELpUpcjaR44ndOh5gTJv3YGC/hqCPVCuaYl+fUC6UegqqXqbCSnQbKHLA/HhwLH5Hyj2Rm
 kW+onV8tjZ27tont2xunUltXDDVQ8Q==
X-Proofpoint-ORIG-GUID: dILuEy90uQPv-3RJYjDZmrmfau-t7cCA
X-Authority-Analysis: v=2.4 cv=dKGrWeZb c=1 sm=1 tr=0 ts=690bb124 cx=c_pps
 a=V3ntuBz9Vw3KuD7vyzUphg==: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=FqEwLnM4QWL-3rhyxcIA:9 a=CjuIK1q_8ugA:10
X-Proofpoint-GUID: dILuEy90uQPv-3RJYjDZmrmfau-t7cCA
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_07,2025-11-03_03,2025-10-01_01
X-Proofpoint-Spam-Details: rule=outbound_notspam policy=outbound score=0
 bulkscore=0 priorityscore=1501 clxscore=1015 malwarescore=0 suspectscore=0
 impostorscore=0 phishscore=0 lowpriorityscore=0 spamscore=0 adultscore=0
 classifier=typeunknown authscore=0 authtc= authcc= route=outbound adjust=0
 reason=mlx scancount=1 engine=8.19.0-2510240000 definitions=main-2511050114
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/BL1PR03MB6038D428E038A78B7807F749FAC5A%40BL1PR03MB6038.namprd03.prod.outlook.com>
Precedence: bulk


>> Thanks for the reply, but that did not seem to help.

> I tried to replicate this as follows:

> --- CUT ---
> create table request(objectid text, productid int, data jsonb); create in=
dex on request(objectid, productid);

> CREATE OR REPLACE FUNCTION public.steve1(param_requestid text[], param_pr=
oductid 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$
> stable ;

> explain
> SELECT objectid::text, n::text, v::text, vt::int FROM steve1(ARRAY['5ab8e=
0ca-abb5-48c9-a95e-2bb2a375d903','adcbe251-6723-48a8-8385-55133fab704a'], 1=
);
> --- CUT ---

> and I got:

>                                                                      QUER=
Y PLAN                                                                    =
=20
> -------------------------------------------------------------------------=
---------------------------------------------------------------------------
>  Nested Loop  (cost=3D0.15..11.42 rows=3D100 width=3D100)
>    ->  Index Scan using request_objectid_productid_idx on request r  (cos=
t=3D0.15..8.17 rows=3D1 width=3D64)
>          Index Cond: ((objectid =3D ANY ('{5ab8e0ca-abb5-48c9-a95e-2bb2a3=
75d903,adcbe251-6723-48a8-8385-55133fab704a}'::text[])) AND (productid =3D =
1))
>    ->  Function Scan on jsonb_array_elements i  (cost=3D0.01..1.00 rows=
=3D100 width=3D32)
> (4 rows)

> which is what I expected from successful inlining of the function.
> So there are some moving parts in your situation that you've not told us =
about.

>			regards, tom lane

Hi Tom,

I ran your code and got similar results so I agree there is more for me to =
dig into.  I see STABLE has a limit of not allowing DML in the function.  I=
 am testing against a very simple function here but we have many other func=
tions with the same problem but those also contain DML so even if I got the=
 STABLE to work in this one test case it does not appear I could use in all=
 of my functions with this problem.  Are there other options here besides t=
he STABLE option that would work for functions that also contain DML?

Thanks
Steve