Postgres：材料化视图查询时间变长

Question

我刚开始使用Postgres，有一个关于Materialized Views的问题。我目前正在使用Postgres 9.6.0和PGAdmin 4。

我的查询由多个连接组成，当在PGAdmin中执行时，需要大约13秒钟。如果正确设置了索引，则情况是如此。

下一个方法是使用Materialized Views。通过使用Materialized Views并设置适当的索引，我认为运行查询会更快，因为数据将被缓存/临时存储。然而，在运行查询之后，看起来仍需要13秒钟...

除非我对Materialized Views的理解不正确，否则可以有人请解释为什么执行Materialized Views需要与运行查询一样长时间。这可能是PGAdmin导致的问题吗？或者Postgres内部有没有需要设置的选项，以便数据在Materialized Views中持久存在？

感谢任何建议。谢谢。

- rm12345

---

你确定你正在创建一个物化视图（CREATE MATERIALIZED VIEW...），而不仅仅是一个普通视图（CREATE VIEW...）吗？刷新物化视图大约需要13秒钟，但在刷新后，选择操作应该更快。 - Jeremy

你改变了查询使用MView了吗？Postgres（不像Oracle）不会自动重写查询，即使有一个恰好具有相同查询的mview。 - user330315

嗨@Jeremy，是的，我确实使用了CREATE MATERIALIZED VIEW mv_view AS <query>语句。有趣的是，对这个视图运行SQL语句并没有起到帮助作用。 - rm12345

请注意，如果一个查询很慢，请务必提供查询本身以及EXPLAIN (ANALYZE, BUFFERS) <query>的输出。 - Ancoron

Answer 1

这是一个简单情况的预期行为。这意味着物化视图（通过对索引查询的结果）的性能完全像其来源的索引。如果您考虑一下，这是有很多道理的。 物化视图并不一定比底层查询在第一次访问时性能更好。数据装配的复杂度可能是微不足道的。大量使用索引的联接要比复杂的条件集或需要函数处理或排序的东西少得多。 在这种情况下，您发现的是底层查询与相同数据集的物化视图一样好。您可能想考虑此情况下的简单视图。

- Kirk Roybal

---

有趣。我不完全理解，因为物化视图的结果应该已经被缓存了......所以检索速度不应该更快吗？ - rm12345

物化视图是由表支持的。您甚至可以对它们创建索引。在第一次访问后，结果有可能也存在于共享缓冲区中。在某些情况下，这也可以帮助访问，但不能保证它会被缓存。 - Kirk Roybal

好的，只是确认一下，结果并不总是被缓存吗？因为那不是一个物化视图。将查询结果存储到一个表中，只在该表上执行查询是否更好呢？ - rm12345

你们两个都弄错了一些事情，而且@KirkRoybal在不了解原始查询、视图或数据的情况下做出了太多的假设。这只是胡乱猜测，抱歉。当使用物化视图时，访问它可能比创建它的查询要慢，但这需要根据OP提供的实际数据进行验证。 - Ancoron

我回答了简单情况下的问题，没有做任何假设，并明确表示了这一点。@ancoron你的批评是不合理的。我要向你挑战解释一下“你们两个都有些地方错了”，这正是你指责其他人所犯的假设错误。 - Kirk Roybal

@rm12345，Materialized不一定要在缓冲区中。在这种情况下，它首先被实体化到磁盘上，根据对该磁盘实体的读取流量，可能会进一步实体化到缓冲区中。如果只将其实体化到缓冲区中，那么它将是暂时的，而这并不是设计目标。 - Kirk Roybal