空闲磁盘空间的减少是`work_mem`设置过低的一个很好的总体指标吗？

Question

据我所知(经过相当多的网上搜索).

1-如果查询的一个组件(排序、连接等)使用比我的work_mem设置更多的内存/内存，或者服务器上所有当前操作使用的总内存超过可用的OS内存，查询将开始写入磁盘。

这是真的吗？

2- Postgres (和许多其他好的DB引擎)使用内存来缓存大量数据，因此查询速度更快；因此，即使服务器没有真正需要内存，服务器也应该指示低空闲内存。因此，除了良好的DB引擎和良好的利用率之外，低空闲内存并不意味着其他任何东西。

这是真的吗？

3-如果上面的#1和#2都是真的，保存所有其他内容，如果我想要一个work_mem设置的板指示符，它的整体操作系统内存太低或者不够，我应该看看服务器空闲磁盘空间是否正在下降？

我想得对吗？

链接：

https://www.postgresql.org/docs/current/static/runtime-config-resource.html

http://patshaughnessy.net/2016/1/22/is-your-postgres-query-starved-for-memory

https://www.enterprisedb.com/monitor-cpu-and-memory-percentage-used-each-process-postgresqlppas-9

https://dba.stackexchange.com/questions/18484/tuning-postgresql-for-large-amounts-of-ram

我知道我可以设置log_temp_files并查看单个临时文件来调优work_mem设置，但我想要一个总体指标，以便在我开始研究超过work_mem设置的临时文件大小之前，确定work_mem是否太低。

我有PostgreSQL 10。

Answer 1

处理查询需要采取多个步骤：

1. 制定(所有)可能的计划
2. 根据调优常量和统计数据估计这些计划的执行成本(根据资源:磁盘I/O、缓冲区、内存、CPU)。
3. 根据调优常数选择“最优”计划
4. 执行选定的计划。

在大多数情况下，预期(step2)需要比work_mem设置更多的work_mem的计划将不会在step3中被选择。(因为“溢出到磁盘”被认为是非常昂贵的)一旦step4检测到它需要更多的work_mem，它的唯一选择就是溢出到磁盘。狗屎会发生..。至少这不依赖于操作系统的页面交换过多提交的内存。)

这些规则非常简单：

• 散列连接通常是最优的，但会消耗内存。
• 不要试图使用比你拥有的内存更多的内存
• 如果期望(Step2)和观察(Step4)内存之间存在差异，则您的统计数据是错误的。你将受到泄漏的惩罚。
• 缺少可用的索引将导致散列联接或seqscans。
• 排序也使用work_mem。其机制也是相似的:错误的估计会产生糟糕的计划。
• CTE's经常/总是(？)物化了。一旦缓冲区空间溢出，这将分裂到磁盘上。
• CTE没有统计数据，也没有指数。

一些准则/建议：

• 使用正确的数据模型(不要去造势)
• 使用正确的PK/FK和安全索引。
• 在对表的结构或数据进行重大修改之后，运行ANALYZE the_table_name;来收集新的统计数据。

监测：

• 检查Postgres日志文件
• 检查查询计划，比较观察到的<->预期
• 监视系统资源的使用情况(在Linux上:通过top/vmstat/iostat)