Date: Fri, 14 Feb 2025 23:12:25 +0800
From: Julien Rouhaud <rjuju123@gmail.com>
To: Dmitry Dolgov <9erthalion6@gmail.com>
Cc: Sami Imseih <samimseih@gmail.com>,
	=?iso-8859-1?Q?=C1lvaro?= Herrera <alvherre@alvh.no-ip.org>,
	Kirill Reshke <reshkekirill@gmail.com>,
	Sergei Kornilov <sk@zsrv.org>, yasuo.honda@gmail.com,
	tgl@sss.pgh.pa.us, smithpb2250@gmail.com, vignesh21@gmail.com,
	michael@paquier.xyz, nathandbossart@gmail.com, stark.cfm@gmail.com,
	geidav.pg@gmail.com, marcos@f10.com.br, robertmhaas@gmail.com,
	david@pgmasters.net, pgsql-hackers@postgresql.org,
	pavel.trukhanov@gmail.com, Sutou Kouhei <kou@clear-code.com>
Subject: Re: pg_stat_statements and "IN" conditions
Message-ID: <Z69dWcGRLaE1C8mb@jrouhaud>
References: <Z68MtDCGVZi-Qqry@jrouhaud>
 <202502140936.lni5gtrycnzf@alvherre.pgsql>
 <Z68Tba4x2N2FaER_@jrouhaud>
 <7vjscmc3ybcibtyetdufrevyyl2hxpcpnofn2eygnne46owmql@gofozgqyq722>
 <CAA5RZ0st9R2V+E_QnVx2KivJFZbYgDVsm70TS=bO8T7XByFfZA@mail.gmail.com>
 <nwseuxutrhemosezhwvzyjdnwvpdrv7gmwqdj6cqc55xdrpnm6@h5pw65h4elkp>
 <Z69VsdpMfHZCuMK5@jrouhaud>
 <kxufplw6bh2qmlcdpph7oeqmmeqrflhujv3z6nejos6lomfli3@bfksqoyetq3n>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <kxufplw6bh2qmlcdpph7oeqmmeqrflhujv3z6nejos6lomfli3@bfksqoyetq3n>
Archived-At: <https://www.postgresql.org/message-id/Z69dWcGRLaE1C8mb%40jrouhaud>
Precedence: bulk

On Fri, Feb 14, 2025 at 03:56:32PM +0100, Dmitry Dolgov wrote:
> > On Fri, Feb 14, 2025 at 10:39:45PM GMT, Julien Rouhaud wrote:
> > There seems to be an off-by-1 error in parameter numbering when merging them.
>
> There are indeed three constants, but the second is not visible in the
> query text. Maybe makes sense to adjust the number in this case, let me
> try.

Thanks!
>
> > Note that the query text as-is can still be successfully be used in an EXPLAIN
> > (GENERIC_PLAN), but it might cause problem to third party tools that try to do
> > something smarter about the parameters.
>
> Since the normalized query will be a valid one now, I hope that such
> cases will be rare. On top of that it always will be option to not
> enable constants squashing and avoid any troubles.

It might not always be an option.  I have seen application that create
thousands of duplicated queryids just because they have a non deterministic
amount of parameters they put in such IN () clauses.  If that leads to a total
number of unique (dbid, userid, queryid, toplevel) too big for a reasonable
pg_stat_statements.max, they the only choice might be to enable the new merging
parameter or deactivating pg_stat_statements.

> Or do you have some
> particular scenario of what might be problematic?

I don't have a very specific scenario.  It's mostly for things like trying to
"un-jumble" a query, you may need to loop through the parameters and a missing
number could be problematic.  But since the overall number of parameters might
change from execution to execution that's probably the least of the problems to
deal with with this merging feature.