Re: pg_stat_statements and "IN" conditions
Sami Imseih <samimseih@gmail.com>
From: Sami Imseih <samimseih@gmail.com>
To: Álvaro Herrera <alvherre@alvh.no-ip.org>
Cc: Dmitry Dolgov <9erthalion6@gmail.com>,
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>
Date: 2025-02-11T18:00:27Z
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
I do not have an explanation from the patch yet, but I have a test
that appears to show unexpected results. I only tested a few datatypes,
but from what I observe, some merge as expected and others do not;
i.e. int columns merge correctly but bigint do not.
"""
show pg_stat_statements.query_id_const_merge ;
create table foo (col_int int, col_smallint smallint, col_bigint
bigint, col_float float, col_text text, col_varchar varchar);
select from foo where col_int in (1, 2, 3);
select from foo where col_int in (1, 2, 3, 4);
select from foo where col_int in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);
select from foo where col_smallint in (1, 2, 3);
select from foo where col_smallint in (1, 2, 3, 4);
select from foo where col_bigint in (1, 2, 3);
select from foo where col_bigint in (1, 2, 3, 4);
select from foo where col_bigint in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);
select from foo where col_float in (1, 2, 3);
select from foo where col_float in (1, 2, 3, 4);
select query, queryid, calls from pg_stat_statements where query like
'select from foo where%' order by 1 desc ;
"""
postgres=# show pg_stat_statements.query_id_const_merge ;
pg_stat_statements.query_id_const_merge
-----------------------------------------
on
(1 row)
....
.......
..........
postgres=# select query, queryid, calls from pg_stat_statements where
query like 'select from foo where%' order by 1 desc ;
query
| queryid | calls
-------------------------------------------------------------------------------+----------------------+-------
select from foo where col_smallint in (...)
| -2065640271713949220 | 2
select from foo where col_int in (...)
| 2911888824129257715 | 3
select from foo where col_float in ($1, $2, $3, $4)
| -6847088148705359339 | 1
select from foo where col_float in ($1, $2, $3)
| 1631437678183488606 | 1
select from foo where col_bigint in ($1, $2, $3, $4, $5, $6, $7, $8,
$9, $10) | 3174053975478689499 | 1
select from foo where col_bigint in ($1, $2, $3, $4)
| -5236067031911646410 | 1
select from foo where col_bigint in ($1, $2, $3)
| -5529594240898645457 | 1
(7 rows)
---
Sami