如何加快对PostgreSQL表中的行计数？ [英] How do I speed up counting rows in a PostgreSQL table?

问题描述

我们需要计算PostgreSQL表中的行数。在我们的例子中，没有条件需要满足，如果这显着提高查询速度，得到行估计是完全可以接受的。

基本上，我们想要 select count（id）from< table> 尽可能快地运行，即使这意味着没有得到准确的结果。

谢谢！

<$>

p $ p> SELECT reltuples FROM pg_class WHERE oid ='my_schema.my_table':: regclass;


这优于目前提出的查询 - 包括 Postgres Wiki慢计数（我现在更新了）：

relname 在 pg_class 中不是唯一的。在数据库的多个模式中可以有多个具有相同 relname 的表。

并且对 pg_stat_user_tables 的查询慢500倍，因为这是一个视图

如果您没有对表名进行模式限定，则转换为 regclass 目前 search_path 选择最佳匹配。如果该表在 search_path 中的任何模式中不存在（或不可见），则会收到错误消息。

有关 对象标识符类型的详细信息，请参见手册 a>。

新选项的相关答案：

• 快速发现表的行计数 a>

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 select count(id) from <table> to run as fast as possible, even if that implies not getting exact results.

Thanks! Any suggestions will be appreciated!

解决方案

For a quick estimate:

SELECT reltuples FROM pg_class WHERE oid = 'my_schema.my_table'::regclass;

This is superior to the queries presented so far - including the advice in the Postgres Wiki on slow counting. (I updated that by now.):
relname is not unique in pg_class. There can be multiple tables with the same relname in multiple schemas of the database. That's regularly the case in my installations.

And a query on pg_stat_user_tables is 500x slower, because that's a view on a couple of tables.

If you do not schema-qualify the table name, a cast to 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.

Details on Object Identifier Types in the manual.

Related answer with new options:

• Fast way to discover the row count of a table

这篇关于如何加快对PostgreSQL表中的行计数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！