postgreSQL-pg_class问题 [英] postgreSQL - pg_class question

问题描述

PostgreSQL将有关表的统计信息存储在名为pg_class的系统表中。查询计划者为每个查询访问该表。这些统计信息只能使用analyze命令进行更新。如果analyze命令不经常运行，则此表中的统计信息可能不准确，并且查询计划程序可能做出的决策不当，可能会降低系统性能。查询计划者的另一种策略是为每个查询生成这些统计信息（包括选择，插入，更新和删除）。这种方法将使查询计划者可以拥有最新的统计信息。

PostgreSQL stores statistics about tables in the system table called pg_class. The query planner accesses this table for every query. These statistics may only be updated using the analyze command. If the analyze command is not run often, the statistics in this table may not be accurate and the query planner may make poor decisions which can degrade system performance. Another strategy is for the query planner to generate these statistics for each query (including selects, inserts, updates, and deletes). This approach would allow the query planner to have the most up-to-date statistics possible.

为什么postgres总是依赖pg_class？

Why postgres always rely on pg_class instead?

推荐答案

pg_class不它不包含计划者所需的所有统计信息，而仅包含有关表结构的信息。由analyze命令生成的统计信息包含有关每列中存在的值的信息，因此在执行以下命令时：

pg_class doesn't contain all the statistics needed by the planner, it only contains information about the structure of the table. Statistics generated by analyze command contain information about values existing in each column so when executing a command like:

SELECT * FROM tab WHERE cname = "pg";

计划者知道表中有多少行，并且有多少行的值是 pg列cname。这些信息在pg_class中不存在。

the planner knows how much rows are in the table and how many rows have the value "pg" in the column cname. These information does not exist in pg_class.

PostgreSQL的另一个不错的功能是autovacuum，在99,9999％的情况下，应该启用它，以便数据库尽快实现统计信息一些（可以在配置文件中定义）行数更改。这样可以将由于表统计信息错误而导致执行计划错误的可能性降到最低。

Another nice feature of PostgreSQL is autovacuum, in 99,9999% of cases it should be enabled so the database actualizes statistics as soon as some (can be defined in config file) number of rows change. That minimizes the chance of wrong execution plan because of wrong table statistics.

这篇关于postgreSQL-pg_class问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！