如何加快 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.
登录
关闭