Hi,
Thanks for your answer.
> I don't think that it would move the needle much. Deallocating entries is very
> expensive, even when the query text file isn't being cleaned up, as it needs to
> sort all entries by usage to remove the least recently used all with an
> exclusive pgss->lock. The real solution is probably to rely on the new
> pluggable statistic architecture rather than using the hash table / query text
> file.
I'm sorry I left out some details earlier. I found that the garbage collect backend process was in the loop of
gc_qtextswhile for a long time. The main backtrace is below.
```
#0 0x00007fc528d6aba0 in __write_nocancel () from /lib64/libc.so.6
#1 0x00007fc528cf52f3 in _IO_new_file_write () from /lib64/libc.so.6
#2 0x00007fc528cf5b90 in __GI__IO_file_xsputn () from /lib64/libc.so.6
#3 0x00007fc528cea7e2 in fwrite () from /lib64/libc.so.6
#4 0x00007fc529199dd5 in gc_qtexts () at pg_stat_statements.c:2380
#5 pgss_store
#6 0x00007fc52919a2b8 in pgss_post_parse_analyze (query=0x1e9aed8, pstate=0x178a220) at pg_stat_statements.c:900
```
So I think the main reason for this long lock holding is that the I/O operation takes a long time because of these very
longqueries.
In my production environment. pg_stat_statement.max is set to 1000. I found that when this problem occurred, gc took
morethan 20 seconds. If I limit the length of a single sql to 8k, it will only take 1.79 seconds.
> Isn't the pg_stat_statements_info.dealloc counter enough to figure out the root
> issue?
Only in my opinions, pg_stat_statements_info.dealloc doesn't reflect how long it takes for garbage collect. Earlier
whenI was checking the logs for abnormal periods, there is only some slow parse logging like below.
> duration: 20834 ms parse S0_1: …...
Best regards,
Tinghai Zhao
