如何加快 PostgreSQL 表中的行计数? [英] How do I speed up counting rows in a PostgreSQL table?
问题描述
我们需要计算 PostgreSQL 表中的行数.在我们的例子中，不需要满足任何条件，如果能显着提高查询速度，那么获得行估计是完全可以接受的.
We need to count the number of rows in a PostgreSQL table. In our case, no conditions need to be met, and it would be perfectly acceptable to get a row estimate if that significantly improved query speed.
基本上，我们希望 Basically, we want 快速估算: 不过，有几个警告.一方面， There are several caveats, though. For one, 如果您没有对表名进行架构限定，则转换为 If you do not schema-qualify the table name, a cast to 转换为 The cast to 此外， Also, 以及对 And a query on 这篇关于如何加快 PostgreSQL 表中的行计数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！select count(id) from 尽可能快地运行，即使这意味着无法获得准确的结果.
select count(id) from <table> to run as fast as possible, even if that implies not getting exact results.推荐答案
SELECT reltuples FROM pg_class WHERE relname = 'my_table';
relname 在 pg_class 中不一定是唯一的.在数据库的多个模式中可以有多个具有相同 relname 的表.明确:relname is not necessarily unique in pg_class. There can be multiple tables with the same relname in multiple schemas of the database. To be unambiguous:SELECT reltuples::bigint FROM pg_class WHERE oid = 'my_schema.my_table'::regclass;
regclass 会观察当前的 search_path 选择最佳匹配.如果该表在 search_path 中的任何模式中不存在(或无法看到)，您会收到一条错误消息.请参阅手册中的对象标识符类型.regclass observes the current search_path to pick the best match. And if the table does not exist (or cannot be seen) in any of the schemas in the search_path you get an error message. See Object Identifier Types in the manual.bigint 可以很好地格式化 real 数字，特别是对于大计数.bigint formats the real number nicely, especially for big counts.reltuples 可能或多或少已经过时.有一些方法可以在一定程度上弥补这一点.使用新的和改进的选项查看稍后的答案:reltuples can be more or less out of date. There are ways to make up for this to some extent. See this later answer with new and improved options: 的查询pg_stat_user_tables 慢很多倍(尽管仍然比完整计数快得多)，因为这是几个表的视图.pg_stat_user_tables is many times slower (though still much faster than full count), as that's a view on a couple of tables.
登录
关闭
