pg_stat_statements and "IN" conditions
Dmitry Dolgov <9erthalion6@gmail.com>
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>
Date: 2020-08-12T16:19:02Z
Lists: pgsql-hackers
Commits
Same data as JSON:
GET /api/v1/messages/:b64id/commits
the thread's linked commits as JSON, with link sources.
API reference →
-
Introduce squashing of constant lists in query jumbling
- 62d712ecfd94 18.0 landed
-
Make documentation builds reproducible
- b0f0a9432d0b 17.0 cited
-
Include values of A_Const nodes in query jumbling
- 9ba37b2cb6a1 16.0 cited
-
Teach planner about more monotonic window functions
- 456fa635a909 16.0 cited
-
Split up guc.c for better build speed and ease of maintenance.
- 0a20ff54f5e6 16.0 cited
Attachments
- 0001-Reduce-ArrayExpr-into-const-array.patch (application/octet-stream) patch 0001
- 0001-Limit-jumbling-for-repeating-constants.patch (application/octet-stream) patch 0001
Hi
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.
# Reducing ArrayExpr to an array Const
IIUC this requires producing a Const with ArrayType constvalue in
transformAExprIn for ScalarArrayOpExpr. This could be a general improvement,
since apparently it's being done later anyway. But it deals only with Const,
which leaves more on the table, e.g. Params and other similar types of
duplication we observe when repeating constants are wrapped into VALUES.
Another point here is that it's quite possible this approach will still require
corresponding changes in pg_stat_statements, since just preventing duplicates
to show also loses the information. Ideally we also need to have some
understanding how many elements are actually there and display it, e.g. in
cases when there is just one outlier query that contains a huge IN list.
# Contribute nothing to the hash
I guess there could be multiple ways of doing this, but the first idea I had in
mind is to skip jumbling when necessary. At the same time it can be implemented
more centralized for different types of queries (although in the attached patch
there are only Const & Values). In the simplest case we just identify sequence
of constants of the same type, which just ignores any other cases when stuff is
mixed. But I believe it's something that could be considered a rare corner case
and it's better to start with the simplest solution.
Having said that I believe the second approach of contributing nothing to the
hash sounds more appealing, but would love to hear other opinions.
[1]: https://www.postgresql.org/message-id/flat/CAF42k%3DJCfHMJtkAVXCzBn2XBxDC83xb4VhV7jU7enPnZ0CfEQQ%40mail.gmail.com