Date: Sat, 26 Dec 2020 11:46:35 +0100
From: Dmitry Dolgov <9erthalion6@gmail.com>
To: PostgreSQL-development <pgsql-hackers@postgresql.org>,
	Tom Lane <tgl@sss.pgh.pa.us>, Greg Stark <stark@mit.edu>,
	Pavel Trukhanov <pavel.trukhanov@gmail.com>
Subject: Re: pg_stat_statements and "IN" conditions
Message-ID: <20201226104635.uvzkftzu72zg5qux@localhost>
References: 
 <CA+q6zcWtUbT_Sxj0V6HY6EZ89uv5wuG5aefpe_9n0Jr3VwntFg@mail.gmail.com>
 <20201118160432.hyko73sqc2bf7nkj@localhost>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="2rxgos7csfshja6i"
Content-Disposition: inline
In-Reply-To: <20201118160432.hyko73sqc2bf7nkj@localhost>
Precedence: bulk


--2rxgos7csfshja6i
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

> On Wed, Nov 18, 2020 at 05:04:32PM +0100, Dmitry Dolgov wrote:
> > On Wed, Aug 12, 2020 at 06:19:02PM +0200, Dmitry Dolgov wrote:
> >
> > I would like to start another thread to follow up on [1], mostly to bump up the
> > topic. Just to remind, it's about how pg_stat_statements jumbling ArrayExpr in
> > queries like:
> >
> >     SELECT something FROM table WHERE col IN (1, 2, 3, ...)
> >
> > The current implementation produces different jumble hash for every different
> > number of arguments for essentially the same query. Unfortunately a lot of ORMs
> > like to generate these types of queries, which in turn leads to
> > pg_stat_statements pollution. Ideally we want to prevent this and have only one
> > record for such a query.
> >
> > As the result of [1] I've identified two highlighted approaches to improve this
> > situation:
> >
> > * Reduce the generated ArrayExpr to an array Const immediately, in cases where
> >   all the inputs are Consts.
> >
> > * Make repeating Const to contribute nothing to the resulting hash.
> >
> > I've tried to prototype both approaches to find out pros/cons and be more
> > specific. Attached patches could not be considered a completed piece of work,
> > but they seem to work, mostly pass the tests and demonstrate the point. I would
> > like to get some high level input about them and ideally make it clear what is
> > the preferred solution to continue with.
>
> I've implemented the second approach mentioned above, this version was
> tested on our test clusters for some time without visible issues. Will
> create a CF item and would appreciate any feedback.

After more testing I found couple of things that could be improved,
namely in the presence of non-reducible constants one part of the query
was not copied into the normalized version, and this approach also could
be extended for Params. These are incorporated in the attached patch.

--2rxgos7csfshja6i
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="v2-0001-Prevent-jumbling-of-every-element-in-ArrayExpr.patch"