PostgreSQL 13.7
我在所有postgresql服务器上的数据库postgres中有pg_stat_statements扩展。
Pmm-agent每分钟向此分机发出一次请求：

SELECT /* pmm-agent:pgstatstatements */ pg_stat_statements.userid, pg_stat_statements.dbid, pg_stat_statements.queryid, pg_stat_statements.query, pg_stat_statements.calls, pg_stat_statements.total_exec_time, pg_stat_statements.rows, pg_stat_statements.shared_blks_hit, pg_stat_statements.shared_blks_read, pg_stat_statements.shared_blks_dirtied, pg_stat_statements.shared_blks_written, pg_stat_statements.local_blks_hit, pg_stat_statements.local_blks_read, pg_stat_statements.local_blks_dirtied, pg_stat_statements.local_blks_written, pg_stat_statements.temp_blks_read, pg_stat_statements.temp_blks_written, pg_stat_statements.blk_read_time, pg_stat_statements.blk_write_time FROM pg_stat_statements WHERE queryid IS NOT NULL AND query IS NOT NULL

在我的一个服务器上，这个请求导致了临时文件的使用。

EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
"Function Scan on pg_stat_statements  (cost=0.00..10.00 rows=990 width=168) (actual time=1068.291..1207.841 rows=3401 loops=1)"
"  Filter: ((queryid IS NOT NULL) AND (query IS NOT NULL))"
"  Buffers: temp read=30382 written=30382"
"Settings: effective_cache_size = '24GB', effective_io_concurrency = '200', max_parallel_workers = '12', max_parallel_workers_per_gather = '6', random_page_cost = '1.1', temp_buffers = '16MB', work_mem = '40MB'"
"Planning:"
"  Buffers: shared hit=6"
"Planning Time: 0.153 ms"
"Execution Time: 1244.587 ms"

在另一个服务器上，此查询的execution_plan是正常的，例如：

"Function Scan on pg_stat_statements  (cost=0.00..10.00 rows=990 width=168) (actual time=0.939..1.064 rows=364 loops=1)"
"  Filter: ((queryid IS NOT NULL) AND (query IS NOT NULL))"
"Settings: cpu_index_tuple_cost = '0.0005', effective_cache_size = '16GB', effective_io_concurrency = '200', max_parallel_workers = '10', max_parallel_workers_per_gather = '6', parallel_tuple_cost = '0.05', random_page_cost = '1.1', temp_buffers = '16MB', work_mem = '153MB'"
"Planning:"
"  Buffers: shared hit=6"
"Planning Time: 0.140 ms"
"Execution Time: 2.224 ms"

查询中没有排序或联接，为什么使用临时文件？
将work_mem增加到300 MB并没有解决问题。