PostgreSQL临时文件的误解

PostgreSQL 13.7

我在所有PostgreSQL服务器上的数据库Postgres中有pg_stat_Statements扩展名.

PMM-代理每分钟向此扩展发出请求:

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增加到300MB并不能解决问题.