PostgreSQL临时表

Question

我需要执行250万次查询。这些查询会生成一些行，我需要对这些行的列使用 AVG(column)函数获得平均值，并使用该AVG筛选表中所有低于平均值的数据。然后，我需要将这些筛选结果 INSERT 到另一个表中。

唯一能够以合理的效率完成这种操作的方法，似乎是通过为每个查询-主进程Python线程创建一个TEMPORARY TABLE。我希望这些TEMPORARY TABLE不会被持久化到硬盘上（完全不写入），并且除非它们超出工作内存，否则将始终保留在内存（RAM）中。

我想知道临时表是否会产生磁盘写入（这将干扰插入操作，即整个过程变慢）。

- Nicholas Leonard

---

你在这里的问题是什么？ - Tim

抱歉，我想知道临时表是否会产生磁盘写入（这可能会干扰插入操作，即整个过程变慢）。谢谢！ - Nicholas Leonard

好的，我刚刚仔细阅读了。看起来临时表确实会导致一些磁盘写入开销...但我仍然想知道是否在磁盘上保存了整个表的副本，还是只有持久化的元数据？ - Nicholas Leonard

Answer 1

请注意，在Postgres中，临时表的默认行为是不会自动删除，并且数据在提交时会被持久化。请参见ON COMMIT。

然而，临时表在数据库会话结束时会被删除：

临时表会在会话结束时自动删除，或者可选择在当前事务结束时删除。

你需要考虑多个因素：

• 如果你想在事务结束时显式地DROP一个临时表，请使用CREATE TEMPORARY TABLE ... ON COMMIT DROP语法来创建它。
• 在连接池的情况下，一个数据库会话可能跨越多个客户端会话；为了避免CREATE中的冲突，您应该删除临时表——要么在将连接返回到池中之前（例如通过在事务中执行所有操作并使用ON COMMIT DROP创建语法），或者根据需要删除（通过在任何CREATE TEMPORARY TABLE语句之前加上相应的DROP TABLE IF EXISTS，这也有一个优点，即可以在事务外工作，例如如果连接在自动提交模式下使用）。
• 当使用临时表时，有多少内容可以放入内存中，然后才会溢出到磁盘上？请参见postgresql.conf中的temp_buffers选项。
• 在经常使用临时表时还有其他需要担心的事情吗？建议在DROP临时表后进行VACUUM，以清除目录中的任何死元组。当使用默认设置（auto_vacuum）时，Postgres会每隔3分钟自动进行VACUUM。

此外，与您的问题无关（但可能与您的项目相关）：请记住，如果您必须在填充临时表后运行查询，则最好创建适当的索引，并在完成插入后对相关的临时表发出ANALYZE。默认情况下，基于成本的优化器将假定新创建的临时表具有约1000行，如果临时表实际包含数百万行，则可能导致性能不佳。

- Vlad R

---

即使您只在临时表上进行两次SELECT操作，每次创建临时表时投资几毫秒来创建索引+分析可能会在将其他表与临时表连接时节省大量时间 - 将查询手动放置在PgAdminIII中并使用“查询/解释（F7）”功能。 - vladr

ANALYZE的开销平均为100毫秒，您可以按表/列进行配置。为了使优化器不做任何愚蠢的假设（例如将包含一百万行的表看作只包含100行并对其进行10次扫描），您绝对需要进行ANALYZE操作。 :) - vladr

换句话说，如果没有执行ANALYZE操作，你不能说你在某种程度上鼓励了优化器，因为优化器无条件地使用ANALYZE数据来做出决策。 - vladr

只使用 enable_seqscan 来调试 CBO。试图通过设置此项来说服 postgres 使用索引，就像用大锤修理电视或用脑叶切除术治疗头痛一样。此外，在数据库的生命周期中，并不仅限于“使用索引或不使用索引”这一个问题。 - vladr

比如说，“使用带索引查找的嵌套循环”和“使用哈希聚合”（而哈希聚合可能比索引查找快得多！），这种问题只能通过分析统计数据来回答，而不能通过某些“不执行表扫描”的配置变量来回答。 - vladr

Answer 2

临时表只有一个保障 - 在会话结束时它们将被删除。对于小表，您可能会发现大部分数据都在后备存储中。但对于大表，我保证数据会定期刷新到磁盘上，因为数据库引擎需要更多的工作空间来处理其他请求。

编辑： 如果你绝对需要RAM-only（仅存储在内存中）的临时表，可以在RAM磁盘上为数据库创建一个表空间（例如/dev/shm），这样可以减少磁盘IO操作，但请注意，创建临时表时DB引擎会将表列表刷新到稳定存储器上，此过程可能会造成物理磁盘写入。

- Adam Hawes

---

临时表也不记录WAL日志。http://rhaas.blogspot.com/2010/05/global-temporary-and-unlogged-tables.html - shusson