Greetings,
I want to limit the query text that gets captured in pg_stat_statements. We have sql statements with thousands of values clauses (upwards of 10,000) that run at a 1 second interval. When just a handful are running plus 2 or 3 loads using the same technique (10,000 entry values clauses) querying the pg_stat_statements table gets bogged down (see below). With the pg_stat_statements.max is set to 1000 statements just querying the table stats table seems to impact the running statements! I have temporarily staved off the issue by reducing the max to 250 statements, and I have made recommendations to the development team to cut down the number of values clauses. However, it seems to me that the ability to truncate the captured query would be a useful feature.
I’ve peeked at the source code and I don’t see the track_activity_query_size used (pg_stat_activity.query) which would be one mechanism. I don’t really know what would be the right way to do this or even if it is a good idea, i.e. if limiting that would have a larger impact to the statistics ecosystem…
Thoughts or suggestions?
Regards,
pg
# select length(query) from pg_stat_statements;
length
---------
876153
879385
171
44
3796
873527
<snip>
896454
864538
1869286
938
869891
<snip>
883526
877365
(969 rows)
Time: 9898.411 ms (00:09.898)
# select count(*) from pg_stat_statements;
count
-------
971
(1 row)
Time: 6457.985 ms (00:06.458)
Using showtext:=false shows the impact of the large columns:
# select count(*) from pg_stat_statements(showtext:=false);
count
-------
970
(1 row)
Time: 10.644 ms
Phil Godfrin | Database Administration
NOV
NOV US | Engineering Data
9720 Beechnut St | Houston, Texas 77036
M 281.825.2311
E Philippe.Godfrin@nov.com